Back To Table of Contents

PRO FORMA WORKSHEET
Creating pro forma financial statements is a standard way to forecast a company's
financial future. It is a requirement for business plans.
This worksheet is a step-by-step creation of your pro forma. All shaded cells
contain formulas.
Step 1
Enter Tax Rate

25%

1. Enter tax rate.

2. Enter appropriate dates throughout sheet except in shaded cells.

3. Enter three years data from your financial statements.

4. If you are a service company enter a zero in the inventory cells.
Income statement for year ended:

12/31/97

12/31/98

12/31/99

Net sales

$349,595

$367,450

$389,864

Cost of goods sold
Inventory, 1/1

$122,904

$92,903

$113,120

Purchases

$115,372

$141,137

$187,454

Available for sale

$238,276

$234,040

$300,574

Inventory, 12/31

$92,903

$113,120

$129,190

     

Cost of goods sold

$145,373

$120,920

$171,384

     
Gross profit

$204,222

$246,530

$218,480

     
Expenses

Advertising

$6,166

$5,915

$6,770

Building lease

$8,750

$9,110

$9,544

Depreciation: equipment

$4,194

$4,480

$4,536

Insurance

$3,906

$3,754

$4,010

Office supplies

$2,110

$2,680

$3,005

Salaries

$62,378

$72,924

$94,347

Telecommunications

$3,708

$5,507

$7,014

Travel

$1,070

$6,310

$8,733

Total expenses

$92,282

$110,680

$137,959

Taxes

$27,985

$33,962

$20,130

Net income

$83,955

$101,887

$60,391

Step 2
Balance sheet for year ended:

12/31/97

12/31/98

12/31/99

Assets

Cash

$27,215

$52,776

$73,640

Accounts Receivable

$66,629

$158,328

$163,907

Inventory

$92,903

$113,120

$129,190

Equipment

$62,903

$67,206

$68,033

Accumulated depreciation

$4,194

$8,674

$13,209

Equipment

$58,709

$58,532

$54,824

Total

$245,456

$382,756

$421,561

Liabilities and Owner's Equity

Accounts Payable

$169,848

$205,260

$183,674

Owner's Equity:

Owner, 12/31

$75,608

$177,496

$237,887

Total Liabilities and Owner's Equity

$245,456

$382,756

$421,561

Step 3
Income statement for year ended:

12/31/97

12/31/98

12/31/99

Percentage of Sales

3-yr Average 1999 Percent
Net sales

$349,595

$367,450

$389,864

100.00%

100.00%

Cost of goods sold
Inventory, 1/1

$122,904

$92,903

$113,120

29.72%

29.02%

Purchases

$115,372

$141,137

$187,454

40.11%

48.08%

Available for sale

$238,276

$234,040

$300,574

69.82%

77.10%

Inventory, 12/31

$92,903

$113,120

$129,190

30.28%

33.14%

Cost of goods sold

$145,373

$120,920

$171,384

39.54%

43.96%

Gross profit

$204,222

$246,530

$218,480

60.46%

56.04%

Expenses

Advertising

$6,166

$5,915

$6,770

1.70%

1.74%

Building lease

$8,750

$9,110

$9,544

2.48%

2.45%

Depreciation: equipment

$4,194

$4,480

$4,536

1.19%

1.16%

Insurance

$3,906

$3,754

$4,010

1.05%

1.03%

Office supplies

$2,110

$2,680

$3,005

0.70%

0.77%

Salaries

$62,378

$72,924

$94,347

20.75%

24.20%

Telecommunications

$3,708

$5,507

$7,014

1.47%

1.80%

Travel

$1,070

$6,310

$8,733

1.46%

2.24%

Total expenses

$92,282

$110,680

$137,959

30.80%

35.39%

Taxes

$27,985

$33,962

$20,130

7.42%

5.16%

Net income

$83,955

$101,887

$60,391

22.25%

15.49%

Step 4
Projected net sales, 2000

$411,696

1999 Percent 2000 projections
Net sales

100.00%

$411,696

Cost of goods sold
Inventory, 1/1

$129,190

Purchases

48.08%

$197,951

Available for sale

$327,141

Inventory, 12/31

33.14%

$136,425

Cost of goods sold

$190,716

Gross profit

$220,980

Expenses

Advertising

1.74%

$7,149

Building lease

$9,900

Depreciation: equipment

$4,403

Insurance

1.03%

$4,235

Office supplies

0.77%

$3,173

Salaries

24.20%

$99,630

Telecommunications

1.80%

$7,407

Travel

2.24%

$9,222

Total expenses

$145,119

Taxes

$18,965

Net Income

$75,861

Step 5
Modified net sales, 2000

$247,018

1999 Percent 2000 projections
Net sales

100.00%

$247,018

Cost of goods sold
Inventory, 1/1

$129,190

Purchases

$98,976

Available for sale

$228,166

Inventory, 12/31

33.14%

$81,855

Cost of goods sold

$146,311

Gross profit

$100,707

Expenses

Advertising

1.74%

$4,289

Building lease

$9,544

Depreciation: equipment

$4,403

Insurance

1.03%

$2,541

Office supplies

0.77%

$1,904

Salaries

24.20%

$59,778

Telecommunications

1.80%

$4,444

Travel

2.24%

$5,533

Total expenses

$92,436

Taxes

$2,068

Net Income

$6,203

Step 6
Balance sheet for year ended:

12/31/97

12/31/98

12/31/99

12/31/2000

Assets

Cash

$27,215

$52,776

$73,640

$91,397

Accounts Receivable

$66,629

$158,328

$163,907

$155,621

Inventory

$92,903

$113,120

$129,190

$81,855

Equipment

$62,903

$67,206

$68,033

$68,033

Accumulated depreciation

$4,194

$8,674

$13,209

$17,745

Equipment

$58,709

$58,532

$54,824

$50,288

Total

$245,456

$382,756

$421,561

$379,161

Liabilities and Owner's Equity

Accounts Payable

$169,848

$205,260

$183,674

$135,071

Owner's Equity:

Owner, 12/31

$75,608

$177,496

$237,887

$244,090

Total Liabilities and Owner's Equity

$245,456

$382,756

$421,561

$379,161

Step 7

Total

Quarterly projections

for 2000

Q1

Q2

Q3

Q4

Net sales

$411,696

$82,339

$164,679

$102,924

$61,754

Cost of goods sold
Inventory, Start of Period

$129,190

$129,190

$119,723

$100,789

$88,955

Purchases

$98,976

$19,795

$39,590

$24,744

$14,846

Available for sale

$228,166

$148,985

$159,313

$125,533

$103,802

Inventory, End of Period

$81,855

$119,723

$100,789

$88,955

$81,855

Cost of goods sold

$146,311

$29,262

$58,524

$36,578

$21,947

Gross profit

$265,386

$53,077

$106,154

$66,346

$39,808

Expenses

Advertising

$7,149

$1,430

$2,860

$1,787

$1,072

Building lease

$9,900

$2,475

$2,475

$2,475

$2,475

Depreciation: equipment

$4,403

$1,101

$1,101

$1,101

$1,101

Insurance

$4,235

$1,059

$1,059

$1,059

$1,059

Office supplies

$3,173

$635

$1,269

$793

$476

Salaries

$99,630

$19,926

$39,852

$24,908

$14,945

Telecommunications

$7,407

$1,481

$2,963

$1,852

$1,111

Travel

$9,222

$1,844

$3,689

$2,306

$1,383

Total expenses

$145,119

$29,951

$55,267

$36,280

$23,622

Taxes

$30,067

$5,782

$12,722

$7,517

$4,047

Net Income

$90,200

$17,345

$38,165

$22,550

$12,140

Step 8

Total

Quarterly projections

Modified

for 2000

Q1

Q2

Q3

Q4

2000 totals

Net sales

$411,696

$82,339

$164,679

$102,924

$61,754

$411,696

Cost of goods sold
Inventory, Start of Period

$129,190

$129,190

$124,672

$90,891

$79,058

$129,190

Purchases

$98,976

$24,744

$24,744

$24,744

$24,744

$98,976

Available for sale

$228,166

$153,934

$149,416

$115,635

$103,802

$228,166

Inventory, End of Period

$81,855

$124,672

$90,891

$79,058

$81,855

$81,855

Cost of goods sold

$146,311

$29,262

$58,524

$36,578

$21,947

$146,311

Gross profit

$265,386

$53,077

$106,154

$66,346

$39,808

$265,386

Expenses

Advertising

$7,149

$1,430

$2,860

$1,787

$1,072

$7,149

Building lease

$9,900

$2,475

$2,475

$2,475

$2,475

$9,900

Depreciation: equipment

$4,403

$1,101

$1,101

$1,101

$1,101

$4,404

Insurance

$4,235

$847

$1,694

$1,059

$635

$4,235

Office supplies

$3,173

$635

$1,269

$793

$476

$3,173

Salaries

$99,630

$22,500

$22,500

$22,500

$22,500

$90,000

Telecommunications

$7,407

$1,481

$2,963

$1,852

$1,111

$7,407

Travel

$9,222

$1,844

$3,689

$2,306

$1,383

$9,222

Total expenses

$145,119

$32,313

$38,550

$33,873

$30,754

$135,490

Taxes

$30,067

$5,191

$16,901

$8,118

$2,263

$32,474

Net Income

$90,200

$15,573

$50,703

$24,355

$6,790

$97,422

Back To Table of Contents

Copyright, 2007, Jaxworks, All Rights Reserved.