Export Summary
| | This document was exported from Numbers. Each table was converted to an Excel worksheet. All other objects on each Numbers sheet were placed on separate worksheets. Please be aware that formula calculations may differ in Excel. |
| | Numbers Sheet Name | Numbers Table Name | Excel Worksheet Name |
| | Instructions |
| | | Table 1 | Instructions |
| | 2.1 & 2.2 Sales Forecast |
| | | Table 1 | 2.1 & 2.2 Sales Forecast |
| | 3.0 Capital Expenditure Budget |
| | | Table 1 | 3.0 Capital Expenditure Budget |
| | 4.1 Cashflows |
| | | Table 1 | 4.1 Cashflows |
| | 4.2 NPV Analysis |
| | | Table 1 | 4.2 NPV Analysis |
| | 4.3 Rate of Return Calculations |
| | | Table 1 | 4.3 Rate of Return Calculations |
| | 4.4 Payback Period Calculations |
| | | Table 1 | 4.4 Payback Period Calculations |
| | 5.0 Pro Forma Financials |
| | | Table 1 | 5.0 Pro Forma Financials |
Instructions
| BUSN278 Budgeting and Forecasting Template Instructions |
| Use this spreadsheet structure to lay out the various sections of your project. |
| The purpose of this spreadsheet is to make it easy for your professor to locate the |
| various sections of your project. Please don't alter the worksheet tabs or titles. |
| After you finish your calculations in this spreadsheet, you will have to |
| create a written report in which you take screenshots from this spreadsheet |
| and put them in the Budget Proposal Template, along with necessary |
| explanations. Detailed instructions for how to write the report |
| are found in the Budget Proposal Template, a Word document. |
&"Helvetica Neue,Regular"&12&K000000&P
2.1 & 2.2 Sales Forecast
| sales | year 1 | year 2 | year 3 | year 4 | years 5 |
| pizza | $560,000 | $1,053,000 | $1,158,000 | $1,274,120 | $1,401,540 |
| soda | $45,100 | $89,000 | $98,000 | $107,000 | $120,000 |
| salad | $46,000 | $93,000 | $105,000 | $117,000 | $125,000 |
| pastas | $374,850 | $737,200 | $820,000 | $900,000 | 981,000 |
| soup | $39,000 | $75,000 | $89,000 | $95,000 | $104,500 |
| desserts | $55,200 | $110,500 | $120,100 | $132,800 | $148,000 |
| total sales | $1,074,150 | $2,157,700 | $2,390,100 | $2,625,920 | $2,880,040 |
&"Helvetica Neue,Regular"&12&K000000&P
3.0 Capital Expenditure Budget
| year 1 | year 2 | year 3 | year 4 | year 5 |
| $204,360 | $393,000 | $411,800 | $440,500 | $460,700 | (payroll) |
| $50,000 | $53,000 | $53,000 | $52,000 | $50,000 | (marketing) |
| $25,000 | $30,000 | $34,000 | $37,000 | $41,000 | (utilities) |
| $12,000 | $38,000 | $43,000 | $44,000 | $45,000 | (repairs/maintainance) |
| $2,400 | $10,000 | $11,000 | $13,000 | $15,200 | (dishes/ cleaning supplies) |
| $15,000 | $18,000 | $22,000 | $24,000 | $31,000 | (employee healthcare) |
| $17,000 | $50,000 | $50,000 | $50,000 | $5,000 | (rent) |
| $325,760 | $606,000 | $624,800 | $660,500 | $647,900 |
&"Helvetica Neue,Regular"&12&K000000&P
4.1 Cashflows
| year 1 | year 2 | year 3 | year 4 | year 5 |
| $1,074,150 | $2,157,700 | $2,390,100 | $2,625,920 | $2,880,040 | (cash sales) |
| $69,045 | $134,673 | $148,117 | $162,899 | $179,146 | (taxes) |
| $10,300 | $12,000 | $13,000 | $14,000 | $15,000 | (sales of current assets) |
| ($204,360) | ($393,200) | ($411,860) | ($431,453) | ($451,717) | (cash spending) |
| ($570,420) | ($1,380,599) | ($1,493,514) | ($1,619,145) | ($1,758,632) | (bill payment) |
| $378,715 | $530,574 | $645,843 | $752,221 | $863,837 | (net cash flow) |
| $495,311 | $1,112,740 | $1,537,093 | $2,508,380 | $2,647,942 | (cash balance) |
&"Helvetica Neue,Regular"&12&K000000&P
4.2 NPV Analysis
| npv |
| year 1 | year 2 | year 3 | year 4 | year 5 |
| $378,715 | $530,574 | $645,843 | $752,221 | $863,837 |
| $360,680.00 | $481,246 | $557,903 | $618,854 | $676,838 |
| Npv | $2,695,521 |
&"Helvetica Neue,Regular"&12&K000000&P
4.3 Rate of Return Calculations
| | current value of investment | original value of investment | rate of return |
| year 1 | 1,000,000 | 250,000 | 400 |
| year 2 | 2,000,000 | 1,000,000 | 200 |
| year 3 | 2,200,000 | 1,500,000 | 147 |
| year 4 | 2,500,000 | 1,700,000 | 147 |
| year 5 | 2,900,000 | 1,900,000 | 152 |
&"Helvetica Neue,Regular"&12&K000000&P
4.4 Payback Period Calculations
| | cash inflow | cumulative cash flows | initial oulay | remaining payment |
| year1 | $378,715 | $378,715 | -1,000,000 | -621,285 |
| year 2 | $530,574 | $909,289 | | 288,004 |
| year 3 | $645,843 | $1,555,132 |
| year 4 | $752,221 | $2,307,353 |
| year 5 | $863,837 | $3,171,190 |
| | $3,171,190 |
| | | payback period is two years |
&"Helvetica Neue,Regular"&12&K000000&P
5.0 Pro Forma Financials
| pro formula income statement |
| | year 1 | year 2 | year 3 | year 4 | year 5 |
| net sales | $1,074,150 | $2,157,700 | $2,390,100 | $2,625,920 | $2,880,040 |
| direct costs of sales | $375,103 | $700,340 | $770,238 | $847,101 | $931,564 |
| operating expenses | $325,760 | $606,000 | $624,800 | $660,500 | $647,900 |
| taxes incurred | $114,899 | $200,001 | $238,831 | $281,506 | $328,753 |
| net profits | $258,388 | $651,359 | 756,231 | $836,813 | $971,823 |
| pro forma cash flows |
| cash sales | $1,074,150 | $2,157,700 | $2,390,100 | $2,625,920 | $2,880,040 |
| sales of current assets | $10,300 | $12,000 | $13,000 | $14,000 | $15,000 |
| cash spending | ($204,360) | ($393,200) | ($411,860) | ($431,453) | ($451,717) |
| additional cash spent | ($570,420) | ($1,380,599) | ($1,493,514) | ($1,619,145) | ($1,758,632) |
| net cash flow | $309,670 | $395,901 | $497,726 | $589,322 | $684,691 |
| cash balance | $495,311 | $1,112,740 | $1,537,093 | $2,508,380 | $2,647,942 |
| pro forma balance sheet |
| cash | $495,311 | $1,112,740 | $1,537,093 | $2,508,380 | $2,647,942 |
| inventory | $14,331 | $26,756 | $29,427 | $32,364 | $35,591 |
| Other Current Assets | ($10,300) | ($22,300) | ($35,300) | ($49,300) | ($64,300) |
| net current assets | $499,342 | $1,117,196 | $1,531,220 | $2,491,444 | $2,619,233 |
| longterm assets | $0 | $0 | $0 | $0 | $0 |
| liabilities |
| current borrowing | 0 | $0 | $0 | $0 | $0 |
| accounts payable | $98,320 | $114,831 | $123,464 | $133,942 | $145,495 |
| other current liabilities | $69,045 | $203,718 | $351,835 | $514,734 | $693,880 |
| net current liabilities | $167,364 | $318,549 | $475,299 | $648,675 | $839,374 |
| earnings | $258,388 | $651,359 | 756,231 | $836,813 | $971,823 |
&"Helvetica Neue,Regular"&12&K000000&P