Jaxworks.com: Pro Forma Worksheet

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