Essay tutor only

profilecrytal20
week_5_spreadsheet.xlsx
Home>Business & Finance homework help>Management homework help>Essay tutor only

WELCOME

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

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

COMPS

XYZ Company Notes
Compaq: Comparison of Financial Projections with Peer Companies Identify two or three companies that are similar to your venture where you can obtain financial information. Good sources are publicly traded companies in your industry, particularly those that have recently done an IPO. In more traditional industries you may find financial information by NAIC codes in Dun and Bradstreet or the Almanac of Business and Industrial Financial Ratios. When selecting peer companies, you need to consider how many years have they been in business. The financial measures of a company that has been in business for 10 years is likely to be considerable different than a start-up. For example, a start-up’s Sales & Marketing expenses, as a % of revenue, should be considerable greater than a mature company. In that case you should consider the peer company’s measures as a target that you may reach in year 5.
Peer Company Comparisons
Select Best
Company A Company B Company C Comparison Projections
Ratios Year 1 Year 2 Year 3 Year 4 Year 5
Accounts Receivable % of Rev 10.0% 10.0% 8.3% 8.3% 8.3% 8.3% 8.3%
Inventory % of Rev 15.0% 15.0% 8.3% 8.3% 6.7% 6.7% 6.7%
Accounts Payable % of Rev 8.0% 8.0% 8.7% 8.7% 8.7% 8.7% 8.7%
Working Capital % of Rev 16.0% 16.0% 7.9% 7.9% 6.3% 6.3% 6.3%
Net Fixed Assets % of Rev 9.0% 9.0% 23.7% 11.5% 9.3% 7.3% 5.5%
Current Ratio 2.3 2.3 4.2 2.0 2.0 1.9 2.1
Debt to Capital (LT Debt + Equity) 1.2 1.2 0.00 0.32 0.18 0.10 0.04
Profitability
Gross Profit % of Rev 30.0% 30.0% -11.7% 32.7% 41.5% 42.5% 46.0%
Sales & Marketing % of Rev 30.0% 22.9% 19.0% 18.5% 17.9% 18.0%
Research & Development % of Rev 30.0% 14.9% 7.9% 7.2% 7.6% 6.7%
General & Administration % of Rev 30.0% 17.2% 8.9% 8.1% 7.3% 6.8%
Operating Expenses % of Rev 40.0% 17.0% 55.0% 35.7% 33.8% 32.9% 31.5%
Earnings from Operations % of Rev 12.0% 12.0% -66.7% -3.0% 7.7% 9.6% 14.5%
EBIT % of Rev 12.0% 12.0% -71.2% -3.0% 7.7% 9.6% 14.5%
Depreciation % of Rev 3.0% 3.0% 4.3% 2.5% 2.5% 2.3% 2.0%
EBITDA % of Rev 14.0% 14.0% -66.9% -0.5% 10.2% 11.9% 16.5%
Net Earnings % of Rev 5.0% 5.0% -71.2% -4.2% 7.2% 6.2% 8.7%
Returns
Return on Assets 12.0% 12.0% -74.1% -9.5% 21.7% 20.0% 27.2%
Return on Equity 15.0% 15.0% -90.4% -20.4% 39.7% 36.1% 45.2%
Return on Capital (LT Debt + Equity) 15.0% 15.0% -90.4% -13.9% 32.4% 32.6% 43.5%
Growth
Revenue Growth Rate - CAGR: 15.0% 15.0% 425.0% 85.7% 66.7% 60.0%
Net Earnings Growth Rate - CAGR: 12.0% 12.0% Negative Negative 42.3% 124.6%

&D &T

REVENUE

XYZ Company Notes
Compaq: Revenue Projections To project revenues: 1) Determine the key revenue drivers for your business, e.g. a) Number of customers, transactions or units b) Price per customer, transaction or unit c) Average revenue per customer or transaction d) Distribution channel discount e) Market penetration f) Response rate g) Churn rate (proportion of customers lost each year) h) Growth rate i) New services or products 2) Forecast revenues for the 5 years. 3) Estimate revenues by months for years 1 & 2 and by quarters for years 3, 4 and 5. It is critical that these be estimated as accurately as possible, as it forms the basis for projections of Cost of Revenue, Operating Expenses, Plant and Equipment, Working Capital, and Funding, Consider such factors as: a) Timing of product or service roll-out b) Growth rate within the year c) Seasonality d) When orders will be received
Revenue Projections
Years 1 to 5
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Product A
Number of Units 20,000 75,000 150,000 250,000 400,000
Price per unit 50 50 45 45 40
Total 1,000,000 3,750,000 6,750,000 11,250,000 16,000,000
Service B
Number of Customers 50,000 100,000 200,000 500,000
Fee per Customer 30 30 25 20
Total 0 1,500,000 3,000,000 5,000,000 10,000,000
Net Revenue 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Revenues by Months & Quarters
($)
Months Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 0 262,500
Month 2 0 262,500
Month 3 0 367,500
Total 1st Quarter 0 892,500 2,000,000 3,000,000 5,000,000
Month 4 50,000 315,000
Month 5 80,000 367,500
Month 6 100,000 367,500
Total 2nd Quarter 230,000 1,050,000 2,500,000 4,000,000 6,000,000
Month 7 100,000 420,000
Month 8 120,000 525,000
Month 9 150,000 525,000
Total 3rd Quarter 370,000 1,470,000 2,500,000 4,500,000 7,000,000
Month 10 120,000 577,500
Month 11 130,000 630,000
Month 12 150,000 630,000
Total 4th Quarter 400,000 1,837,500 2,750,000 4,750,000 8,000,000
Total for year 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Average Revenue
by Month 83,333 437,500 812,500 1,354,167 2,166,667
by Quarter 250,000 1,312,500 2,437,500 4,062,500 6,500,000

&D &T

COST OF REV

XYZ Company Notes
Compaq: Cost of Revenue Projections To project Cost of Revenues: 1) Determine the key drivers of costs to provide the service or product, e.g. a) Personnel costs (this is calculated automatically when you estimate people expenses - wage rates/salaries, incentives, number of employees - in the PERSONNEL worksheet) b) Depreciation resulting from large capital expenditures (this is calculated automatically when you estimate capital expenditures in PROP & EQUIP spreadsheet) c) Materials costs d) Yields or scrap rates e) Website operating costs f) Systems costs g) Warehouse and shipping expenses h) Maintenance expenses i) Returns j) Outsourcing expenses k) Lease and/or rental expenses l) Cost reductions m) Capacity utilization 2) Estimate All Other Costs that will be required to produce and deliver the product/services by projecting a % of Revenue. The model assumes the same % over the 5-year period. If this is not the case, then change the formula in each cell. 3) Evaluate these cost projections in relation to comparable companies (see the COMPS worksheet). Is the Cost of Revenue/Revenue ratio reasonable when compared to companies similar to yours? 4) Analyze the Cost of Revenues to determine which are variable and fixed costs. Enter these into the worksheet where shown. This allocation will be used in the BREAKEVEN spreadsheet to determine the break-even point. 5) Estimate Cost of Revenues by months for years 1 & 2 and by quarters for years 3, 4 and 5. The model assumes that the Cost of Revenue/Revenue ratio for a particular year is consistent through out the year. This may not be the case, particular in the first and second years. Consider such factors as: a) Product or service roll out timing b) Cost reduction timing c) Inefficiencies when starting up new plant and equipment d) Growth rate e) Seasonality
Cost of Revenues
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
Product A
Material Costs per Unit 15.00 14.00 11.00 10.00 9.00
Subcontact Costs per Unit 10.00 10.00 7.00 6.50 6.00
Total Direct Costs per Unit 25.00 24.00 18.00 16.50 15.00
Unit Sales 20,000 75,000 150,000 250,000 400,000
Total Direct Costs 500,000 1,800,000 2,700,000 4,125,000 6,000,000
Labor Costs 69,000 227,700 552,000 864,000 1,200,000
Total Direct Costs 569,000 2,027,700 3,252,000 4,989,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 276,000 690,000 990,000 1,410,000 2,000,000
Depreciation 36,714 91,714 137,429 190,286 243,143
Facility costs (rent, energy) 50,000 200,000 300,000 300,000 450,000
System Costs 25,000 100,000 150,000 300,000 350,000
All other costs % of Revenue 4% 40,000 210,000 390,000 650,000 1,040,000
Total Cost of Revenues 1,117,464 3,532,164 5,699,429 9,339,286 14,033,143
% of Revenue 111.7% 67.3% 58.5% 57.5% 54.0%
Allocation of Cost of Revenue between:
Variable 729,750 2,450,450 4,122,000 7,139,000 10,990,000

Frank Moyes: Variable Expenses Decide which of the Costs of Revenue are variable		 Fixed 387,714 1,081,714 1,577,429 2,200,286 3,043,143

Frank Moyes: Fixed Expenses Decide which of the Costs of Revenue are fixed		 Total 1,117,464 3,532,164 5,699,429 9,339,286 14,033,143
Cost of Revenues by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 0 176,608 0 0 0
Month 2 0 176,608 0 0 0
Month 3 0 247,252 0 0 0
Total 1st Quarter 0 600,468 1,169,114 1,724,176 2,698,681
Month 4 55,873 211,930 0 0 0
Month 5 89,397 247,252 0 0 0
Month 6 111,746 247,252 0 0 0
Total 2nd Quarter 257,017 706,433 1,461,392 2,298,901 3,238,418
Month 7 111,746 282,573 0 0 0
Month 8 134,096 353,216 0 0 0
Month 9 167,620 353,216 0 0 0
Total 3rd Quarter 413,462 989,006 1,461,392 2,586,264 3,778,154
Month 10 134,096 388,538 0 0 0
Month 11 145,270 423,860 0 0 0
Month 12 167,620 423,860 0 0 0
Total 4th Quarter 446,986 1,236,258 1,607,531 2,729,945 4,317,890
Total for year 1,117,464 3,532,164 5,699,429 9,339,286 14,033,143

&D &T

OPER EXP

XYZ Company Notes
Compaq: Operating Expense Projections To project operating expenses: 1) Determine the key drivers of operating expenses, e.g. a) Sales & Marketing i) Personnel expenses (this is calculated automatically when you estimate people expenses - wage rates/salaries, incentives, number of employees - in the PERSONNEL worksheet) ii) Customer acquisition cost iii) Sales commissions iv) Exhibitions v) Brand building vi) Catalog vii) Customer service viii) Tech support ix) Customer service b) Research and Development i) Personnel expenses (this is calculated automatically when you estimate people expenses - wage rates/salaries, incentives, number of employees - in the PERSONNEL worksheet) ii) Beta testing iii) Time to market iv) Patent and copyright application v) Prototyping vi) Subcontracting c) General and Administration i) Personnel expenses (this is calculated automatically when you estimate people expenses - wage rates/salaries, incentives, number of employees - in the PERSONNEL worksheet) ii) Depreciation resulting from large capital expenditures (this is calculated automatically when you estimate capital expenditures in PROP & EQUIP spreadsheet) iii) Legal, accounting and other service provider expenses iv) Credit card transaction fees v) Recruiting expenses vi) MIS expenses vii) Office rent and utilities 2) Make provisions in each of the operating expenses categories for all the other operating expenses that are not significant enough to be considered a driver. These might include marketing materials, travel and entertainment, insurance, leasing, telecommunications, etc. 3) Evaluate the projection of the Operating Expense/Revenue ratio for Sales & Marketing, Research & Development, and General & Administration in relation to comparable companies (see the COMPS worksheet). Is the Operating Expense/Revenue ratio reasonable when compared to companies similar to yours? 4) Estimate operating expenses for Sales & Marketing, Research & Development, and General & Administration by months for years 1 & 2 and by quarters for years 3, 4 and 5. The model automatically projects the monthly and quarterly expenses by multiplying each month’s or quarter’s Revenue by the year’s operating expense/revenue ratio. This may not be accurate, particularly in the first and second years. Consider such factors as: a) Product or service roll out timing b) Major events, e.g. opening a new location, product launch c) Growth rate d) Seasonality Also, in many new businesses there are little or no revenues in the early months. Conversely, operating expenses can be very high, as you get ready to launch the business. The operating expense/revenue ratio may vastly underestimate these early month expenses and you should make suitable adjustments.
Operating Expenses
Years 1 to 5
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Net Revenues 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Sales & Marketing
Drivers
Salaries and Benefits 69,000 270,250 432,900 624,000 816,000
Commissions % of Revenue 5% 50,000 262,500 487,500 812,500 1,300,000
Direct Mail Campaign 50,000 150,000 300,000 500,000 1,000,000
All other expenses % of Revenue 6% 60,000 315,000 585,000 975,000 1,560,000
Total Sales and Marketing 229,000 997,750 1,805,400 2,911,500 4,676,000
% of Revenue 22.9% 19.0% 18.5% 17.9% 18.0%
Research & Development
Drivers
Salaries and Benefits 69,000 195,500 310,050 504,000 672,000
Testing 50,000 60,000 100,000 250,000 300,000
All other expenses % of Revenue 3% 30,000 157,500 292,500 487,500 780,000
Total Reaserch & Development 149,000 413,000 702,550 1,241,500 1,752,000
% of Revenue 14.9% 7.9% 7.2% 7.6% 6.7%
General & Administration
Drivers
Salaries and Benefits 125,000 300,150 388,440 573,600 784,800
Depreciation 6,667 40,000 106,667 183,333 266,667
Rent and Utilities 20,000 20,000 100,000 110,000 200,000
All other expenses % of Revenue 2% 20,000 105,000 195,000 325,000 520,000
Total General & Administration 171,667 465,150 790,107 1,191,933 1,771,467
% of Revenue 17.2% 8.9% 8.1% 7.3% 6.8%
Total Operating Expenses 549,667 1,875,900 3,298,057 5,344,933 8,199,467
% of Revenue 55.0% 35.7% 33.8% 32.9% 31.5%
Allocation of Operating Expenses between:
Vaiable 160,000 840,000 1,560,000 2,600,000 4,160,000

Frank Moyes: Variable Expenses Decide which of the Operating Expenses are variable		 Fixed 389,667 1,035,900 1,738,057 2,744,933 4,039,467

Frank Moyes: Fixed Expenses Decide which of the Operating Expenses are fixed
Compaq: Operating Expense Projections To project operating expenses: 1) Determine the key drivers of operating expenses, e.g. a) Sales & Marketing i) Personnel expenses (this is calculated automatically when you estimate people expenses - wage rates/salaries, incentives, number of employees - in the PERSONNEL worksheet) ii) Customer acquisition cost iii) Sales commissions iv) Exhibitions v) Brand building vi) Catalog vii) Customer service viii) Tech support ix) Customer service b) Research and Development i) Personnel expenses (this is calculated automatically when you estimate people expenses - wage rates/salaries, incentives, number of employees - in the PERSONNEL worksheet) ii) Beta testing iii) Time to market iv) Patent and copyright application v) Prototyping vi) Subcontracting c) General and Administration i) Personnel expenses (this is calculated automatically when you estimate people expenses - wage rates/salaries, incentives, number of employees - in the PERSONNEL worksheet) ii) Depreciation resulting from large capital expenditures (this is calculated automatically when you estimate capital expenditures in PROP & EQUIP spreadsheet) iii) Legal, accounting and other service provider expenses iv) Credit card transaction fees v) Recruiting expenses vi) MIS expenses vii) Office rent and utilities 2) Make provisions in each of the operating expenses categories for all the other operating expenses that are not significant enough to be considered a driver. These might include marketing materials, travel and entertainment, insurance, leasing, telecommunications, etc. 3) Evaluate the projection of the Operating Expense/Revenue ratio for Sales & Marketing, Research & Development, and General & Administration in relation to comparable companies (see the COMPS worksheet). Is the Operating Expense/Revenue ratio reasonable when compared to companies similar to yours? 4) Estimate operating expenses for Sales & Marketing, Research & Development, and General & Administration by months for years 1 & 2 and by quarters for years 3, 4 and 5. The model automatically projects the monthly and quarterly expenses by multiplying each month’s or quarter’s Revenue by the year’s operating expense/revenue ratio. This may not be accurate, particularly in the first and second years. Consider such factors as: a) Product or service roll out timing b) Major events, e.g. opening a new location, product launch c) Growth rate d) Seasonality Also, in many new businesses there are little or no revenues in the early months. Conversely, operating expenses can be very high, as you get ready to launch the business. The operating expense/revenue ratio may vastly underestimate these early month expenses and you should make suitable adjustments. 		Total 549,667 1,875,900 3,298,057 5,344,933 8,199,467
Sales & Marketing by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 0 49,888 0 0 0
Month 2 0 49,888 0 0 0
Month 3 0 69,843 0 0 0
Total 1st Quarter 0 169,618 370,338 537,508 899,231
Month 4 11,450 59,865 0 0 0
Month 5 18,320 69,843 0 0 0
Month 6 22,900 69,843 0 0 0
Total 2nd Quarter 52,670 199,550 462,923 716,677 1,079,077
Month 7 22,900 79,820 0 0 0
Month 8 27,480 99,775 0 0 0
Month 9 34,350 99,775 0 0 0
Total 3rd Quarter 84,730 279,370 462,923 806,262 1,258,923
Month 10 27,480 109,753 0 0 0
Month 11 29,770 119,730 0 0 0
Month 12 34,350 119,730 0 0 0
Total 4th Quarter 91,600 349,213 509,215 851,054 1,438,769
Total for year 229,000 997,750 1,805,400 2,911,500 4,676,000
Research & Development by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 0 20,650 0 0 0
Month 2 0 20,650 0 0 0
Month 3 0 28,910 0 0 0
Total 1st Quarter 0 70,210 144,113 229,200 336,923
Month 4 7,450 24,780 0 0 0
Month 5 11,920 28,910 0 0 0
Month 6 14,900 28,910 0 0 0
Total 2nd Quarter 34,270 82,600 180,141 305,600 404,308
Month 7 14,900 33,040 0 0 0
Month 8 17,880 41,300 0 0 0
Month 9 22,350 41,300 0 0 0
Total 3rd Quarter 55,130 115,640 180,141 343,800 471,692
Month 10 17,880 45,430 0 0 0
Month 11 19,370 49,560 0 0 0
Month 12 22,350 49,560 0 0 0
Total 4th Quarter 59,600 144,550 198,155 362,900 539,077
Total for year 149,000 413,000 702,550 1,241,500 1,752,000
General & Administrative by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 0 23,258 0 0 0
Month 2 0 23,258 0 0 0
Month 3 0 32,561 0 0 0
Total 1st Quarter 0 79,076 162,073 220,049 340,667
Month 4 8,583 27,909 0 0 0
Month 5 13,733 32,561 0 0 0
Month 6 17,167 32,561 0 0 0
Total 2nd Quarter 39,483 93,030 202,591 293,399 408,800
Month 7 17,167 37,212 0 0 0
Month 8 20,600 46,515 0 0 0
Month 9 25,750 46,515 0 0 0
Total 3rd Quarter 63,517 130,242 202,591 330,074 476,933
Month 10 20,600 51,167 0 0 0
Month 11 22,317 55,818 0 0 0
Month 12 25,750 55,818 0 0 0
Total 4th Quarter 68,667 162,803 222,851 348,411 545,067
Total for year 171,667 465,150 790,107 1,191,933 1,771,467

&D &T

Note: total expenses for the year must equal the annual projections above.

Note: total expenses for the year must equal the annual projections above.

Note: total expenses for the year must equal the annual projections above.

PERSONNEL

XYZ Company Notes
Compaq: Personnel Expenses To project personnel expenses: 1) Determine key personnel to be recruited a) Sales and Marketing b) Research and Development c) General and Administrative d) Cost of Revenue i) Salary ii) Hourly 2) For each of the above areas indicate a) Position or title b) Number of employees c) When will be hired d) Salary or wages. 3) If you are projecting significant growth over the period, then you should make sure that salaries of the key employees are roughly comparable to companies in the same industry and size. For example, you may be successful in attracting the Chief Marketing Officer to your company with a generous options package, but “low” salary of $100,000 in the first two years of operations. If your company grows to $50 million in revenues in year 3, then you will have to begin the pay close the market rate for company of that size. One of the most common mistakes new entrepreneurs make is to vastly underestimate the salary levels that the company must pay to attract key personnel. You need to determine the market rate in your area and industry for personnel. 4) Determine the benefits package as a % of base pay. This should include legally required employer deductions such as FICA and Workman’s comp, as well as health insurance, pensions and other benefits. 5) Determine incentive plan (options, profit sharing, bonus). Estimate the cost and include it in the Administrative Expense section of the Operating Expenses worksheet.
Personnel
Years 1 to 5
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Net Revenues 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Sales & Marketing
Sales Manager 60,000 80,000 100,000 125,000 150,000
Marketing Manager 80,000 100,000 125,000 150,000
Customer service 40,000 120,000 200,000 250,000
Tech support 35,000 50,000 70,000 130,000
(other…)
(other…)
(other…)
Total Salary 60,000 235,000 370,000 520,000 680,000
Benefits
Percent (%) 15% 15% 17% 20% 20%
Total benefit costs 9,000 35,250 62,900 104,000 136,000
Total S & M Compensation 69,000 270,250 432,900 624,000 816,000
% of Revenue 6.9% 5.1% 4.4% 3.8% 3.1%
Research and Development
R & D Manager 80,000 100,000 125,000 150,000
Engineers 60,000 60,000 130,000 230,000 300,000
Technicians 30,000 35,000 65,000 110,000
Subcontract
(other…)
(other…)
Total Salary 60,000 170,000 265,000 420,000 560,000
Benefits
Percent (%) 15% 15% 17% 20% 20%
Total benefit costs 9,000 25,500 45,050 84,000 112,000
Total R & D Compensation 69,000 195,500 310,050 504,000 672,000
% of Revenue 6.9% 3.7% 3.2% 3.1% 2.6%
General & Administration
Chief Executive Officer 60,000 80,000 100,000 125,000 150,000
Chief Financial Officer 50,000 60,000 75,000 100,000
Accounting 40,000 45,000 25,000 50,000 75,000
Secretarial 25,000 26,000 27,000 28,000 29,000
Clerks and admin personnel 60,000 120,000 200,000 300,000
(other…)
(other…)
Total Salary 125,000 261,000 332,000 478,000 654,000
Benefits
Percent (%) 15% 15% 17% 20% 20%
Total benefit costs 0 39,150 56,440 95,600 130,800
Total G & A Compensation 125,000 300,150 388,440 573,600 784,800
% of Revenue 12.5% 5.7% 4.0% 3.5% 3.0%
Cost of Revenue
Manufacturing Personnel
Operations Manager 100,000 125,000 150,000 175,000 200,000
Quality Assurance 50,000 100,000 150,000 300,000 350,000
Materials and Logistics 40,000 150,000 200,000 300,000 450,000
Engineering 75,000 125,000 150,000 300,000
Other personnel 50,000 150,000 200,000 250,000 300,000
(other...)
Total Salary 240,000 600,000 825,000 1,175,000 1,600,000
Benefits
Percent (%) 15% 15% 20% 20% 25%
Total benefit costs 36,000 90,000 165,000 235,000 400,000
Total Salary Costs 276,000 690,000 990,000 1,410,000 2,000,000
Hourly Personnel
Number of employees 3 9 20 30 40
Average wages per employee 20,000 22,000 23,000 24,000 24,000
Total wages 60,000 198,000 460,000 720,000 960,000
Benefits
Percent (%) 15% 15% 20% 20% 25%
Total benefit costs 9,000 29,700 92,000 144,000 240,000
Total Wage Costs 69,000 227,700 552,000 864,000 1,200,000
Service Personnel
Number of employees 3 5 10 25 40
Salary per employee 35,000 37,000 40,000 50,000 55,000
Total salaries 105,000 185,000 400,000 1,250,000 2,200,000
Benefits
Percent (%) 15% 15% 20% 20% 25%
Total benefit costs 15,750 27,750 80,000 250,000 550,000
Total Salary Costs 120,750 212,750 480,000 1,500,000 2,750,000
Total COR's Compensation 465,750 1,130,450 2,022,000 3,774,000 5,950,000
% of Revenue 46.6% 21.5% 20.7% 23.2% 22.9%
Total Salary & Wages 545,000 1,464,000 2,252,000 3,313,000 4,454,000
Total Benefits 63,000 219,600 421,390 662,600 1,018,800
Total Compensation 728,750 1,896,350 3,153,390 5,475,600 8,222,800
% of Revenue 72.9% 36.1% 32.3% 33.7% 31.6%

&D &T

EXTRA

XYZ Company Notes
Compaq: Extraordinary Income & Expense To project extraordinary Income & Expense, estimate those amounts that are one of a kind or nonrecurring. Estimate Extraordinary Income & Expenses by months for years 1 & 2 and by quarters for years 3, 4 and 5. Consider major events such as product or service rollout, acquisition, initial public offering, etc.
Extraordinary Income & Expense
Years 1 to 5
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Income
(Item…) 0
(Item…)
(Item…)
Total 0 0 0 0 0
Start-up expenses
Legal 25,000
Relocation 20,000
(Item…)
Total 45,000 0 0 0 0
Total Extraordinary Income/(Expense) (45,000) 0 0 0 0
Extraordinary Income and Expense by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 (25,000)
Month 2 (20,000)
Month 3
Total 1st Quarter (45,000) 0 0 0 0
Month 4
Month 5
Month 6
Total 2nd Quarter 0 0 0 0 0
Month 7
Month 8
Month 9
Total 3rd Quarter 0 0 0 0 0
Month 10
Month 11
Month 12
Total 4th Quarter 0 0 0 0 0
Total for year (45,000) 0 0 0 0

&D &T

TAXES

XYZ Company Notes
Compaq: Taxes Determine the appropriate federal, state and local income tax rates. If you have losses in the initial years, the loss carry-forward is automatically calculated.
Taxes
Years 1 to 5
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Net Revenues 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Income Tax (Rate Federal & State) 0.40 0.40 0.40 0.40 0.40
Net Earnings Before Taxes (712,131) (218,064) 704,515 1,520,781 3,753,390
Cumulative (712,131) (930,195) (225,681) 1,295,100 5,048,491
Taxes 0 0 0 (518,040) (1,501,356)
Percent of Revenues 0.0% 0.0% 0.0% -3.2% -5.8%

&D &T

PROP & EQUIP

XYZ Company Notes
Compaq: Property & Equipment To project property and equipment, capital expenditures, depreciation, accumulated depreciation: 1) Determine the major capital expenditure projects for 5 years, e.g. property, plant, equipment, computers, servers, systems, software, furniture and fixtures, etc. Keep in mind that software, system design, training can be equal to or greater than the cost of hardware. Sometimes a new business may be able to purchase the assets of an existing business. The depreciation rates for these assets may be different than that of new capital expenditures. The model will calculate the depreciation and net asset value of the expenditures. 2) Estimate the level on-going capital expenditures. 3) Determine the expected life for the each of the expenditures. Depreciation will be calculated on a straight-line basis. The model assumes that full 12-month’s depreciation is taken in the year that the expenditure takes place. The model groups expenditures into three categories: a) Computers, software and office equipment (depreciation allocated to General & Administrative expenses) b) Plant and equipment (depreciation allocated to Cost of Revenue) c) Other (depreciation allocated to General & Administrative expenses) If you are a capital-intensive business, you may need more categories of expenditures with different depreciation rates. The model can be modified, but you will need to establish links to the appropriate cost/expense spreadsheets. 4) Estimate capital expenditure by months for years 1 & 2 and by quarters for years 3, 4 and 5. Consider such factors as: a) Product or service roll out timing b) Capacity utilization c) New process development and technology d) Equipment and systems obsolecence e) Cost reduction timing f) Growth rate g) Seasonality
Property and Equipment
Years 1 to 5
($)
Year 0
Frank Moyes: Purchased Assets To be used when your venture purchases an existing business.
Compaq: Property & Equipment To project property and equipment, capital expenditures, depreciation, accumulated depreciation: 1) Determine the major capital expenditure projects for 5 years, e.g. property, plant, equipment, computers, servers, systems, software, furniture and fixtures, etc. Keep in mind that software, system design, training can be equal to or greater than the cost of hardware. Sometimes a new business may be able to purchase the assets of an existing business. The depreciation rates for these assets may be different than that of new capital expenditures. The model will calculate the depreciation and net asset value of the expenditures. 2) Estimate the level on-going capital expenditures. 3) Determine the expected life for the each of the expenditures. Depreciation will be calculated on a straight-line basis. The model assumes that full 12-month’s depreciation is taken in the year that the expenditure takes place. The model groups expenditures into three categories: a) Computers, software and office equipment (depreciation allocated to General & Administrative expenses) b) Plant and equipment (depreciation allocated to Cost of Revenue) c) Other (depreciation allocated to General & Administrative expenses) If you are a capital-intensive business, you may need more categories of expenditures with different depreciation rates. The model can be modified, but you will need to establish links to the appropriate cost/expense spreadsheets. 4) Estimate capital expenditure by months for years 1 & 2 and by quarters for years 3, 4 and 5. Consider such factors as: a) Product or service roll out timing b) Capacity utilization c) New process development and technology d) Equipment and systems obsolecence e) Cost reduction timing f) Growth rate g) Seasonality 		Year 1 Year 2 Year 3 Year 4 Year 5
Purchased
Net Revenues Assets 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Capital Expenditures
Computers, Software & Office Equipment 20,000 100,000 200,000 250,000 350,000
Plant & Equipment 250,000 350,000 250,000 300,000 300,000
Other 10,000 50,000 100,000 100,000 100,000
Total Capital Expenditures 0 280,000 500,000 550,000 650,000 750,000
% of Revenue 28.0% 9.5% 5.6% 4.0% 2.9%
Depreciation Computers, Sofware & Office Equipment (allocated to General & Administrative Expenses)
Depreciation Rate: Years 1 3 3 3 3 3
Year 0 0 0 0 0 0
Year 1 6,667 6,667 6,667 0 0
Year 2 33,333 33,333 33,333 0
Year 3 66,667 66,667 66,667
Year 4 83,333 83,333
Year 5 116,667
Total Depreciation 6,667 40,000 106,667 183,333 266,667
Depreciation on Plant and Equipment (allocated to Cost of Revenue)
Depreciation Rate: Years 1 7 7 7 7 7
Year 0 0 0 0 0 0
Year 1 35,714 35,714 35,714 35,714 35,714
Year 2 50,000 50,000 50,000 50,000
Year 3 35,714 35,714 35,714
Year 4 42,857 42,857
Year 5 42,857
Total Depreciation 35,714 85,714 121,429 164,286 207,143
Depreciation Other (allocated to Cost of Revenue)
Depreciation Rate: Years 1 10 10 10 10 10
Year 0 0 0 0 0 0
Year 1 1,000 1,000 1,000 1,000 1,000
Year 2 5,000 5,000 5,000 5,000
Year 3 10,000 10,000 10,000
Year 4 10,000 10,000
Year 5 10,000
Total Depreciation 1,000 6,000 16,000 26,000 36,000
Total Depreciation 43,381 131,714 244,095 373,619 509,810
% of Revenue 4.3% 2.5% 2.5% 2.3% 2.0%
Property & Equipment
Gross Asset Value 0 280,000 780,000 1,330,000 1,980,000 2,730,000
Accumulated Depreciation 43,381 175,095 419,190 792,810 1,302,619
Net Property and Equipment 0 236,619 604,905 910,810 1,187,190 1,427,381
% of Revenue 23.7% 11.5% 9.3% 7.3% 5.5%
Capital Expenditures by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 50,000 100,000
Month 2 100,000 100,000
Month 3 50,000
Total 1st Quarter 200,000 200,000 100,000 200,000 200,000
Month 4 40,000
Month 5
Month 6 100,000
Total 2nd Quarter 40,000 100,000 200,000 200,000 200,000
Month 7 40,000
Month 8
Month 9 100,000
Total 3rd Quarter 40,000 100,000 100,000 100,000 200,000
Month 10
Month 11
Month 12 100,000
Total 4th Quarter 0 100,000 150,000 150,000 150,000
Total for year 280,000 500,000 550,000 650,000 750,000

&D &T

WORKCAP

XYZ Company Notes
Compaq: Working Capital To project working capital: 1) Accounts Receivable Determine for each year accounts receivables as a % of Rrevenue (the average number of days outstanding is automatically calculated). Use the information collected in the Financial COMP's analysis to help estimate the percentage. The model will calculate the accounts receivable outstanding at the end of the year and month/quarter, based upon the monthly and quarterly revenue projections. The formula for calculating accounts receivable has a maximum of 120 days. If you expect the days outstanding to be greater than 120, then you will need to adjust the formula, or perhaps consider a different credit policy. 2) Inventory Determine for each year inventory as a % of Revenue (the inventory turns are automatically calculated). Use the information collected in the Financial COMP's analysis to help estimate the percentage. The model will calculate the inventory at the end of the year and month/quarter, based upon the monthly and quarterly revenue projections. The formula for calculating inventory has a minimum of 3 turns. If you inventory turns are less than 3, then you will need to adjust the formula. 3) Other Current Assets Estimate for each year Other Current Assets as a % of Revenue. See limitation of 120 days described above. 4) Accounts Payable and Accrued Expenses Estimate for each year payables and accrued expenses a % of Revenue. The model shows the equivalent days outstanding. The formula for calculating payables and accrued expenses has a maximum of 120 days. If you expect to take longer than 120 days to pay your bills, then you will need to adjust the formula. For most industries, credit terms beyond 120 days is very unusual. 5) Other Current Liabilities Estimate for each year Other Current Liabilities as a % of Revenue. See limitation of 120 days described above.
Working Capital
Years 1 to 5
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Net Revenues 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Accounts Receivable
% of Revenue 8.3% 8.3% 8.3% 8.3% 8.3%

Moyes: Accounts Receivable See Notes above for restriction on Days Outstanding in calculating Accounts Receivable.		 Days Outstanding 30 30 30 30 30
Accounts Receivable 149,400 627,480 913,000 1,577,000 2,656,000
(Increase)/Decrease from Prev. Period (149,400) (478,080) (285,520) (664,000) (1,079,000)
Inventory
% of Revenue 8.3% 8.3% 6.7% 6.7% 6.7%
Inventory Turns
Moyes: Moyes: Inventory turns chancges each year to reflect the differnet mix of product and services
Frank Moyes: Inventory See Notes above for restrictions on Turns in calculating Inventory.
Compaq: Working Capital To project working capital: 1) Accounts Receivable Determine for each year accounts receivables as a % of Rrevenue (the average number of days outstanding is automatically calculated). Use the information collected in the Financial COMP's analysis to help estimate the percentage. The model will calculate the accounts receivable outstanding at the end of the year and month/quarter, based upon the monthly and quarterly revenue projections. The formula for calculating accounts receivable has a maximum of 120 days. If you expect the days outstanding to be greater than 120, then you will need to adjust the formula, or perhaps consider a different credit policy. 2) Inventory Determine for each year inventory as a % of Revenue (the inventory turns are automatically calculated). Use the information collected in the Financial COMP's analysis to help estimate the percentage. The model will calculate the inventory at the end of the year and month/quarter, based upon the monthly and quarterly revenue projections. The formula for calculating inventory has a minimum of 3 turns. If you inventory turns are less than 3, then you will need to adjust the formula. 3) Other Current Assets Estimate for each year Other Current Assets as a % of Revenue. See limitation of 120 days described above. 4) Accounts Payable and Accrued Expenses Estimate for each year payables and accrued expenses a % of Revenue. The model shows the equivalent days outstanding. The formula for calculating payables and accrued expenses has a maximum of 120 days. If you expect to take longer than 120 days to pay your bills, then you will need to adjust the formula. For most industries, credit terms beyond 120 days is very unusual. 5) Other Current Liabilities Estimate for each year Other Current Liabilities as a % of Revenue. See limitation of 120 days described above. 		12 12 15 15 15
Inventory Days 30 30 24 24 24
Inventory 149,400 627,480 737,000 1,273,000 2,144,000
(Increase)/Decrease from Prev. Period (149,400) (478,080) (109,520) (536,000) (871,000)
Other Current Assets
% of Revenue 1.0% 1.0% 1.0% 1.0% 1.0%
Days 4 4 4 4 4
Other CA Value 18,000 75,600 110,000 190,000 320,000
(Increase)/Decrease from Prev. Period (18,000) (57,600) (34,400) (80,000) (130,000)
Accounts Payable & Accrued Expenses
% of Revenue 8.7% 8.7% 8.7% 8.7% 8.7%
Days 31 31 31 31 31
AP & Accrued Value 155,720 657,720 957,000 1,653,000 2,784,000
Increase/(Decrease) from Prev. Period 155,720 502,000 299,280 696,000 1,131,000
Other Current Liabilites
% of Revenue 1.0% 1.0% 1.0% 1.0% 1.0%
Days 4 4 4 4 4
Other Current Liabilities 18,000 75,600 110,000 190,000 320,000
Increase/(Decrease) from Prev. Period 18,000 57,600 34,400 80,000 130,000
Revenues by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 0 262,500 0 0 0
Month 2 0 262,500 0 0 0
Month 3 0 367,500 0 0 0
Total 1st Quarter 0 892,500 2,000,000 3,000,000 5,000,000
Month 4 50,000 315,000 0 0 0
Month 5 80,000 367,500 0 0 0
Month 6 100,000 367,500 0 0 0
Total 2nd Quarter 230,000 1,050,000 2,500,000 4,000,000 6,000,000
Month 7 100,000 420,000 0 0 0
Month 8 120,000 525,000 0 0 0
Month 9 150,000 525,000 0 0 0
Total 3rd Quarter 370,000 1,470,000 2,500,000 4,500,000 7,000,000
Month 10 120,000 577,500 0 0 0
Month 11 130,000 630,000 0 0 0
Month 12 150,000 630,000 0 0 0
Total 4th Quarter 400,000 1,837,500 2,750,000 4,750,000 8,000,000
Total for year 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Accounts Receivable by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 - 0 261,450
Month 2 - 0 261,450
Month 3 - 0 366,030 664,000 996,000 1,660,000
Month 4 49,800 313,740
Month 5 79,680 366,030
Month 6 99,600 366,030 830,000 1,328,000 1,992,000
Month 7 99,600 418,320
Month 8 119,520 522,900
Month 9 149,400 522,900 830,000 1,494,000 2,324,000
Month 10 119,520 575,190
Month 11 129,480 627,480
Month 12 149,400 627,480 913,000 1,577,000 2,656,000
Inventory by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 - 0 261,450
Month 2 - 0 261,450
Month 3 - 0 366,030 536,000 804,000 1,340,000
Month 4 49,800 313,740
Month 5 79,680 366,030
Month 6 99,600 366,030 670,000 1,072,000 1,608,000
Month 7 99,600 418,320
Month 8 119,520 522,900
Month 9 149,400 522,900 670,000 1,206,000 1,876,000
Month 10 119,520 575,190
Month 11 129,480 627,480
Month 12 149,400 627,480 737,000 1,273,000 2,144,000
Other Current Assets by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 - 0 31,500
Month 2 - 0 31,500
Month 3 - 0 44,100 80,000 120,000 200,000
Month 4 6,000 37,800
Month 5 9,600 44,100
Month 6 12,000 44,100 100,000 160,000 240,000
Month 7 12,000 50,400
Month 8 14,400 63,000
Month 9 18,000 63,000 100,000 180,000 280,000
Month 10 14,400 69,300
Month 11 15,600 75,600
Month 12 18,000 75,600 110,000 190,000 320,000
Accounts Payble & Accrued Expenses by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 - 0 6,600
Month 2 0 0
Month 3 31 31 696,000 1,044,000 1,740,000
Month 4 50,000 331,170
Month 5 82,200 381,360
Month 6 103,520 383,670 870,000 1,392,000 2,088,000
Month 7 104,400 436,170
Month 8 124,400 543,480
Month 9 155,280 548,100 870,000 1,566,000 2,436,000
Month 10 126,600 600,600
Month 11 135,280 655,410
Month 12 155,720 657,720 957,000 1,653,000 2,784,000
Other Current Liabilities by Months & Quarters
($)
Year 1 Year 2 Year 3 Year 4 Year 5
Month 1 - 0 31,500
Month 2 - 0 31,500
Month 3 - 0 44,100 80,000 120,000 200,000
Month 4 6,000 37,800
Month 5 9,600 44,100
Month 6 12,000 44,100 100,000 160,000 240,000
Month 7 12,000 50,400
Month 8 14,400 63,000
Month 9 18,000 63,000 100,000 180,000 280,000
Month 10 14,400 69,300
Month 11 15,600 75,600
Month 12 18,000 75,600 110,000 190,000 320,000

&D &T

FUNDING

XYZ Company Notes
Compaq: Funding Look at Cash Flow projections to determine the amount of funding required. Decide whether equity or debt is most appropriate. For most start-ups, equity is required in the initial years. If you decide to use debt, determine the type of loan (long term or short term); repayment terms and interest rate. Interest Income not calculated automatically. Suggest look at cash balance on spreadsheet and make a rough approximation of the interest income, e.g. Cash balance at the beginning of the year is $250,000 Cash balance at the end of the year is 500,000 Average balance for the year 375,000 Interest rate on 30 day Treasury bills is 5% Interest income for Year 3 is 18,750 Timing of Funding The model assumes that equity and new debt funding occurs at the beginning of each year. Debt repayments take place at the end of each year and interest expense is calculated accordingly. If this is not the case, then manual adjustments to the monthly income statements, balance sheets and cash flow statements are required.
Funding Projections
Years 1 to 5
($)
Begin Year 1 Year 2 Year 3 Year 4 Year 5
Beginning Cash 1,500,000
Equity
Common 500,000 500,000 500,000 500,000 500,000 500,000
Increase / (Decrease) Previous Period 0 0 0 0 0
Preferred
A Round 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000
B Round 500,000 500,000 500,000 500,000
Total Preferred 1,000,000 1,000,000 1,500,000 1,500,000 1,500,000 1,500,000
Increase / (Decrease) Previous Period 0 500,000 0 0 0
Total Equity 1,500,000 1,500,000 2,000,000 2,000,000 2,000,000 2,000,000
Debt
Short Term Debt 100,000
Increase / (Decrease) Previous Period 0 0 0 100,000 (100,000)
Long Term Debt
Current Portion 100,000 100,000 100,000 100,000
Long Term Portion 400,000 300,000 200,000 100,000
Total Long Term Debt 0 500,000 400,000 300,000 200,000
Increase / (Decrease) Previous Period
Current Portion 0 100,000 0 0 0
Long Term Protion 0 400,000 (100,000) (100,000) (100,000)
Total Long Term Debt 0 500,000 (100,000) (100,000) (100,000)
Total Equity & Debt 1,500,000 1,500,000 2,500,000 2,400,000 2,400,000 2,200,000
Interest
Interest Rate
Short Term Debt 9.0% 9.0% 9.0% 9.0% 9.0%
Long Term Debt 12.0% 12.0% 12.0% 12.0% 12.0%
Interest Expense
Short Term Debt 0 0 0 9,000 0
Long Term Debt 0 60,000 48,000 36,000 24,000
Total Interest 0 60,000 48,000 45,000 24,000
Interest Income
Interest Rate 4.0% 4.0% 4.0% 4.0% 4.0%
Interest Income 0 0 0 0 10,000
Retained Earnings
Net Income (712,131) (218,064) 704,515 1,002,741 2,252,034
Dividends 50,000
Increase / (Decrease) Retained Earnings (712,131) (218,064) 704,515 1,002,741 2,202,034
Beginning Retained Earnings 0 (712,131) (930,195) (225,681) 777,060
Ending Retained Earnings (712,131) (930,195) (225,681) 777,060 2,979,094

&D &T

INCOME

XYZ Company
Income Statement
Years 1 to 5
($)
Year 1 Year 2 Year 3 Year 4 Year 5
NET REVENUES 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
COST OF REVENUE 1,117,464 3,532,164 5,699,429 9,339,286 14,033,143
% of Revenues 111.7% 67.3% 58.5% 57.5% 54.0%
GROSS PROFIT (117,464) 1,717,836 4,050,571 6,910,714 11,966,857
% of Revenues -11.7% 32.7% 41.5% 42.5% 46.0%
OPERATING EXPENSES
Sales & Marketing 229,000 997,750 1,805,400 2,911,500 4,676,000
Research & Development 149,000 413,000 702,550 1,241,500 1,752,000
General and Administration 171,667 465,150 790,107 1,191,933 1,771,467
Total Operating Expenses 549,667 1,875,900 3,298,057 5,344,933 8,199,467
% of Revenues 55% 36% 34% 33% 32%
EARNINGS FROM OPERATIONS (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
TAXES 0 0 0 (518,040) (1,501,356)
NET EARNINGS (712,131) (218,064) 704,515 1,002,741 2,252,034
% of Revenues -71.2% -4.2% 7.2% 6.2% 8.7%

&D &T

BALANCE

XYZ Company
Balance Sheet
Years 1 to 5
($)
Begin Year 1 Year 2 Year 3 Year 4 Year 5
ASSETS
CURRENT ASSETS
Cash 1,500,000 408,170 367,660 570,510 792,870 1,735,714
Accounts Receivable 149,400 627,480 913,000 1,577,000 2,656,000
Inventories 149,400 627,480 737,000 1,273,000 2,144,000
Other Current Assets 18,000 75,600 110,000 190,000 320,000
Total Current Assets 1,500,000 724,970 1,698,220 2,330,510 3,832,870 6,855,714
PROPERTY & EQUIPMENT 0 236,619 604,905 910,810 1,187,190 1,427,381
TOTAL ASSETS 1,500,000 961,589 2,303,125 3,241,319 5,020,060 8,283,094
LIABILITIES & SHAREHOLDERS' EQUITY
CURRENT LIABILITIES
Short Term Debt 0 0 0 0 100,000 0
Accounts Payable & Accrued Expen 155,720 657,720 957,000 1,653,000 2,784,000
Other Current Liab 18,000 75,600 110,000 190,000 320,000
Current portion of long term debt 0 0 100,000 100,000 100,000 100,000
Total Current Liabilities 0 173,720 833,320 1,167,000 2,043,000 3,204,000
LONG TERM DEBT (less current portion) 0 0 400,000 300,000 200,000 100,000
STOCKHOLDERS' EQUITY
CommonStock 500,000 500,000 500,000 500,000 500,000 500,000
Preferred Stock 1,000,000 1,000,000 1,500,000 1,500,000 1,500,000 1,500,000
Retained Earnings (712,131) (930,195) (225,681) 777,060 2,979,094
Total Equity 1,500,000 787,869 1,069,805 1,774,319 2,777,060 4,979,094
TOTAL LIABILITIES & EQUITY 1,500,000 961,589 2,303,125 3,241,319 5,020,060 8,283,094

&D &T

CASHFLOW

XYZ Company
Cash Flow Statememt
Years 1 to 5
($)
Year 1 Year 2 Year 3 Year 4 Year 5
OPERATING ACTIVITIES
Net Earnings (712,131) (218,064) 704,515 1,002,741 2,252,034
Depreciation 43,381 131,714 244,095 373,619 509,810
Working Capital Changes
(Increase)/Decrease Accounts Receivable (149,400) (478,080) (285,520) (664,000) (1,079,000)
(Increase)/Decrease Inventories (149,400) (478,080) (109,520) (536,000) (871,000)
(Increase)/Decrease Other Current Assets (18,000) (57,600) (34,400) (80,000) (130,000)
Increase/(Decrease) Accts Pay & Accrd Expenses 155,720 502,000 299,280 696,000 1,131,000
Increase/(Decrease) Other Current Liab 18,000 57,600 34,400 80,000 130,000
Net Cash Provided/(Used) by Operating Activities (811,830) (540,510) 852,850 872,360 1,942,844
INVESTING ACTIVITIES
Property & Equipment (280,000) (500,000) (550,000) (650,000) (750,000)
Other
Net Cash Used in Investing Activities (280,000) (500,000) (550,000) (650,000) (750,000)
FINANCING ACTIVITIES
Increase/(Decrease) Short Term Debt 0 0 0 100,000 (100,000)
Increase/(Decrease) Curr. Portion LTD 0 100,000 0 0 0
Increase/(Decrease) Long Term Debt 0 400,000 (100,000) (100,000) (100,000)
Increase/(Decrease) Common Stock 0 0 0 0 0
Increase/(Decrease) Preferred Stock 0 500,000 0 0 0
Dividends Declared 0 0 0 0 (50,000)
Net Cash Provided / (Used) by Financing 0 1,000,000 (100,000) 0 (250,000)
INCREASE/(DECREASE) IN CASH (1,091,830) (40,510) 202,850 222,360 942,844
CASH AT BEGINNING OF YEAR 1,500,000 408,170 367,660 570,510 792,870
CASH AT END OF YEAR 1,500,000 408,170 367,660 570,510 792,870 1,735,714

&D &T

BREAKEVEN

XYZ Company Notes
Compaq: Break-even Analysis Look at the Costs of Revenues and determine which costs are fixed and variable. Conduct a similar analysis of the Operating Expenses. Input these numbers in the appropriate cells of those worksheets. The break-even revenue is calculated automatically. To determine the break-even quantity, divide the break-even revenue by the average selling price per customer or unit sold that is assumed in the REVENUE spreadsheet.
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 0
Variable 729,750 2,450,450 4,122,000 7,139,000 10,990,000
Fixed 387,714 1,081,714 1,577,429 2,200,286 3,043,143
Total 1,117,464 3,532,164 5,699,429 9,339,286 14,033,143
Operating Expenses
Variable 160,000 840,000 1,560,000 2,600,000 4,160,000
Fixed 389,667 1,035,900 1,738,057 2,744,933 4,039,467
Total 549,667 1,875,900 3,298,057 5,344,933 8,199,467
Total Costs & Expenses
Variable 889,750 3,290,450 5,682,000 9,739,000 15,150,000
Fixed 777,381 2,117,614 3,315,485 4,945,219 7,082,610
Total 1,667,131 5,408,064 8,997,485 14,684,219 22,232,610
Variable Costs/Revenue Ratio 0.89 0.63 0.58 0.60 0.58
Break-Even Point Revenues 7,051,076 5,673,484 7,946,406 12,342,161 16,972,152

&D &T

INCOME-MOS

XYZ Company
Income Statements
5 Years by Months & Quarters
($)
XYZ Company
Income Statement Year 1
Year 1 by Months Total Annual
Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 12 Months Projection
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 0 0 0 11,450 18,320 22,900 22,900 27,480 34,350 27,480 29,770 34,350 229,000 229,000
Research & Development 0 0 0 7,450 11,920 14,900 14,900 17,880 22,350 17,880 19,370 22,350 149,000 149,000
General and Administration 0 0 0 8,583 13,733 17,167 17,167 20,600 25,750 20,600 22,317 25,750 171,667 171,667
Total Operating Expenses 0 0 0 27,483 43,973 54,967 54,967 65,960 82,450 65,960 71,457 82,450 549,667 549,667
EARNINGS FROM OPERATIONS 0 0 0 (33,357) (53,370) (66,713) (66,713) (80,056) (100,070) (80,056) (86,727) (100,070) (667,131) (667,131)
EXTRAORDINARY INCOME / (EXPENSE) (25,000) (20,000) 0 0 0 0 0 0 0 0 0 0 (45,000) (45,000)
EARNINGS BEFORE INTEREST & TAXES (25,000) (20,000) 0 (33,357) (53,370) (66,713) (66,713) (80,056) (100,070) (80,056) (86,727) (100,070) (712,131) (712,131)
INTEREST INCOME / (EXPENSE) 0 0 0 0 0 0 0 0 0 0 0 0 0 0
NET EARNINGS BEFORE TAXES (25,000) (20,000) 0 (33,357) (53,370) (66,713) (66,713) (80,056) (100,070) (80,056) (86,727) (100,070) (712,131) (712,131)
TAXES 0 0 0 0 0 0 0 0 0 0 0 0 0 0
NET EARNINGS (25,000) (20,000) 0 (33,357) (53,370) (66,713) (66,713) (80,056) (100,070) (80,056) (86,727) (100,070) (712,131) (712,131)
XYZ Company
Income Statement Year 2
Year 2 by Months Total Annual
Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 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 49,888 49,888 69,843 59,865 69,843 69,843 79,820 99,775 99,775 109,753 119,730 119,730 997,750 997,750
Research & Development 20,650 20,650 28,910 24,780 28,910 28,910 33,040 41,300 41,300 45,430 49,560 49,560 413,000 413,000
General and Administration 23,258 23,258 32,561 27,909 32,561 32,561 37,212 46,515 46,515 51,167 55,818 55,818 465,150 465,150
Total Operating Expenses 93,795 93,795 131,313 112,554 131,313 131,313 150,072 187,590 187,590 206,349 225,108 225,108 1,875,900 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) 0 0 0 0 0 0 0 0 0 0 0 0 0 0
EARNINGS BEFORE INTEREST & TAXES (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)
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 0 0 0 0 0 0 0 0 0 0 0 0 0 0
NET EARNINGS (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)
XYZ Company
Income Statement Year 3 Year 4 Year 5
Years 3, 4 & 5 by Quarters Annual Annual Annual
Year 3 Year 4 Year 5 Projection Projection Projection
1st Qtr 2nd Qrtr 3rd Qrtr 4th Qrtr Total 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 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 9,750,000 16,250,000 26,000,000
0 0 0
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 5,699,429 9,339,286 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 370,338 462,923 462,923 509,215 1,805,400 537,508 716,677 806,262 851,054 2,911,500 899,231 1,079,077 1,258,923 1,438,769 4,676,000 1,805,400 2,911,500 4,676,000
Research & Development 144,113 180,141 180,141 198,155 702,550 229,200 305,600 343,800 362,900 1,241,500 336,923 404,308 471,692 539,077 1,752,000 702,550 1,241,500 1,752,000
General and Administration 162,073 202,591 202,591 222,851 790,107 220,049 293,399 330,074 348,411 1,191,933 340,667 408,800 476,933 545,067 1,771,467 790,107 1,191,933 1,771,467
Total Operating Expenses 676,524 845,656 845,656 930,221 3,298,057 986,757 1,315,676 1,480,135 1,562,365 5,344,933 1,576,821 1,892,185 2,207,549 2,522,913 8,199,467 3,298,057 5,344,933 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
EXTRAORDINARY INCOME / (EXPENSE) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
EARNINGS BEFORE INTEREST & TAXES 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
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
TAXES 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)
NET EARNINGS 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
Do Not Delete
Calculation Area
Tax Loss Carry Forward
Interest Income/(Expense)
Tax Loss Carry Forward
Cumulative Net Earning Before Taxes 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 (25,000) (45,000) (45,000) (78,357) (131,727) (198,440) (265,153) (345,209) (445,279) (525,334) (612,061) (712,131)
Year 2 (725,034) (737,938) (754,002) (768,486) (784,550) (800,615) (818,260) (839,066) (859,873) (882,260) (906,228) (930,195)
Cumulative Net Earning Before Taxes Year 3 Year 4 Year 5
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Quarter 1 Quarter 2 Quarter 3 Quarter 4
(787,833) (606,881) (425,928) (225,681) 52,137 426,310 848,661 1,295,100 2,016,098 2,881,996 3,892,794 5,048,491
Interest Income/(Expense) Total Year Per Month/Qtr
Year 1 0 0
Year 2 (60,000) (5,000)
Year 3 (48,000) (12,000)
Year 4 (45,000) (11,250)
Year 5 (14,000) (3,500)

&D &T

Note: these two columns must be the same

CASHFLOW-MOS

XYZ Company
Cash Flow Statememt
Year 1 by Months
($)
XYZ Company
Cash Flow Statement Year 1
Year 1 by Months Estimated Annual
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 Projection
OPERATING ACTIVITIES
Net Earnings (25,000) (20,000) 0 (33,357) (53,370) (66,713) (66,713) (80,056) (100,070) (80,056) (86,727) (100,070) (712,131) (712,131)
Depreciation 3,615 3,615 3,615 3,615 3,615 3,615 3,615 3,615 3,615 3,615 3,615 3,615 43,381 43,381
Working Capital Changes
(Increase)/Decrease Accounts Receivable 0 0 0 (49,800) (29,880) (19,920) 0 (19,920) (29,880) 29,880 (9,960) (19,920) (149,400) (149,400)
(Increase)/Decrease Inventories 0 0 0 (49,800) (29,880) (19,920) 0 (19,920) (29,880) 29,880 (9,960) (19,920) (149,400) (149,400)
(Increase)/Decrease Other Current Assets 0 0 0 (6,000) (3,600) (2,400) 0 (2,400) (3,600) 3,600 (1,200) (2,400) (18,000) (18,000)
Increase/(Decrease) Accts Pay & Accrd Expenses 0 0 31 49,969 32,200 21,320 880 20,000 30,880 (28,680) 8,680 20,440 155,720 155,720
Increase/(Decrease) Other Current Liab 0 0 0 6,000 3,600 2,400 0 2,400 3,600 (3,600) 1,200 2,400 18,000 18,000
Net Cash Provided/(Used) by Operating Activities (21,385) (16,385) 3,646 (79,373) (77,315) (81,618) (62,218) (96,281) (125,335) (45,361) (94,352) (115,855) (811,830) (811,830)
INVESTING ACTIVITIES
Property & Equipment (50,000) (100,000) (50,000) (40,000) 0 0 (40,000) 0 0 0 0 0 (280,000) (280,000)
Other 0 0
Net Cash Used in Investing Activities (50,000) (100,000) (50,000) (40,000) 0 0 (40,000) 0 0 0 0 0 (280,000) (280,000)
FINANCING ACTIVITIES
Increase/(Decrease) Short Term Debt 0 0 0 0
Increase/(Decrease) Curr. Portion LTD 0 0 0 0
Increase/(Decrease) Long Term Debt 0 0 0 0
Increase/(Decrease) Common Stock 0 0 0 0
Increase/(Decrease) Preferred Stock 0 0 0 0
Dividends Declared 0 0 0
Net Cash Provided / (Used) by Financing 0 0 0 0 0 0 0 0 0 0 0 0 0 0
INCREASE/(DECREASE) IN CASH (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,091,830) (1,091,830)
CASH AT BEGINNING OF PERIOD 1,500,000 1,428,615 1,312,230 1,265,877 1,146,504 1,069,188 987,570 885,352 789,072 663,737 618,376 524,024 1,500,000
CASH AT END OF PERIOD ERROR:#NAME? 1,428,615 1,312,230 1,265,877 1,146,504 1,069,188 987,570 885,352 789,072 663,737 618,376 524,024 408,170 408,170
XYZ Company
Cash Flow Statement Year 2
Year 2 by Months Estimated Annual
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 Projection
OPERATING ACTIVITIES
Net Earnings (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)
Depreciation 10,976 10,976 10,976 10,976 10,976 10,976 10,976 10,976 10,976 10,976 10,976 10,976 131,714 131,714
Working Capital Changes 0
(Increase)/Decrease Accounts Receivable (112,050) 0 (104,580) 52,290 (52,290) 0 (52,290) (104,580) 0 (52,290) (52,290) 0 (478,080) (478,080)
(Increase)/Decrease Inventories (112,050) 0 (104,580) 52,290 (52,290) 0 (52,290) (104,580) 0 (52,290) (52,290) 0 (478,080) (478,080)
(Increase)/Decrease Other Current Assets (13,500) 0 (12,600) 6,300 (6,300) 0 (6,300) (12,600) 0 (6,300) (6,300) 0 (57,600) (57,600)
Increase/(Decrease) Accts Pay & Accrd Expenses (149,120) (6,600) 31 331,139 50,190 2,310 52,500 107,310 4,620 52,500 54,810 2,310 502,000 502,000
Increase/(Decrease) Other Current Liab 13,500 0 12,600 (6,300) 6,300 0 6,300 12,600 0 6,300 6,300 0 57,600 57,600
Net Cash Provided/(Used) by Operating Activities (375,147) (8,527) (214,217) 432,211 (59,478) (2,778) (58,749) (111,680) (5,210) (63,491) (62,762) (10,682) (540,510) (540,510)
0
INVESTING ACTIVITIES 0
Property & Equipment (100,000) (100,000) 0 0 0 (100,000) 0 0 (100,000) 0 0 (100,000) (500,000) (500,000)
Other 0 0
Net Cash Used in Investing Activities (100,000) (100,000) 0 0 0 (100,000) 0 0 (100,000) 0 0 (100,000) (500,000) (500,000)
0
FINANCING ACTIVITIES 0
Increase/(Decrease) Short Term Debt 0 0 0 0
Increase/(Decrease) Curr. Portion LTD 100,000 0 100,000 100,000
Increase/(Decrease) Long Term Debt 400,000 0 400,000 400,000
Increase/(Decrease) Common Stock 0 0 0 0
Increase/(Decrease) Preferred Stock 500,000 0 500,000 500,000
Dividends Declared 0 0 0
Net Cash Provided / (Used) by Financing 1,000,000 0 0 0 0 0 0 0 0 0 0 0 1,000,000 1,000,000
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) (40,510) (40,510)
CASH AT BEGINNING OF PERIOD 408,170 933,023 824,496 610,279 1,042,490 983,012 880,233 821,484 709,804 604,594 541,103 478,341 408,170
CASH AT END OF PERIOD ERROR:#NAME? 933,023 824,496 610,279 1,042,490 983,012 880,233 821,484 709,804 604,594 541,103 478,341 367,660 367,660
XYZ Company
Cash Flow Statement Year 3 Year 4 Year 5
Years 3, 4 & 5 by Quarters Annual Annual Annual
Year 3 Year 4 Year 5 Projection Projection Projection
1st Qtr 2nd Qrtr 3rd Qrtr 4th Qrtr Total 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
OPERATING ACTIVITIES
Net Earnings 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
Depreciation 61,024 61,024 61,024 61,024 244,095 93,405 93,405 93,405 93,405 373,619 127,452 127,452 127,452 127,452 509,810 244,095 373,619 509,810
Working Capital Changes
(Increase)/Decrease Accounts Receivable (36,520) (166,000) 0 (83,000) (285,520) (83,000) (332,000) (166,000) (83,000) (664,000) (83,000) (332,000) (332,000) (332,000) (1,079,000) (285,520) (664,000) (1,079,000)
(Increase)/Decrease Inventories 91,480 (134,000) 0 (67,000) (109,520) (67,000) (268,000) (134,000) (67,000) (536,000) (67,000) (268,000) (268,000) (268,000) (871,000) (109,520) (536,000) (871,000)
(Increase)/Decrease Other Current Assets (4,400) (20,000) 0 (10,000) (34,400) (10,000) (40,000) (20,000) (10,000) (80,000) (10,000) (40,000) (40,000) (40,000) (130,000) (34,400) (80,000) (130,000)
Increase/(Decrease) Accts Pay & Accrd Expenses 38,280 174,000 0 87,000 299,280 87,000 348,000 174,000 87,000 696,000 87,000 348,000 348,000 348,000 1,131,000 299,280 696,000 1,131,000
Increase/(Decrease) Other Current Liab 4,400 20,000 0 10,000 34,400 10,000 40,000 20,000 10,000 80,000 10,000 40,000 40,000 40,000 130,000 34,400 80,000 130,000
Net Cash Provided/(Used) by Operating Activities 296,626 115,976 241,976 198,272 852,850 213,586 88,119 245,886 324,769 872,360 497,051 394,991 481,931 568,871 1,942,844 852,850 872,360 1,942,844
INVESTING ACTIVITIES
Property & Equipment (100,000) (200,000) (100,000) (150,000) (550,000) (200,000) (200,000) (100,000) (150,000) (650,000) (200,000) (200,000) (200,000) (150,000) (750,000) (550,000) (650,000) (750,000)
Other 0 0 0 0 0
Net Cash Used in Investing Activities (100,000) (200,000) (100,000) (150,000) (550,000) (200,000) (200,000) (100,000) (150,000) (650,000) (200,000) (200,000) (200,000) (150,000) (750,000) (550,000) (650,000) (750,000)
FINANCING ACTIVITIES
Increase/(Decrease) Short Term Debt 0 0 0 100,000 0 100,000 0 (100,000) (100,000) 0 100,000 (100,000)
Increase/(Decrease) Curr. Portion LTD 0 0 0 0 0 0 0 0 0 0 0 0
Increase/(Decrease) Long Term Debt 0 (100,000) (100,000) 0 (100,000) (100,000) 0 (100,000) (100,000) (100,000) (100,000) (100,000)
Increase/(Decrease) Common Stock 0 0 0 0 0 0 0 0 0 0 0 0
Increase/(Decrease) Preferred Stock 0 0 0 0 0 0 0 0 0 0 0 0
Dividends Declared 0 0 0 0 (50,000) (50,000) 0 0 (50,000)
Net Cash Provided / (Used) by Financing 0 0 0 (100,000) (100,000) 100,000 0 0 (100,000) 0 0 0 0 (250,000) (250,000) (100,000) 0 (250,000)
INCREASE/(DECREASE) IN CASH 196,626 (84,024) 141,976 (51,728) 202,850 113,586 (111,881) 145,886 74,769 222,360 297,051 194,991 281,931 168,871 942,844 202,850 222,360 942,844
CASH AT BEGINNING OF PERIOD 367,660 564,286 480,262 622,238 570,510 684,096 572,215 718,101 792,870 1,089,921 1,284,912 1,566,843 367,660 570,510 792,870
CASH AT END OF PERIOD 564,286 480,262 622,238 570,510 684,096 572,215 718,101 792,870 1,089,921 1,284,912 1,566,843 1,735,714 570,510 792,870 1,735,714

&D &T

SUMMARY

XYZ Company
Summary
Years 1 to 5
Year 1 Year 2 Year 3 Year 4 Year 5
Summary Financials ($)
Revenue 1,000,000 5,250,000 9,750,000 16,250,000 26,000,000
Gross Profit (117,464) 1,717,836 4,050,571 6,910,714 11,966,857
EBIT (712,131) (158,064) 752,515 1,565,781 3,767,390
EBITDA (668,750) (26,350) 996,610 1,939,400 4,277,200
Net Earnings (712,131) (218,064) 704,515 1,002,741 2,252,034
Net Cash from Operating Activities (811,830) (540,510) 852,850 872,360 1,942,844
Capital Expenditures 280,000 500,000 550,000 650,000 750,000
Interest Income/(Expense) 0 (60,000) (48,000) (45,000) (14,000)
Dividends 0 0 0 0 50,000
Cash 408,170 367,660 570,510 792,870 1,735,714
Total Equity (212,131) (430,195) 274,319 1,277,060 3,479,094
Total Debt 0 500,000 400,000 300,000 200,000
Growth
Revenue Growth Rate - CAGR: 425% 86% 67% 60%
Net Earnings Growth Rate - CAGR: Nil Nil 42.3% 124.6%
Ratios
Current Ratio 4.2 2.0 2.0 1.9 2.1
Debt to Capital (LT Debt + Equity) 0.0 0.3 0.2 0.1 0.0
Profitability
Gross Profit % -11.7% 32.7% 41.5% 42.5% 46.0%
Operating Expenses % 55.0% 35.7% 33.8% 32.9% 31.5%
Net Earnings % -71.2% -4.2% 7.2% 6.2% 8.7%
Returns
Return on Assets -74.1% -9.5% 21.7% 20.0% 27.2%
Return on Equity -90.4% -20.4% 39.7% 36.1% 45.2%
Return on Capital (LT Debt + Equity) -90.4% -13.9% 32.4% 32.6% 43.5%

&D &T

VAL-1

XYZ Company Notes
Compaq: Valuation The venture capital method assumes that a firm will undertake an Initial Public Offering (IPO) at some point in the future or be acquired, ie a “liquidity event”. The future value of the firm is determined by multiplying the earnings of the firm in the year of the liquidity event by the expected price/earnings (P/E) ratio that the market will support. (The long-run P/E ratio of NYSE stocks is about 15.) This provides the expected future value of the firm. The present value of the firm is then calculated using a risk adjusted discount rate. Discount rates of 50 to 100% (and more) are frequently used in valuing start-up businesses to capture the inherently risky nature of new ventures. Similarly, venture capitalists frequently demand an Internal Rate of Return (IRR) of 100% (or more) in order to justify investing in a risky startup. (An IRR of 100% is equivalent to doubling the value of an investment every year.) There are two valuation worksheets provided in the model: VAL-1 estimates the value of the company based on an initial investment of the start of the venture and a one-time liquidity event. The net income estimates for each of the five years need to be inputted manually, but you can easily make links directly to Net Income line in the Income Statement projections. Use the Negotiation Workspace to test various dilution assumptions. Val-2 estimates the value of the company based on multiple rounds of investment. The timing of the each round of Investment and the IPO can be modified. Use the Negotiation Workspace to test various dilution assumptions.
Valuation
Venture Capital Method
Years 1 to 5
Assumptions: REF
Investor required IRR 60% A
P/E ratio at IPO or acquisition 15 B
Initial investment $1,500,000 C FV(A,C) G/F IRR(D,G)
REF D E F G H I J K
Valuation Calculation Year Net Income Market Capitalization Required Future Value (Investor) Investor's Share Investor's Return Investor's ROI Investor's IRR IRR Calculation Workspace
Calculation Income Stmt B * E FV(F,D) C / G F * H I / C IRR(I,C,D) Start Year 1 Year 2 Year 3 Year 4 Year 5
Liquidity Event in Year 1 1 (712,131) ($10,681,967) $2,400,000 100.0% $0 0% ERROR:#N/A ($1,500,000) $0
Liquidity Event in Year 2 2 (218,064) ($3,270,964) $3,840,000 100.0% $0 0% ERROR:#N/A ($1,500,000) $0 $0
Liquidity Event in Year 3 3 704,515 $10,567,721 $6,144,000 58.1% $6,144,000 410% 60% ($1,500,000) $0 $0 $6,144,000
Liquidity Event in Year 4 4 1,002,741 $15,041,112 $9,830,400 65.4% $9,830,400 655% 60% ($1,500,000) $0 $0 $0 $9,830,400
Liquidity Event in Year 5 5 2,252,034 $33,780,514 $15,728,640 46.6% $15,728,640 1049% 60% ($1,500,000) $0 $0 $0 $0 $15,728,640
Negotiation Workspace
Year Investor's Share Investor's Return Investor's ROI Investor's IRR
1 25.0% $0 0% ERROR:#N/A ($1,500,000) $0
2 25.0% $0 0% ERROR:#N/A ($1,500,000) $0 $0
3 25.0% $2,641,930 176% 21% ($1,500,000) $0 $0 $2,641,930
4 25.0% $3,760,278 251% 26% ($1,500,000) $0 $0 $0 $3,760,278
5 40.0% $13,512,206 901% 55% ($1,500,000) $0 $0 $0 $0 $13,512,206

&D &T

VAL-2

XYZ Company Notes
Compaq: Valuation The venture capital method assumes that a firm will undertake an Initial Public Offering (IPO) at some point in the future or be acquired, ie a “Liquidity Event”. The future value of the firm is determined by multiplying the earnings of the firm in the year of the Liquidity Event by the expected price/earnings (P/E) ratio that the market will support. (The long-run P/E ratio of NYSE stocks is about 15.) This provides the expected future value of the firm. The present value of the firm is then calculated using a risk adjusted discount rate. Discount rates of 50 to 100% (and more) are frequently used in valuing start-up businesses to capture the inherently risky nature of new ventures. Similarly, venture capitalists frequently demand an Internal Rate of Return (IRR) of 100% (or more) in order to justify investing in a risky startup. (An IRR of 100% is equivalent to doubling the value of an investment every year.) There are two valuation worksheets provided in the model: VAL-1 estimates the value of the company based on an initial investment of the start of the venture and a one-time liquidity event. The net income estimates for each of the five years need to be inputted manually, but you can easily make links directly to Net Income line in the Income Statement projections. Use the Negotiation Workspace to test various dilution assumptions. Val-2 estimates the value of the company based on multiple rounds of investment. The timing of the each round of Investment and the IPO can be modified. Use the Negotiation Workspace to test various dilution assumptions.
Valuation
Venture Capital Method
Years 1 to 5
Multiple Rounds
Assumptions Ref Calculation
Month of IPO 60 A
Forecast annualized earnings at IPO $2,252,034 B
P/E ratio at IPO 15 C
Investment Round First Second Third
Month of Investment 0 24 C
Investor required IRR 60% 50% D
Amount of Investment $1,500,000 $500,000 E
Required Monthly IRR 5.00% 4.17% 0.00% F D/12
Duration of Investment 60 36 60 G A-C
Calculations
Market Capitalization at IPO $33,780,514 H B*C
First Second Third
Required FV for Investor at IPO $15,728,640 $1,687,500 $0 I E*(1+F)^G
Individual Investor's Share 46.6% 5.0% 0.0% J I/H
Individual Investor's ROI 1049% 338% ERROR:#DIV/0! K I/E
Individual Investor's IRR 60% 50% ERROR:#DIV/0! L (I/E)^(12/G)-1
Cumulative Investors' Share 46.6% 51.6% 51.6% M sum(J)
Cumulative Founders' Share 53.4% 48.4% 48.4% N 1-M
Negotiations Round First Second Third
Individual Investor's Share 15.0% 5.0% 3.0%
FV for Investor at IPO $5,067,077 $1,689,026 $1,013,415
Individual Investor's ROI 338% 338% ERROR:#DIV/0!
Individual Investor's IRR 28% 50% ERROR:#DIV/0!

&D &T