Financial Projections Model For Business Plans Frank Moyes and Stephen Lawrence Deming Center for Entrepreneurship Leeds College of Business University of Colorado Boulder, Colorado
Copyright © 2003 by the Regents of the University of Colorado
Instructions Financial Projections Model v6.8.3 This financial model is designed to enable entrepreneurs and students project the financial results of their ventures for a five-year period. As with all models there are certain simplifying assumptions that have been made. Each of the assumption spreadsheets has an accompanying Comments box (denoted by a cell with small red triangle in the upper right corner) that provides suggestions on how to complete the spreadsheet and the underlying assumptions to the model. You will notice that the cells in the spreadsheet are color-coded. This has been done to assist you in entering data, creating or changing formulae, or deleting data, formulae and links. Enter data and information in Green shaded cells only. Blue shaded cells calculate results within a spreadsheet. Data in Yellow shaded cells is transferred to other worksheets. Purple shaded cells bring in data from other spreadsheets. In the Green and Blue shaded cells, data and formulas may changed, moved or deleted. The current calculations are for illustration only -- you will need to tailor these spreadsheets to fit your plan. In the Yellow shaded cells data and formulas may be changed, but not deleted. DO NOT delete the Purple shaded cells -- they link one spreadsheet with others. There are 7 principal spreadsheet outputs of this model: INCOME: Income Statement BALANCE: Balance Sheet CASHFLOW: Cash Flow Statement BREAKEVEN: Break-even analysis SUMMARY: Analysis of key measures INCOME-MOS: Income Statements by months for Years 1 to 5 CASHFLOW-MOS: Cash flow Statements by months for Years 1 and 2, and by quarters for Years 3 to 5 VAL-1 & VAL-2: Venture capital method for valuing companies All other spreadsheets are to be used for calculating the underlying assumptions of the 7 principal spreadsheets: COMPS: Comparison of financial measures to peer companies REVENUE: Revenue Projections COST OF REV: Cost of Goods Sold OPER EXPEN: Operating Expenses PROP & EQUIP: Capital Expenditures, Depreciation, and Net Fixed Assets SALARIES: Salary Personnel EXTRA: Extraordinary Income and Expense TAXES: Tax calculation WORKCAP: Working Capital FUNDING: Equity, Debt, Interest Expense, Interest Income, Dividends and Retained Earnings These assumption spreadsheets are linked to each other and to the 7 principal spreadsheets. When you make a change in one of the spreadsheets, the impact is automatically recalculated for all other spreadsheets. When doing financial projections, it is easy to get lost in the trees and lose the sense of what is reality. Most errors can be identified if you continuously ask yourself “Does this result make sense?” The COMPS spreadsheet, where you look at peer companies, can be particularly helpful as a reality test. There is an example in the model with which you can experiment. When you are ready to start your own projections, clear all the numbers in the green shaded cells, and then you can begin to enter new numbers. Enter the name of your company in the first cell of the COMPS spreadsheet. Before you begin, make a copy of the model on a separate disk. This original version of the model has been used since the mid-1980’s. It has undergone numerous revisions over that period. Since 1998, students in the Business Plan Preparation course at the University of Colorado have used it to prepare their business plans. We have appreciated their suggestions for improvements and would encourage all users to send us comments and suggestions.
Created by Frank Moyes and Stephen Lawrence Deming Center for Entrepreneurship Leeds School of Business University of Colorado Boulder, Colorado
Copyright © 2001 by the Regents of the University of Colorado
04/18/2009 13:58:08
XYZ Company Peer Company Comparisons
Company A
Company B
Company C
Select Best Comparison
Projections
Year 1
Ratios Accounts Receivable % of Rev Inventory % of Rev Accounts Payable % of Rev Working Capital % of Rev Net Fixed Assets % of Rev Current Ratio Debt to Capital (LT Debt + Equity)
10.0% 15.0% 8.0% 16.0% 9.0% 2.3 1.2
10.0% 15.0% 8.0% 16.0% 9.0% 2.3 1.2
8.3% 8.3% 8.7% 7.9% 23.7% 4.2 0.00
Profitability Gross Profit % of Rev Sales & Marketing % of Rev Research & Development % of Rev General & Administration % of Rev Operating Expenses % of Rev Earnings from Operations % of Rev EBIT % of Rev Depreciation % of Rev EBITDA % of Rev Net Earnings % of Rev
30.0% 30.0% 30.0% 30.0% 40.0% 12.0% 12.0% 3.0% 14.0% 5.0%
30.0%
17.0% 12.0% 12.0% 3.0% 14.0% 5.0%
-11.7% 22.9% 14.9% 17.2% 55.0% -66.7% -71.2% 4.3% -66.9% -71.2%
Returns Return on Assets Return on Equity Return on Capital (LT Debt + Equity)
12.0% 15.0% 15.0%
12.0% 15.0% 15.0%
-74.1% -90.4% -90.4%
Growth Revenue Growth Rate - CAGR: Net Earnings Growth Rate - CAGR:
15.0% 12.0%
15.0% 12.0%
04/18/2009 13:58:10
Notes
Projections
Year 2
Year 3
Year 4
Year 5
8.3% 8.3% 8.7% 7.9% 11.5% 2.0 0.32
8.3% 6.7% 8.7% 6.3% 9.3% 2.0 0.18
8.3% 6.7% 8.7% 6.3% 7.3% 1.9 0.10
8.3% 6.7% 8.7% 6.3% 5.5% 2.1 0.04
32.7% 19.0% 7.9% 8.9% 35.7% -3.0% -3.0% 2.5% -0.5% -4.2%
41.5% 18.5% 7.2% 8.1% 33.8% 7.7% 7.7% 2.5% 10.2% 7.2%
42.5% 17.9% 7.6% 7.3% 32.9% 9.6% 9.6% 2.3% 11.9% 6.2%
46.0% 18.0% 6.7% 6.8% 31.5% 14.5% 14.5% 2.0% 16.5% 8.7%
-9.5% -20.4% -13.9%
21.7% 39.7% 32.4%
20.0% 36.1% 32.6%
27.2% 45.2% 43.5%
425.0% Negative
85.7% Negative
66.7% 42.3%
60.0% 124.6%
04/18/2009 Notes 13:58:11
XYZ Company Revenue Projections Years 1 to 5 ($) Year 1 Product A Number of Units Price per unit Total Service B Number of Customers Fee per Customer Total
Net Revenue
Months Month 1 Month 2 Month 3 Total 1st Quarter Month 4 Month 5 Month 6 Total 2nd Quarter Month 7 Month 8 Month 9 Total 3rd Quarter Month 10 Month 11 Month 12 Total 4th Quarter Total for year Average Revenue by Month by Quarter
Year 2
Year 3
Year 4
Year 5
20,000 50 1,000,000
75,000 50 3,750,000
150,000 45 6,750,000
250,000 45 11,250,000
400,000 40 16,000,000
0
50,000 30 1,500,000
100,000 30 3,000,000
200,000 25 5,000,000
500,000 20 10,000,000
1,000,000
5,250,000
9,750,000
16,250,000
26,000,000
Revenues by Months & Quarters ($) Year 1 Year 2 Year 3 0 0 0 0 50,000 80,000 100,000 230,000 100,000 120,000 150,000 370,000 120,000 130,000 150,000 400,000 1,000,000
262,500 262,500 367,500 892,500 315,000 367,500 367,500 1,050,000 420,000 525,000 525,000 1,470,000 577,500 630,000 630,000 1,837,500 5,250,000
83,333 250,000
437,500 1,312,500
Year 4
Year 5
2,000,000
3,000,000
5,000,000
2,500,000
4,000,000
6,000,000
2,500,000
4,500,000
7,000,000
2,750,000 9,750,000
4,750,000 16,250,000
8,000,000 26,000,000
812,500 2,437,500
1,354,167 4,062,500
2,166,667 6,500,000
04/18/2009 Notes 13:58:11
XYZ Company Cost of Revenues Years 1 to 5 ($) Year 1 Revenue
Year 2
Year 3
Year 4 16,250,000
Year 5
1,000,000
5,250,000
9,750,000
26,000,000
Product A Material Costs per Unit Subcontact Costs per Unit Total Direct Costs per Unit Unit Sales Total Direct Costs Labor Costs Total Direct Costs
15.00 10.00 25.00 20,000 500,000 69,000 569,000
14.00 10.00 24.00 75,000 1,800,000 227,700 2,027,700
11.00 7.00 18.00 150,000 2,700,000 552,000 3,252,000
10.00 6.50 16.50 250,000 4,125,000 864,000 4,989,000
9.00 6.00 15.00 400,000 6,000,000 1,200,000 7,200,000
Service B Service Personnel Costs
120,750
212,750
480,000
1,500,000
2,750,000
Other Expenses Salary Expenses Depreciation Facility costs (rent, energy) System Costs
276,000 36,714 50,000 25,000
690,000 91,714 200,000 100,000
990,000 137,429 300,000 150,000
1,410,000 190,286 300,000 300,000
2,000,000 243,143 450,000 350,000
40,000 1,117,464 111.7%
210,000 3,532,164 67.3%
390,000 5,699,429 58.5%
650,000 9,339,286 57.5%
1,040,000 14,033,143 54.0%
729,750 387,714 1,117,464
2,450,450 1,081,714 3,532,164
4,122,000 1,577,429 5,699,429
7,139,000 2,200,286 9,339,286
10,990,000 3,043,143 14,033,143
Cost of Revenue
All other costs % of Revenue Total Cost of Revenues % of Revenue Allocation of Cost of Revenue between: Variable Fixed Total
4%
Cost of Revenues by Months & Quarters ($) Year 1 Year 2 Year 3 Month 1 Month 2 Month 3 Total 1st Quarter Month 4 Month 5 Month 6 Total 2nd Quarter Month 7 Month 8 Month 9 Total 3rd Quarter Month 10 Month 11 Month 12 Total 4th Quarter Total for year
0 0 0 0 55,873 89,397 111,746 257,017 111,746 134,096 167,620 413,462 134,096 145,270 167,620 446,986 1,117,464
176,608 176,608 247,252 600,468 211,930 247,252 247,252 706,433 282,573 353,216 353,216 989,006 388,538 423,860 423,860 1,236,258 3,532,164
0 0 0 1,169,114 0 0 0 1,461,392 0 0 0 1,461,392 0 0 0 1,607,531 5,699,429
Year 4 0 0 0 1,724,176 0 0 0 2,298,901 0 0 0 2,586,264 0 0 0 2,729,945 9,339,286
Year 5 0 0 0 2,698,681 0 0 0 3,238,418 0 0 0 3,778,154 0 0 0 4,317,890 14,033,143
XYZ Company
Notes
Operating Expenses Years 1 to 5 ($) Net Revenues Sales & Marketing Drivers Salaries and Benefits Commissions % of Revenue Direct Mail Campaign All other expenses % of Revenue Total Sales and Marketing % of Revenue
5%
6%
Research & Development Drivers Salaries and Benefits Testing All other expenses % of Revenue Total Reaserch & Development % of Revenue
3%
General & Administration Drivers Salaries and Benefits Depreciation Rent and Utilities
All other expenses % of Revenue Total General & Administration % of Revenue Total Operating Expenses % of Revenue Allocation of Operating Expenses between: Vaiable Fixed Total
2%
Year 1
Year 2
Year 3
Year 4
Year 5
1,000,000
5,250,000
9,750,000
16,250,000
26,000,000
69,000 50,000 50,000
270,250 262,500 150,000
432,900 487,500 300,000
624,000 812,500 500,000
816,000 1,300,000 1,000,000
60,000 229,000 22.9%
315,000 997,750 19.0%
585,000 1,805,400 18.5%
975,000 2,911,500 17.9%
1,560,000 4,676,000 18.0%
69,000 50,000
195,500 60,000
310,050 100,000
504,000 250,000
672,000 300,000
30,000 149,000 14.9%
157,500 413,000 7.9%
292,500 702,550 7.2%
487,500 1,241,500 7.6%
780,000 1,752,000 6.7%
125,000 6,667 20,000
300,150 40,000 20,000
388,440 106,667 100,000
573,600 183,333 110,000
784,800 266,667 200,000
20,000
105,000
195,000
325,000
520,000
171,667 17.2%
465,150 8.9%
790,107 8.1%
1,191,933 7.3%
1,771,467 6.8%
549,667 55.0%
1,875,900 35.7%
3,298,057 33.8%
5,344,933 32.9%
8,199,467 31.5%
160,000 389,667 549,667
840,000 1,035,900 1,875,900
1,560,000 1,738,057 3,298,057
2,600,000 2,744,933 5,344,933
4,160,000 4,039,467 8,199,467
04/18/2009 13:58:12
Sales & Marketing by Months & Quarters ($) Year 1 Year 2 Year 3 Month 1 Month 2 Month 3 Total 1st Quarter Month 4 Month 5 Month 6 Total 2nd Quarter Month 7 Month 8 Month 9 Total 3rd Quarter Month 10 Month 11 Month 12 Total 4th Quarter Total for year
0 0 0 0 11,450 18,320 22,900 52,670 22,900 27,480 34,350 84,730 27,480 29,770 34,350 91,600 229,000
49,888 49,888 69,843 169,618 59,865 69,843 69,843 199,550 79,820 99,775 99,775 279,370 109,753 119,730 119,730 349,213 997,750
0 0 0 370,338 0 0 0 462,923 0 0 0 462,923 0 0 0 509,215 1,805,400
04/18/2009 13:58:13
Year 4 0 0 0 537,508 0 0 0 716,677 0 0 0 806,262 0 0 0 851,054 2,911,500
Research & Development by Months & Quarters ($) Year 1 Year 2 Year 3 Year 4 Month 1 Month 2 Month 3 Total 1st Quarter Month 4 Month 5 Month 6 Total 2nd Quarter Month 7 Month 8 Month 9 Total 3rd Quarter Month 10 Month 11 Month 12 Total 4th Quarter Total for year
0 0 0 0 7,450 11,920 14,900 34,270 14,900 17,880 22,350 55,130 17,880 19,370 22,350 59,600 149,000
20,650 20,650 28,910 70,210 24,780 28,910 28,910 82,600 33,040 41,300 41,300 115,640 45,430 49,560 49,560 144,550 413,000
0 0 0 144,113 0 0 0 180,141 0 0 0 180,141 0 0 0 198,155 702,550
0 0 0 229,200 0 0 0 305,600 0 0 0 343,800 0 0 0 362,900 1,241,500
General & Administrative by Months & Quarters ($) Year 1 Year 2 Year 3 Year 4 Month 1 Month 2 Month 3 Total 1st Quarter Month 4 Month 5 Month 6 Total 2nd Quarter Month 7 Month 8 Month 9 Total 3rd Quarter Month 10 Month 11 Month 12 Total 4th Quarter Total for year
0 0 0 0 8,583 13,733 17,167 39,483 17,167 20,600 25,750 63,517 20,600 22,317 25,750 68,667 171,667
23,258 23,258 32,561 79,076 27,909 32,561 32,561 93,030 37,212 46,515 46,515 130,242 51,167 55,818 55,818 162,803 465,150
0 0 0 162,073 0 0 0 202,591 0 0 0 202,591 0 0 0 222,851 790,107
0 0 0 220,049 0 0 0 293,399 0 0 0 330,074 0 0 0 348,411 1,191,933
Year 5 0 0 0 899,231 0 0 0 1,079,077 0 0 0 1,258,923 0 0 0 1,438,769 4,676,000
Note: total expenses for the year must equal the annual projections above.
Year 5 0 0 0 336,923 0 0 0 404,308 0 0 0 471,692 0 0 0 539,077 1,752,000
Note: total expenses for the year must equal the annual projections above.
Year 5 0 0 0 340,667 0 0 0 408,800 0 0 0 476,933 0 0 0 545,067 1,771,467
Note: total expenses for the year must equal the annual projections above.
04/18/2009
XYZ Company
Notes13:58:13
Personnel Years 1 to 5 ($) Year 1 Net Revenues Sales & Marketing Sales Manager Marketing Manager Customer service Tech support (other…) (other…) (other…) Total Salary Benefits Percent (%) Total benefit costs Total S & M Compensation % of Revenue Research and Development R & D Manager Engineers Technicians Subcontract (other…) (other…) Total Salary Benefits Percent (%) Total benefit costs Total R & D Compensation % of Revenue General & Administration Chief Executive Officer Chief Financial Officer Accounting Secretarial Clerks and admin personnel (other…) (other…) Total Salary Benefits Percent (%) Total benefit costs Total G & A Compensation % of Revenue
Year 2
Year 3
Year 4
Year 5
1,000,000
5,250,000
9,750,000
16,250,000
26,000,000
60,000
80,000 80,000 40,000 35,000
100,000 100,000 120,000 50,000
125,000 125,000 200,000 70,000
150,000 150,000 250,000 130,000
60,000
235,000
370,000
520,000
680,000
15% 9,000 69,000 6.9%
15% 35,250 270,250 5.1%
17% 62,900 432,900 4.4%
20% 104,000 624,000 3.8%
20% 136,000 816,000 3.1%
60,000
80,000 60,000 30,000
100,000 130,000 35,000
125,000 230,000 65,000
150,000 300,000 110,000
60,000
170,000
265,000
420,000
560,000
15% 9,000 69,000 6.9%
15% 25,500 195,500 3.7%
17% 45,050 310,050 3.2%
20% 84,000 504,000 3.1%
20% 112,000 672,000 2.6%
60,000
80,000 50,000 45,000 26,000 60,000
100,000 60,000 25,000 27,000 120,000
125,000 75,000 50,000 28,000 200,000
150,000 100,000 75,000 29,000 300,000
125,000
261,000
332,000
478,000
654,000
15% 0 125,000 12.5%
15% 39,150 300,150 5.7%
17% 56,440 388,440 4.0%
20% 95,600 573,600 3.5%
20% 130,800 784,800 3.0%
40,000 25,000
Cost of Revenue Manufacturing Personnel Operations Manager Quality Assurance Materials and Logistics Engineering Other personnel (other...) Total Salary Benefits Percent (%) Total benefit costs Total Salary Costs Hourly Personnel Number of employees Average wages per employee Total wages Benefits Percent (%) Total benefit costs Total Wage Costs Service Personnel Number of employees Salary per employee Total salaries Benefits Percent (%) Total benefit costs Total Salary Costs
04/18/2009 13:58:14 100,000 50,000 40,000 50,000
125,000 100,000 150,000 75,000 150,000
150,000 150,000 200,000 125,000 200,000
175,000 300,000 300,000 150,000 250,000
200,000 350,000 450,000 300,000 300,000
240,000
600,000
825,000
1,175,000
1,600,000
15% 36,000 276,000
15% 90,000 690,000
20% 165,000 990,000
20% 235,000 1,410,000
25% 400,000 2,000,000
3 20,000 60,000
9 22,000 198,000
20 23,000 460,000
30 24,000 720,000
40 24,000 960,000
15% 9,000 69,000
15% 29,700 227,700
20% 92,000 552,000
20% 144,000 864,000
25% 240,000 1,200,000
3 35,000 105,000
5 37,000 185,000
10 40,000 400,000
25 50,000 1,250,000
40 55,000 2,200,000
15% 15,750 120,750
15% 27,750 212,750
20% 80,000 480,000
20% 250,000 1,500,000
25% 550,000 2,750,000
Total COR's Compensation % of Revenue
465,750 46.6%
1,130,450 21.5%
2,022,000 20.7%
3,774,000 23.2%
5,950,000 22.9%
Total Salary & Wages Total Benefits Total Compensation % of Revenue
545,000 63,000 728,750 72.9%
1,464,000 219,600 1,896,350 36.1%
2,252,000 421,390 3,153,390 32.3%
3,313,000 662,600 5,475,600 33.7%
4,454,000 1,018,800 8,222,800 31.6%
04/18/2009 Notes 13:58:14
XYZ Company Extraordinary Income & Expense Years 1 to 5 ($) Year 1 Income (Item…) (Item…) (Item…) Total Start-up expenses Legal Relocation (Item…) Total Total Extraordinary Income/(Expense)
Year 2
Year 3
Year 4
Year 5
0
0
0
0
0
0
0 0
0 0
0 0
0 0
25,000 20,000 45,000 (45,000)
Extraordinary Income and Expense by Months & Quarters ($) Year 1 Year 2 Year 3 Year 4 Year 5 Month 1 Month 2 Month 3 Total 1st Quarter Month 4 Month 5 Month 6 Total 2nd Quarter Month 7 Month 8 Month 9 Total 3rd Quarter Month 10 Month 11 Month 12 Total 4th Quarter Total for year
(25,000) (20,000) (45,000)
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0 0
0 0
0 0
0 0
0 (45,000)
04/18/2009 Notes 13:58:15
XYZ Company Taxes Years 1 to 5 ($)
Net Revenues Income Tax (Rate Federal & State) Net Earnings Before Taxes Cumulative Taxes Percent of Revenues
Year 1
Year 2
Year 3
Year 4
Year 5
1,000,000
5,250,000
9,750,000
16,250,000
26,000,000
0.40
0.40
0.40
0.40
0.40
(712,131) (712,131)
(218,064) (930,195)
704,515 (225,681)
1,520,781 1,295,100
3,753,390 5,048,491
0 0.0%
0 0.0%
0 0.0%
(518,040) -3.2%
(1,501,356) -5.8%
04/18/2009 13:58:15
XYZ Company Property and Equipment Years 1 to 5 ($) Year 0 Net Revenues
Purchased Assets
Capital Expenditures Computers, Software & Office Equipment Plant & Equipment Other Total Capital Expenditures % of Revenue
0
Year 1
Year 2
Year 3
Year 4
Year 5
1,000,000
5,250,000
9,750,000
16,250,000
26,000,000
20,000 250,000 10,000 280,000 28.0%
100,000 350,000 50,000 500,000 9.5%
200,000 250,000 100,000 550,000 5.6%
250,000 300,000 100,000 650,000 4.0%
350,000 300,000 100,000 750,000 2.9%
3 0 0 33,333 66,667 83,333
3 0 0 0 66,667 83,333 116,667 266,667
Depreciation Computers, Sofware & Office Equipment (allocated to General & Administrative Expenses) Depreciation Rate: Years 1 3 3 3 Year 0 0 0 0 Year 1 6,667 6,667 6,667 Year 2 33,333 33,333 Year 3 66,667 Year 4 Year 5 Total Depreciation 6,667 40,000 106,667 Depreciation on Plant and Equipment (allocated to Cost of Revenue) Depreciation Rate: Years 1 7 Year 0 0 Year 1 35,714 Year 2 Year 3 Year 4 Year 5 Total Depreciation 35,714 Depreciation Other (allocated to Cost of Revenue) Depreciation Rate: Years Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Total Depreciation
1
Total Depreciation % of Revenue Property & Equipment Gross Asset Value Accumulated Depreciation Net Property and Equipment % of Revenue
0 0
7 0 35,714 50,000
7 0 35,714 50,000 35,714
7 0 35,714 50,000 35,714 42,857
85,714
121,429
164,286
10 0 1,000
10 0 1,000 5,000
10 0 1,000 5,000 10,000
10 0 1,000 5,000 10,000 10,000
1,000
6,000
16,000
26,000
10 0 1,000 5,000 10,000 10,000 10,000 36,000
43,381 4.3%
131,714 2.5%
244,095 2.5%
373,619 2.3%
509,810 2.0%
280,000 43,381 236,619 23.7%
780,000 175,095 604,905 11.5%
1,330,000 419,190 910,810 9.3%
1,980,000 792,810 1,187,190 7.3%
2,730,000 1,302,619 1,427,381 5.5%
Year 4
Year 5
Capital Expenditures by Months & Quarters ($) Year 1 Year 2 Year 3 Month 1 Month 2 Month 3 Total 1st Quarter Month 4 Month 5 Month 6 Total 2nd Quarter Month 7 Month 8 Month 9 Total 3rd Quarter Month 10 Month 11 Month 12 Total 4th Quarter Total for year
183,333
50,000 100,000 50,000 200,000 40,000
7 0 35,714 50,000 35,714 42,857 42,857 207,143
100,000 100,000 200,000
100,000
200,000
200,000
100,000 100,000
200,000
200,000
200,000
40,000
100,000 100,000
100,000
100,000
200,000
0 280,000
100,000 100,000 500,000
150,000 550,000
150,000 650,000
150,000 750,000
40,000 40,000
Notes
04/18/2009 13:58:15
XYZ Company
Notes
Working Capital Years 1 to 5 ($) Year 1 1,000,000
Year 2 5,250,000
Year 3 9,750,000
Accounts Receivable % of Revenue Days Outstanding Accounts Receivable (Increase)/Decrease from Prev. Period
8.3% 30 149,400 (149,400)
8.3% 30 627,480 (478,080)
8.3% 30 913,000 (285,520)
8.3% 30 1,577,000 (664,000)
8.3% 30 2,656,000 (1,079,000)
Inventory % of Revenue Inventory Turns Inventory Days Inventory (Increase)/Decrease from Prev. Period
8.3% 12 30 149,400 (149,400)
8.3% 12 30 627,480 (478,080)
6.7% 15 24 737,000 (109,520)
6.7% 15 24 1,273,000 (536,000)
6.7% 15 24 2,144,000 (871,000)
Other Current Assets % of Revenue Days Other CA Value (Increase)/Decrease from Prev. Period
1.0% 4 18,000 (18,000)
1.0% 4 75,600 (57,600)
1.0% 4 110,000 (34,400)
1.0% 4 190,000 (80,000)
1.0% 4 320,000 (130,000)
Accounts Payable & Accrued Expenses % of Revenue Days AP & Accrued Value Increase/(Decrease) from Prev. Period
8.7% 31 155,720 155,720
8.7% 31 657,720 502,000
8.7% 31 957,000 299,280
8.7% 31 1,653,000 696,000
8.7% 31 2,784,000 1,131,000
1.0% 4 18,000 18,000
1.0% 4 75,600 57,600
1.0% 4 110,000 34,400
1.0% 4 190,000 80,000
1.0% 4 320,000 130,000
Year 1
($) Year 2
Year 3
Year 4
Year 5
0 0
262,500 262,500
0 0
0 0
0 0
0 0 50,000 80,000 100,000 230,000 100,000 120,000 150,000 370,000 120,000 130,000 150,000 400,000 1,000,000
367,500 892,500 315,000 367,500 367,500 1,050,000 420,000 525,000 525,000 1,470,000 577,500 630,000 630,000 1,837,500 5,250,000
0 2,000,000 0 0 0 2,500,000 0 0 0 2,500,000 0 0 0 2,750,000 9,750,000
0 3,000,000 0 0 0 4,000,000 0 0 0 4,500,000 0 0 0 4,750,000 16,250,000
0 5,000,000 0 0 0 6,000,000 0 0 0 7,000,000 0 0 0 8,000,000 26,000,000
Year 1
($) Year 2
Year 3
Year 4
Year 5
Net Revenues
Other Current Liabilites % of Revenue Days Other Current Liabilities Increase/(Decrease) from Prev. Period
Year 4 Year 5 16,250,000 26,000,000
Revenues by Months & Quarters
Month 1 Month 2 Month 3 Total 1st Quarter Month 4 Month 5 Month 6 Total 2nd Quarter Month 7 Month 8 Month 9 Total 3rd Quarter Month 10 Month 11 Month 12 Total 4th Quarter Total for year
Accounts Receivable by Months & Quarters
Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12
49,800 79,680 99,600 99,600 119,520 149,400 119,520 129,480 149,400
261,450 261,450 366,030 313,740 366,030 366,030 418,320 522,900 522,900 575,190 627,480 627,480
664,000
996,000
1,660,000
830,000
1,328,000
1,992,000
830,000
1,494,000
2,324,000
913,000
1,577,000
2,656,000
Inventory by Months & Quarters Year 1 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8
49,800 79,680 99,600 99,600 119,520
($) Year 2 261,450 261,450 366,030 313,740 366,030 366,030 418,320 522,900
Year 3
Year 4
Year 5
536,000
804,000
1,340,000
670,000
1,072,000
1,608,000
04/18/2009 13:58:16
Month 9 Month 10 Month 11 Month 12
149,400 119,520 129,480 149,400
522,900 575,190 627,480 627,480
670,000
1,206,000
1,876,000
737,000
1,273,000
2,144,000
Other Current Assets by Months & Quarters Year 1 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12
6,000 9,600 12,000 12,000 14,400 18,000 14,400 15,600 18,000
($) Year 2 31,500 31,500 44,100 37,800 44,100 44,100 50,400 63,000 63,000 69,300 75,600 75,600
Year 3
Year 4
Year 5
80,000
120,000
200,000
100,000
160,000
240,000
100,000
180,000
280,000
110,000
190,000
320,000
04/18/2009 13:58:17
04/18/2009 13:58:18
Accounts Payble & Accrued Expenses by Months & Quarters Year 1 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12
0 31 50,000 82,200 103,520 104,400 124,400 155,280 126,600 135,280 155,720
($) Year 2 6,600 0 31 331,170 381,360 383,670 436,170 543,480 548,100 600,600 655,410 657,720
Year 3
Year 4
Year 5
696,000
1,044,000
1,740,000
870,000
1,392,000
2,088,000
870,000
1,566,000
2,436,000
957,000
1,653,000
2,784,000
Other Current Liabilities by Months & Quarters Year 1 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12
6,000 9,600 12,000 12,000 14,400 18,000 14,400 15,600 18,000
($) Year 2 31,500 31,500 44,100 37,800 44,100 44,100 50,400 63,000 63,000 69,300 75,600 75,600
Year 3
Year 4
Year 5
80,000
120,000
200,000
100,000
160,000
240,000
100,000
180,000
280,000
110,000
190,000
320,000
04/18/2009 Notes 13:58:18
XYZ Company Funding Projections Years 1 to 5 ($) Begin
Year 1
Year 2
Year 3
Year 4
Year 5
500,000
500,000 0
500,000 0
500,000 0
500,000 0
500,000 0
1,000,000
1,000,000
1,000,000
1,000,000 0 1,500,000
1,000,000 500,000 1,500,000 500,000 2,000,000
1,000,000 500,000 1,500,000 0 2,000,000
1,000,000 500,000 1,500,000 0 2,000,000
1,000,000 500,000 1,500,000 0 2,000,000
0
0
0
100,000 100,000
(100,000)
0
100,000 400,000 500,000
100,000 300,000 400,000
100,000 200,000 300,000
100,000 100,000 200,000
0 0 0
100,000 400,000 500,000
0 (100,000) (100,000)
0 (100,000) (100,000)
0 (100,000) (100,000)
1,500,000
2,500,000
2,400,000
2,400,000
2,200,000
Interest Interest Rate Short Term Debt Long Term Debt
9.0% 12.0%
9.0% 12.0%
9.0% 12.0%
9.0% 12.0%
9.0% 12.0%
Interest Expense Short Term Debt Long Term Debt Total Interest
0 0 0
0 60,000 60,000
0 48,000 48,000
9,000 36,000 45,000
0 24,000 24,000
4.0% 0
4.0% 0
4.0% 0
4.0% 0
4.0% 10,000
(712,131)
(218,064)
704,515
1,002,741
(712,131) 0 (712,131)
(218,064) (712,131) (930,195)
704,515 (930,195) (225,681)
1,002,741 (225,681) 777,060
2,252,034 50,000 2,202,034 777,060 2,979,094
Beginning Cash Equity Common Increase / (Decrease) Previous Period Preferred A Round B Round Total Preferred Increase / (Decrease) Previous Period Total Equity
1,500,000
1,500,000
Debt Short Term Debt Increase / (Decrease) Previous Period Long Term Debt Current Portion Long Term Portion Total Long Term Debt Increase / (Decrease) Previous Period Current Portion Long Term Protion Total Long Term Debt Total Equity & Debt
Interest Income Interest Rate Interest Income Retained Earnings Net Income Dividends Increase / (Decrease) Retained Earnings Beginning Retained Earnings Ending Retained Earnings
1,500,000
04/18/2009 13:58:19
XYZ Company Income Statement Years 1 to 5 ($) Year 1
Year 2
Year 3
Year 4
NET REVENUES
1,000,000
5,250,000
9,750,000
COST OF REVENUE % of Revenues
1,117,464 111.7%
3,532,164 67.3%
5,699,429 58.5%
9,339,286 57.5%
14,033,143 54.0%
GROSS PROFIT % of Revenues
(117,464) -11.7%
1,717,836 32.7%
4,050,571 41.5%
6,910,714 42.5%
11,966,857 46.0%
229,000 149,000 171,667 549,667 55%
997,750 413,000 465,150 1,875,900 36%
1,805,400 702,550 790,107 3,298,057 34%
2,911,500 1,241,500 1,191,933 5,344,933 33%
4,676,000 1,752,000 1,771,467 8,199,467 32%
(667,131)
(158,064)
752,515
1,565,781
3,767,390
EXTRAORDINARY INCOME / (EXPENSE)
(45,000)
0
0
0
0
EARNINGS BEFORE INTEREST & TAXES
(712,131)
(158,064)
752,515
1,565,781
3,767,390
INTEREST INCOME / (EXPENSE)
0
(60,000)
(48,000)
(45,000)
(14,000)
NET EARNINGS BEFORE TAXES
(712,131)
(218,064)
704,515
1,520,781
3,753,390
0
0
0
(518,040)
(1,501,356)
(712,131) -71.2%
(218,064) -4.2%
704,515 7.2%
1,002,741 6.2%
2,252,034 8.7%
OPERATING EXPENSES Sales & Marketing Research & Development General and Administration Total Operating Expenses % of Revenues EARNINGS FROM OPERATIONS
TAXES NET EARNINGS % of Revenues
16,250,000
Year 5 26,000,000
04/18/2009 13:58:19
XYZ Company Balance Sheet Years 1 to 5 ($) Begin ASSETS CURRENT ASSETS Cash Accounts Receivable Inventories Other Current Assets Total Current Assets PROPERTY & EQUIPMENT TOTAL ASSETS LIABILITIES & SHAREHOLDERS' EQUITY CURRENT LIABILITIES Short Term Debt Accounts Payable & Accrued Expen Other Current Liab Current portion of long term debt Total Current Liabilities LONG TERM DEBT (less current portion) STOCKHOLDERS' EQUITY CommonStock Preferred Stock Retained Earnings Total Equity TOTAL LIABILITIES & EQUITY
1,500,000
Year 1
Year 2
Year 3
Year 4
408,170 149,400 149,400 18,000 724,970 236,619 961,589
367,660 627,480 627,480 75,600 1,698,220 604,905 2,303,125
570,510 913,000 737,000 110,000 2,330,510 910,810 3,241,319
792,870 1,577,000 1,273,000 190,000 3,832,870 1,187,190 5,020,060
0 0
0 155,720 18,000 0 173,720
0 657,720 75,600 100,000 833,320
0 957,000 110,000 100,000 1,167,000
100,000 1,653,000 190,000 100,000 2,043,000
0
0
400,000
300,000
200,000
500,000 1,000,000
500,000 1,000,000 (712,131) 787,869 961,589
500,000 1,500,000 (930,195) 1,069,805 2,303,125
500,000 1,500,000 (225,681) 1,774,319 3,241,319
500,000 1,500,000 777,060 2,777,060 5,020,060
1,500,000 0 1,500,000
0
1,500,000 1,500,000
04/18/2009 13:58:19
Year 5
1,735,714 2,656,000 2,144,000 320,000 6,855,714 1,427,381 8,283,094
0 2,784,000 320,000 100,000 3,204,000 100,000
500,000 1,500,000 2,979,094 4,979,094 8,283,094
04/18/2009 13:58:19
XYZ Company Cash Flow Statememt Years 1 to 5 ($) OPERATING ACTIVITIES Net Earnings Depreciation Working Capital Changes (Increase)/Decrease Accounts Receivable (Increase)/Decrease Inventories (Increase)/Decrease Other Current Assets Increase/(Decrease) Accts Pay & Accrd Expenses Increase/(Decrease) Other Current Liab Net Cash Provided/(Used) by Operating Activities INVESTING ACTIVITIES Property & Equipment Other Net Cash Used in Investing Activities FINANCING ACTIVITIES Increase/(Decrease) Short Term Debt Increase/(Decrease) Curr. Portion LTD Increase/(Decrease) Long Term Debt Increase/(Decrease) Common Stock Increase/(Decrease) Preferred Stock Dividends Declared Net Cash Provided / (Used) by Financing INCREASE/(DECREASE) IN CASH CASH AT BEGINNING OF YEAR CASH AT END OF YEAR
1,500,000
Year 1
Year 2
Year 3
Year 4
(712,131) 43,381
(218,064) 131,714
704,515 244,095
1,002,741 373,619
(149,400) (149,400) (18,000) 155,720 18,000 (811,830)
(478,080) (478,080) (57,600) 502,000 57,600 (540,510)
(285,520) (109,520) (34,400) 299,280 34,400 852,850
(664,000) (536,000) (80,000) 696,000 80,000 872,360
(280,000)
(500,000)
(550,000)
(650,000)
(280,000)
(500,000)
(550,000)
(650,000)
0 0 0 0 0 0 0
0 100,000 400,000 0 500,000 0 1,000,000
0 0 (100,000) 0 0 0 (100,000)
100,000 0 (100,000) 0 0 0 0
(1,091,830)
(40,510)
202,850
222,360
1,500,000 408,170
408,170 367,660
367,660 570,510
570,510 792,870
04/18/2009 13:58:19
Year 5 2,252,034 509,810 (1,079,000) (871,000) (130,000) 1,131,000 130,000 1,942,844
(750,000) (750,000)
(100,000) 0 (100,000) 0 0 (50,000) (250,000) 942,844 792,870 1,735,714
04/18/2009 13:58:19
XYZ Company
Notes
Break-Even Analysis Years 1 to 5 ($) Year 1
Year 2
Year 3
Year 4
Year 5
Revenue
1,000,000
5,250,000
9,750,000
16,250,000
26,000,000
Cost of Revenue Variable Fixed Total
729,750 387,714 1,117,464
2,450,450 1,081,714 3,532,164
4,122,000 1,577,429 5,699,429
7,139,000 2,200,286 9,339,286
10,990,000 3,043,143 14,033,143
160,000 389,667 549,667
840,000 1,035,900 1,875,900
1,560,000 1,738,057 3,298,057
2,600,000 2,744,933 5,344,933
4,160,000 4,039,467 8,199,467
889,750 777,381 1,667,131
3,290,450 2,117,614 5,408,064
5,682,000 3,315,485 8,997,485
9,739,000 4,945,219 14,684,219
15,150,000 7,082,610 22,232,610
0.89
0.63
0.58
0.60
0.58
7,051,076
5,673,484
7,946,406
12,342,161
16,972,152
Operating Expenses Variable Fixed Total Total Costs & Expenses Variable Fixed Total Variable Costs/Revenue Ratio Break-Even Point Revenues
XYZ Company
04/18/2009 13:58:21
Income Statements 5 Years by Months & Quarters ($)
Note: these two columns must be the same
XYZ Company Income Statement Year 1 by Months Month 1
Month 2
Month 3
Month 4
Month 5
Month 6
Month 7
Month 8
Month 9
Month 10
Month 11
Month 12
Year 1 Annual Projection
Total 12 Months
NET REVENUES
0
0
0
50,000
80,000
100,000
100,000
120,000
150,000
120,000
130,000
150,000
1,000,000
1,000,000
COST OF REVENUE
0
0
0
55,873
89,397
111,746
111,746
134,096
167,620
134,096
145,270
167,620
1,117,464
1,117,464
GROSS PROFIT
0
0
0
(5,873)
(9,397)
(11,746)
(11,746)
(14,096)
(17,620)
(14,096)
(15,270)
(17,620)
(117,464)
(117,464)
OPERATING EXPENSES Sales & Marketing Research & Development General and Administration Total Operating Expenses
0 0 0 0
0 0 0 0
0 0 0 0
11,450 7,450 8,583 27,483
18,320 11,920 13,733 43,973
22,900 14,900 17,167 54,967
22,900 14,900 17,167 54,967
27,480 17,880 20,600 65,960
34,350 22,350 25,750 82,450
27,480 17,880 20,600 65,960
29,770 19,370 22,317 71,457
34,350 22,350 25,750 82,450
229,000 149,000 171,667 549,667
229,000 149,000 171,667 549,667
0
0
(33,357)
(53,370)
(66,713)
(66,713)
(80,056)
(100,070)
(80,056)
(86,727)
(100,070)
(667,131)
(667,131)
EARNINGS FROM OPERATIONS
0
EXTRAORDINARY INCOME / (EXPENSE)
(25,000)
(20,000)
0
EARNINGS BEFORE INTEREST & TAXES
(25,000)
(20,000)
0
INTEREST INCOME / (EXPENSE) NET EARNINGS BEFORE TAXES
0 (25,000)
TAXES NET EARNINGS
0
(33,357)
0
(20,000)
0 (25,000)
0
0
0
0
(53,370)
0 (33,357)
0
(20,000)
0
(66,713)
0
0 (80,056)
0
(66,713)
0 (53,370)
0
(66,713)
0 (53,370)
0 (33,357)
0
(66,713)
0
(66,713)
(100,070)
0 (80,056)
0
(66,713)
0
(80,056)
0 (100,070)
0 (80,056)
0
(86,727)
0 (80,056)
0 (100,070)
0
(100,070)
0 (86,727)
0 (80,056)
0
(45,000)
(712,131)
(712,131)
0 (100,070)
0 (86,727)
(45,000)
0 (712,131)
0 (100,070)
0 (712,131)
0 (712,131)
0 (712,131)
XYZ Company Income Statement Year 2 by Months Month 1
Month 2
Month 3
Month 4
Month 5
Month 6
Month 7
Month 8
Month 9
Month 10
Month 11
Month 12
Year 2 Total Annual 12 Months Projection
NET REVENUES
262,500
262,500
367,500
315,000
367,500
367,500
420,000
525,000
525,000
577,500
630,000
630,000
5,250,000
5,250,000
COST OF REVENUE
176,608
176,608
247,252
211,930
247,252
247,252
282,573
353,216
353,216
388,538
423,860
423,860
3,532,164
3,532,164
GROSS PROFIT
85,892
85,892
120,249
103,070
120,249
120,249
137,427
171,784
171,784
188,962
206,140
206,140
1,717,836
1,717,836
OPERATING EXPENSES Sales & Marketing Research & Development General and Administration Total Operating Expenses
49,888 20,650 23,258 93,795
49,888 20,650 23,258 93,795
69,843 28,910 32,561 131,313
59,865 24,780 27,909 112,554
69,843 28,910 32,561 131,313
69,843 28,910 32,561 131,313
79,820 33,040 37,212 150,072
99,775 41,300 46,515 187,590
99,775 41,300 46,515 187,590
109,753 45,430 51,167 206,349
119,730 49,560 55,818 225,108
119,730 49,560 55,818 225,108
997,750 413,000 465,150 1,875,900
997,750 413,000 465,150 1,875,900
EARNINGS FROM OPERATIONS
(7,903)
(7,903)
(11,065)
(9,484)
(11,065)
(11,065)
(12,645)
(15,806)
(15,806)
(17,387)
(18,968)
(18,968)
(158,064)
(158,064)
EXTRAORDINARY INCOME / (EXPENSE) EARNINGS BEFORE INTEREST & TAXES
0 (7,903)
0 (7,903)
0 (11,065)
0 (9,484)
0 (11,065)
0 (11,065)
0 (12,645)
0 (15,806)
0 (15,806)
0 (17,387)
0 (18,968)
0 (18,968)
0 (158,064)
0 (158,064)
INTEREST INCOME / (EXPENSE)
(5,000)
(5,000)
(5,000)
(5,000)
(5,000)
(5,000)
(5,000)
(5,000)
(5,000)
(5,000)
(5,000)
(5,000)
(60,000)
(60,000)
NET EARNINGS BEFORE TAXES
(12,903)
(12,903)
(16,065)
(14,484)
(16,065)
(16,065)
(17,645)
(20,806)
(20,806)
(22,387)
(23,968)
(23,968)
(218,064)
(218,064)
TAXES NET EARNINGS
0 (12,903)
0 (12,903)
0 (16,065)
0 (14,484)
0 (16,065)
0 (16,065)
0 (17,645)
0 (20,806)
0 (20,806)
0 (22,387)
0 (23,968)
0 (23,968)
0 (218,064)
0 (218,064)
XYZ Company Income Statement Years 3, 4 & 5 by Quarters Year 3 1st Qtr
2nd Qrtr
Year 4
3rd Qrtr
4th Qrtr
Total
1st Qtr
2nd Qtr
Year 3 Annual Projection
Year 5
3rd Qtr
4th Qtr
Total
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Year 4 Annual Projection
Year 5 Annual Projection
Total
NET REVENUES
2,000,000
2,500,000
2,500,000
2,750,000
9,750,000
3,000,000
4,000,000
4,500,000
4,750,000
16,250,000
5,000,000
6,000,000
7,000,000
8,000,000
26,000,000
COST OF REVENUE
1,169,114
1,461,392
1,461,392
1,607,531
5,699,429
1,724,176
2,298,901
2,586,264
2,729,945
9,339,286
2,698,681
3,238,418
3,778,154
4,317,890
14,033,143
9,750,000 0 5,699,429
16,250,000 0 9,339,286
26,000,000 0 14,033,143
GROSS PROFIT
830,886
1,038,608
1,038,608
1,142,469
4,050,571
1,275,824
1,701,099
1,913,736
2,020,055
6,910,714
2,301,319
2,761,582
3,221,846
3,682,110
11,966,857
4,050,571
6,910,714
11,966,857
OPERATING EXPENSES Sales & Marketing Research & Development General and Administration Total Operating Expenses
370,338 144,113 162,073 676,524
462,923 180,141 202,591 845,656
462,923 180,141 202,591 845,656
509,215 198,155 222,851 930,221
1,805,400 702,550 790,107 3,298,057
537,508 229,200 220,049 986,757
716,677 305,600 293,399 1,315,676
806,262 343,800 330,074 1,480,135
851,054 362,900 348,411 1,562,365
2,911,500 1,241,500 1,191,933 5,344,933
899,231 336,923 340,667 1,576,821
1,079,077 404,308 408,800 1,892,185
1,258,923 471,692 476,933 2,207,549
1,438,769 539,077 545,067 2,522,913
4,676,000 1,752,000 1,771,467 8,199,467
1,805,400 702,550 790,107 3,298,057
2,911,500 1,241,500 1,191,933 5,344,933
4,676,000 1,752,000 1,771,467 8,199,467
EARNINGS FROM OPERATIONS
154,362
192,953
192,953
212,248
752,515
289,067
385,423
433,601
457,690
1,565,781
724,498
869,398
1,014,297
1,159,197
3,767,390
752,515
1,565,781
3,767,390
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
154,362
192,953
192,953
212,248
752,515
289,067
385,423
433,601
457,690
1,565,781
724,498
869,398
1,014,297
1,159,197
3,767,390
752,515
1,565,781
3,767,390
EXTRAORDINARY INCOME / (EXPENSE) EARNINGS BEFORE INTEREST & TAXES INTEREST INCOME / (EXPENSE)
(12,000)
(12,000)
(12,000)
(12,000)
(48,000)
(11,250)
(11,250)
(11,250)
(11,250)
(45,000)
(3,500)
(3,500)
(3,500)
(3,500)
(14,000)
(48,000)
(45,000)
(14,000)
NET EARNINGS BEFORE TAXES
142,362
180,953
180,953
200,248
704,515
277,817
374,173
422,351
446,440
1,520,781
720,998
865,898
1,010,797
1,155,697
3,753,390
704,515
1,520,781
3,753,390
0
0
0
0
0
(94,636)
(127,459)
(143,870)
(152,076)
(518,040)
(288,399)
(346,359)
(404,319)
(462,279)
(1,501,356)
0
(518,040)
(1,501,356)
142,362
180,953
180,953
200,248
704,515
183,181
246,714
278,481
294,364
1,002,741
432,599
519,539
606,478
693,418
2,252,034
704,515
1,002,741
2,252,034
Month 7 (265,153) (818,260)
Month 8 (345,209) (839,066)
Month 9 (445,279) (859,873)
Month 10 (525,334) (882,260)
Month 11 (612,061) (906,228)
Month 12 (712,131) (930,195)
Year 4 Quarter 2 Quarter 3 426,310 848,661
Quarter 4 1,295,100
Quarter 1 2,016,098
Year 5 Quarter 2 Quarter 3 2,881,996 3,892,794
Quarter 4 5,048,491
TAXES NET EARNINGS
Do Not Delete Calculation Area Tax Loss Carry Forward Interest Income/(Expense) Tax Loss Carry Forward Cumulative Net Earning Before Taxes Year 1 Year 2
Month 1 (25,000) (725,034)
Cumulative Net Earning Before Taxes Quarter 1 (787,833) Interest Income/(Expense) Year 1 Year 2 Year 3 Year 4 Year 5
Total Year 0 (60,000) (48,000) (45,000) (14,000)
Month 2 (45,000) (737,938)
Month 3 (45,000) (754,002)
Month 4 (78,357) (768,486)
Year 3 Quarter 2 Quarter 3 (606,881) (425,928)
Quarter 4 (225,681)
Per Month/Qtr 0 (5,000) (12,000) (11,250) (3,500)
Month 5 (131,727) (784,550)
Month 6 (198,440) (800,615)
Quarter 1 52,137
04/18/2009 13:58:23
XYZ Company Cash Flow Statememt Year 1 by Months ($)
XYZ Company Cash Flow Statement Year 1 by Months Month 1 OPERATING ACTIVITIES Net Earnings Depreciation Working Capital Changes (Increase)/Decrease Accounts Receivable (Increase)/Decrease Inventories (Increase)/Decrease Other Current Assets Increase/(Decrease) Accts Pay & Accrd Expenses Increase/(Decrease) Other Current Liab Net Cash Provided/(Used) by Operating Activities INVESTING ACTIVITIES Property & Equipment Other Net Cash Used in Investing Activities
CASH AT BEGINNING OF PERIOD CASH AT END OF PERIOD
Month 3
Month 4
Month 5
Month 6
Month 7
Month 8
Month 9
Month 10
Month 11
Estimated Year 1
Month 12
Year 1 Annual Projection
(25,000) 3,615
(20,000) 3,615
0 3,615
(33,357) 3,615
(53,370) 3,615
(66,713) 3,615
(66,713) 3,615
(80,056) 3,615
(100,070) 3,615
(80,056) 3,615
(86,727) 3,615
(100,070) 3,615
(712,131) 43,381
(712,131) 43,381
0 0 0 0 0 (21,385)
0 0 0 0 0 (16,385)
0 0 0 31 0 3,646
(49,800) (49,800) (6,000) 49,969 6,000 (79,373)
(29,880) (29,880) (3,600) 32,200 3,600 (77,315)
(19,920) (19,920) (2,400) 21,320 2,400 (81,618)
0 0 0 880 0 (62,218)
(19,920) (19,920) (2,400) 20,000 2,400 (96,281)
(29,880) (29,880) (3,600) 30,880 3,600 (125,335)
29,880 29,880 3,600 (28,680) (3,600) (45,361)
(9,960) (9,960) (1,200) 8,680 1,200 (94,352)
(19,920) (19,920) (2,400) 20,440 2,400 (115,855)
(149,400) (149,400) (18,000) 155,720 18,000 (811,830)
(149,400) (149,400) (18,000) 155,720 18,000 (811,830)
(50,000)
(100,000)
(50,000)
(40,000)
0
0
(40,000)
0
0
0
0
0
(50,000)
(100,000)
(50,000)
(40,000)
0
0
(40,000)
0
0
0
0
0
(280,000) 0 (280,000)
(280,000) 0 (280,000)
0 0 0 0 0 0 0
0 0 0 0 0 0 0
(1,091,830)
(1,091,830)
FINANCING ACTIVITIES Increase/(Decrease) Short Term Debt Increase/(Decrease) Curr. Portion LTD Increase/(Decrease) Long Term Debt Increase/(Decrease) Common Stock Increase/(Decrease) Preferred Stock Dividends Declared Net Cash Provided / (Used) by Financing INCREASE/(DECREASE) IN CASH
Month 2
0 0 0 0 0 0
0
0
0
0
0
0
0
0
0
0
0 0 0 0 0 0 0
(71,385)
(116,385)
(46,354)
(119,373)
(77,315)
(81,618)
(102,218)
(96,281)
(125,335)
(45,361)
(94,352)
(115,855)
1,069,188 987,570
987,570 885,352
885,352 789,072
789,072 663,737
663,737 618,376
618,376 524,024
524,024 408,170
1,500,000 1,428,615
1,428,615 1,312,230
1,312,230 1,265,877
1,265,877 1,146,504
1,146,504 1,069,188
1,500,000 408,170
XYZ Company Cash Flow Statement Year 2 by Months Month 1 OPERATING ACTIVITIES Net Earnings Depreciation Working Capital Changes (Increase)/Decrease Accounts Receivable (Increase)/Decrease Inventories (Increase)/Decrease Other Current Assets Increase/(Decrease) Accts Pay & Accrd Expenses Increase/(Decrease) Other Current Liab Net Cash Provided/(Used) by Operating Activities INVESTING ACTIVITIES Property & Equipment Other Net Cash Used in Investing Activities FINANCING ACTIVITIES Increase/(Decrease) Short Term Debt Increase/(Decrease) Curr. Portion LTD Increase/(Decrease) Long Term Debt Increase/(Decrease) Common Stock Increase/(Decrease) Preferred Stock Dividends Declared Net Cash Provided / (Used) by Financing
Month 2
Month 3
Month 4
Month 5
Month 6
Month 7
Month 8
Month 9
Month 10
Month 11
Estimated Year 2
Month 12
(12,903) 10,976
(12,903) 10,976
(16,065) 10,976
(14,484) 10,976
(16,065) 10,976
(16,065) 10,976
(17,645) 10,976
(20,806) 10,976
(20,806) 10,976
(22,387) 10,976
(23,968) 10,976
(23,968) 10,976
(218,064) 131,714
(112,050) (112,050) (13,500) (149,120) 13,500 (375,147)
0 0 0 (6,600) 0 (8,527)
(104,580) (104,580) (12,600) 31 12,600 (214,217)
52,290 52,290 6,300 331,139 (6,300) 432,211
(52,290) (52,290) (6,300) 50,190 6,300 (59,478)
0 0 0 2,310 0 (2,778)
(52,290) (52,290) (6,300) 52,500 6,300 (58,749)
(104,580) (104,580) (12,600) 107,310 12,600 (111,680)
0 0 0 4,620 0 (5,210)
(52,290) (52,290) (6,300) 52,500 6,300 (63,491)
(52,290) (52,290) (6,300) 54,810 6,300 (62,762)
0 0 0 2,310 0 (10,682)
(478,080) (478,080) (57,600) 502,000 57,600 (540,510)
(100,000)
(100,000)
0
0
0
(100,000)
0
0
(100,000)
0
0
(100,000)
(100,000)
(100,000)
0
0
0
(100,000)
0
0
(100,000)
0
0
(100,000)
(500,000) 0 (500,000)
0 100,000 400,000 0 500,000 0 1,000,000 (40,510)
0 100,000 400,000 0 500,000 1,000,000
0
0
0
0
0
0
0
0
0
0
0 0 0 0 0 0 0
INCREASE/(DECREASE) IN CASH
524,853
(108,527)
(214,217)
432,211
(59,478)
(102,778)
(58,749)
(111,680)
(105,210)
(63,491)
(62,762)
(110,682)
CASH AT BEGINNING OF PERIOD CASH AT END OF PERIOD
408,170 933,023
933,023 824,496
824,496 610,279
610,279 1,042,490
1,042,490 983,012
983,012 880,233
880,233 821,484
821,484 709,804
709,804 604,594
604,594 541,103
541,103 478,341
478,341 367,660
Year 2 Annual Projection (218,064) 131,714 0 (478,080) (478,080) (57,600) 502,000 57,600 (540,510) 0 0 (500,000) 0 (500,000) 0 0 0 100,000 400,000 0 500,000 0 1,000,000 0 (40,510) 408,170 367,660
XYZ Company Cash Flow Statement Years 3, 4 & 5 by Quarters Year 3 1st Qtr OPERATING ACTIVITIES Net Earnings Depreciation Working Capital Changes (Increase)/Decrease Accounts Receivable (Increase)/Decrease Inventories (Increase)/Decrease Other Current Assets Increase/(Decrease) Accts Pay & Accrd Expenses Increase/(Decrease) Other Current Liab Net Cash Provided/(Used) by Operating Activities INVESTING ACTIVITIES Property & Equipment Other Net Cash Used in Investing Activities FINANCING ACTIVITIES Increase/(Decrease) Short Term Debt Increase/(Decrease) Curr. Portion LTD Increase/(Decrease) Long Term Debt Increase/(Decrease) Common Stock Increase/(Decrease) Preferred Stock Dividends Declared Net Cash Provided / (Used) by Financing
2nd Qrtr
Year 4
3rd Qrtr
4th Qrtr
Total
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
142,362 61,024
180,953 61,024
180,953 61,024
200,248 61,024
704,515 244,095
183,181 93,405
246,714 93,405
278,481 93,405
294,364 93,405
(36,520) 91,480 (4,400) 38,280 4,400 296,626
(166,000) (134,000) (20,000) 174,000 20,000 115,976
0 0 0 0 0 241,976
(83,000) (67,000) (10,000) 87,000 10,000 198,272
(285,520) (109,520) (34,400) 299,280 34,400 852,850
(83,000) (67,000) (10,000) 87,000 10,000 213,586
(332,000) (268,000) (40,000) 348,000 40,000 88,119
(166,000) (134,000) (20,000) 174,000 20,000 245,886
(83,000) (67,000) (10,000) 87,000 10,000 324,769
(100,000)
(200,000)
(100,000)
(150,000)
(200,000)
(100,000)
(150,000)
(200,000)
(100,000)
(150,000)
(550,000) 0 (550,000)
(200,000)
(100,000)
(200,000)
(200,000)
(100,000)
0 0 (100,000) 0 0 0 (100,000)
100,000 0 0 0 0 100,000
0
202,850
113,586
(111,881)
570,510 684,096
684,096 572,215
0 0 0 0 0 0
0
0
0 0 (100,000) 0 0 0 (100,000)
INCREASE/(DECREASE) IN CASH
196,626
(84,024)
141,976
(51,728)
CASH AT BEGINNING OF PERIOD CASH AT END OF PERIOD
367,660 564,286
564,286 480,262
480,262 622,238
622,238 570,510
Year 3 Annual Projection
Year 5 Total 1,002,741 373,619
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Year 4 Annual Projection
Year 5 Annual Projection
Total
432,599 127,452
519,539 127,452
606,478 127,452
693,418 127,452
2,252,034 509,810
704,515 244,095
(664,000) (536,000) (80,000) 696,000 80,000 872,360
(83,000) (67,000) (10,000) 87,000 10,000 497,051
(332,000) (268,000) (40,000) 348,000 40,000 394,991
(332,000) (268,000) (40,000) 348,000 40,000 481,931
(332,000) (268,000) (40,000) 348,000 40,000 568,871
(1,079,000) (871,000) (130,000) 1,131,000 130,000 1,942,844
(285,520) (109,520) (34,400) 299,280 34,400 852,850
(664,000) (536,000) (80,000) 696,000 80,000 872,360
(1,079,000) (871,000) (130,000) 1,131,000 130,000 1,942,844
(650,000) 0 (650,000)
(200,000)
(200,000)
(200,000)
(150,000)
(750,000)
(150,000)
(200,000)
(200,000)
(200,000)
(150,000)
(750,000)
(550,000) 0 (550,000)
(650,000) 0 (650,000)
(750,000) 0 (750,000)
100,000 0 (100,000) 0 Err:508 0 Err:508
0 0 0 0 0
0
0 0 (100,000) 0 Err:508 0 Err:508
0
0
0
(100,000) 0 (100,000) 0 0 (50,000) (250,000)
(100,000) 0 (100,000) 0 0 (50,000) (250,000)
0 0 (100,000) 0 0 0 (100,000)
100,000 0 (100,000) 0 0 0 0
(100,000) 0 (100,000) 0 0 (50,000) (250,000)
145,886
Err:508
Err:508
297,051
194,991
281,931
168,871
942,844
202,850
222,360
942,844
572,215 718,101
718,101 Err:508
367,660 570,510
570,510 792,870
792,870 1,735,714
Err:508 Err:508
Err:508 Err:508
Err:508 Err:508
Err:508 Err:508
1,002,741 373,619
2,252,034 509,810
04/18/2009 13:58:23
XYZ Company Summary Years 1 to 5 Year 1
Year 2
Year 3
Year 4
Year 5
1,000,000 (117,464) (712,131) (668,750) (712,131) (811,830) 280,000 0 0 408,170 (212,131) 0
5,250,000 1,717,836 (158,064) (26,350) (218,064) (540,510) 500,000 (60,000) 0 367,660 (430,195) 500,000
9,750,000 4,050,571 752,515 996,610 704,515 852,850 550,000 (48,000) 0 570,510 274,319 400,000
16,250,000 6,910,714 1,565,781 1,939,400 1,002,741 872,360 650,000 (45,000) 0 792,870 1,277,060 300,000
26,000,000 11,966,857 3,767,390 4,277,200 2,252,034 1,942,844 750,000 (14,000) 50,000 1,735,714 3,479,094 200,000
425% Nil
86% Nil
67% 42.3%
60% 124.6%
4.2 0.0
2.0 0.3
2.0 0.2
1.9 0.1
2.1 0.0
Profitability Gross Profit % Operating Expenses % Net Earnings %
-11.7% 55.0% -71.2%
32.7% 35.7% -4.2%
41.5% 33.8% 7.2%
42.5% 32.9% 6.2%
46.0% 31.5% 8.7%
Returns Return on Assets Return on Equity Return on Capital (LT Debt + Equity)
-74.1% -90.4% -90.4%
-9.5% -20.4% -13.9%
21.7% 39.7% 32.4%
20.0% 36.1% 32.6%
27.2% 45.2% 43.5%
Summary Financials ($) Revenue Gross Profit EBIT EBITDA Net Earnings Net Cash from Operating Activities Capital Expenditures Interest Income/(Expense) Dividends Cash Total Equity Total Debt Growth Revenue Growth Rate - CAGR: Net Earnings Growth Rate - CAGR: Ratios Current Ratio Debt to Capital (LT Debt + Equity)
04/18/2009 13:58:25
XYZ Company
Notes
Valuation Venture Capital Method Years 1 to 5 Assumptions: Investor required IRR P/E ratio at IPO or acquisition Initial investment
60% 15 $1,500,000 REF
Valuation Calculation
E
Year
Net Income Income Stmt (712,131) (218,064) 704,515 1,002,741 2,252,034
Calculation Liquidity Event in Year 1 Liquidity Event in Year 2 Liquidity Event in Year 3 Liquidity Event in Year 4 Liquidity Event in Year 5
1 2 3 4 5
www.bantinsom.com
REF A B C
D
FV(A,C) F Market Capitalization B*E ($10,681,967) ($3,270,964) $10,567,721 $15,041,112 $33,780,514
G Required Future Value (Investor) FV(F,D) $2,400,000 $3,840,000 $6,144,000 $9,830,400 $15,728,640
Year 1 2 3 4 5
G/F H Investor's Share C/G 100.0% 100.0% 58.1% 65.4% 46.6%
IRR(D,G) I Investor's Return F*H $0 $0 $6,144,000 $9,830,400 $15,728,640
J Investor's ROI I/C 0% 0% 410% 655% 1049%
K Investor's IRR IRR(I,C,D) #N/A #N/A 60% 60% 60%
Negotiation Workspace Investor's Investor's Share Return 25.0% $0 25.0% $0 25.0% $2,641,930 25.0% $3,760,278 40.0% $13,512,206
Investor's ROI 0% 0% 176% 251% 901%
Investor's IRR #N/A #N/A 21% 26% 55%
04/18/2009 13:58:27
www.bantinsom.com
IRR Calculation Workspace Start ($1,500,000) ($1,500,000) ($1,500,000) ($1,500,000) ($1,500,000)
($1,500,000) ($1,500,000) ($1,500,000) ($1,500,000) ($1,500,000)
04/18/2009 13:58:30
www.bantinsom.com
IRR Calculation Workspace Year 1
Year 2
Year 3
Year 4
Year 5
$0 $0 $0 $0 $0
$0 $0 $0 $0
$6,144,000 $0 $0
$9,830,400 $0
$15,728,640
$0 $0 $0 $0 $0
$0 $0 $0 $0
$2,641,930 $0 $0
$3,760,278 $0
$13,512,206
04/18/2009 13:58:30
XYZ Company
Notes
Valuation Venture Capital Method Years 1 to 5 Multiple Rounds Assumptions Month of IPO Forecast annualized earnings at IPO P/E ratio at IPO
60 $2,252,034 15
Investment Round Month of Investment Investor required IRR Amount of Investment Required Monthly IRR Duration of Investment
First 0 60% $1,500,000 5.00% 60
Calculations Market Capitalization at IPO
$33,780,514
Ref A B C Second 24 50% $500,000 4.17% 36
Calculation
Third
0.00% 60
C D E F G
D/12 A-C
H
B*C
Required FV for Investor at IPO Individual Investor's Share Individual Investor's ROI Individual Investor's IRR
First $15,728,640 46.6% 1049% 60%
Second $1,687,500 5.0% 338% 50%
Third $0 0.0% #DIV/0! #DIV/0!
I J K L
E*(1+F)^G I/H I/E (I/E)^(12/G)-1
Cumulative Investors' Share Cumulative Founders' Share
46.6% 53.4%
51.6% 48.4%
51.6% 48.4%
M N
sum(J) 1-M
First 15.0% $5,067,077 338% 28%
Second 5.0% $1,689,026 338% 50%
Third 3.0% $1,013,415 #DIV/0! #DIV/0!
Negotiations Round Individual Investor's Share FV for Investor at IPO Individual Investor's ROI Individual Investor's IRR