Course Project Overview wk 5
whtifields
AuthorleeS_WK4BudgetProposalWorkbook.xlsxInstructions
| 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. |
2.1 & 2.2 Sales Forecast
| Put your sales forecasting calculations here. | |||||
| Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |
| Sales - Products | 500,000 | 535,500 | 573,521 | 614,240 | 657,852 |
| Sales - Services | 7,500 | 8,033 | 8,603 | 9,214 | 9,868 |
| Products - Volume | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Sales - units | 500.00 | 525.00 | 551.25 | 578.81 | 607.75 |
| Avg. Price / unit | 1,000.00 | 1,020.00 | 1,040.40 | 1,061.21 | 1,082.43 |
| Services - Volume | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Service units | 75.00 | 78.75 | 82.69 | 86.82 | 91.16 |
| Avg. Price / Service | 100.00 | 102.00 | 104.04 | 106.12 | 108.24 |
| Sales - ( $ ) | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Sales - Product ( $ ) | 500,000 | 535,500 | 573,521 | 614,240 | 657,852 |
| Sales - Services ( $ ) | 7,500 | 8,033 | 8,603 | 9,214 | 9,868 |
3.0 Capital Expenditure Budget
| Description | Investment | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Filling Fees | 160 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Renovation | 25,000 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Rent | 5,000 | 5,250 | 5,513 | 5,788 | 6,078 | 6,381 |
| Advertisement | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 |
| Utilities | 1,000 | 2,000 | 2,000 | 2,000 | 2,000 | 2,000 |
| Initial Inventory | 20,000 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Total Cost | 52,160 | 8,250 | 8,513 | 8,788 | 9,078 | 9,381 |
| Initial Investment from owner | 52,160 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Interest payable @ 3.50% | 53,986 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Total CAPITAL EXPENDITURE | 52,160 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! |
4.1 Cashflows
| Put your detailed cash inflows and cash outflows here, also showing net cash flow. | ||||||
| Description | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Cash inflow | ||||||
| Sales - Products | - 0 | 500,000.00 | 535,500.00 | 573,520.50 | 614,240.46 | 657,851.53 |
| Sales - Services | - 0 | 7,500.00 | 8,032.50 | 8,602.81 | 9,213.61 | 9,867.77 |
| Cash outflow | ||||||
| Filling Fees | 160.00 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Renovation | 25,000.00 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Rent | 5,000.00 | 5,250.00 | 5,512.50 | 5,788.13 | 6,077.53 | 6,381.41 |
| Advertisement | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 |
| Business Insurance | - 0 | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 |
| Utilities | 1,000.00 | 2,000.00 | 2,000.00 | 2,000.00 | 2,000.00 | 2,000.00 |
| Salary - Manager & Asst. Manager | - 0 | 1,750.00 | 1,925.00 | 2,117.50 | 2,329.25 | 2,562.18 |
| Salaries - Employees | - 0 | 3,500.00 | 3,850.00 | 4,235.00 | 4,658.50 | 5,124.35 |
| Health and other benefits | - 0 | 350.00 | 385.00 | 423.50 | 465.85 | 512.44 |
| Credit card company share | - 0 | 12,687.50 | 13,588.31 | 14,553.08 | 15,586.35 | 16,692.98 |
| Inventory Requirements | 20,000.00 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Total Cost | 52,160.00 | 8,250.00 | 8,512.50 | 8,788.13 | 9,077.53 | 9,381.41 |
| Initial Investment from owner | 52,160.00 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Interest payable @ 3.50% | - 0 | 5,398.50 | 5,398.50 | 5,398.50 | 5,398.50 | 5,398.50 |
| Cash flows | (156,480.00) | 466,314.00 | 500,360.69 | 536,819.47 | 575,860.55 | 617,666.04 |
| Net Cash flow | 2,540,540.75 | |||||
4.2 NPV Analysis
| Create an NPV analysis here. | ||||||
| Description | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Cash flows | (156,480.00) | 466,314.00 | 500,360.69 | 536,819.47 | 575,860.55 | 617,666.04 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (156,480.00) | 416,418.40 | 398,787.47 | 382,215.47 | 366,247.31 | 350,216.65 |
| NPV = | 1,757,405.29 | |||||
4.3 Rate of Return Calculations
| Show your rate of return calculations in this worksheet. | ||||||
| Description | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Cash flows | (156,480.00) | 466,314.00 | 500,360.69 | 536,819.47 | 575,860.55 | 617,666.04 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (156,480.00) | 416,418.40 | 398,787.47 | 382,215.47 | 366,247.31 | 350,216.65 |
| IRR = | 262% | |||||
4.4 Payback Period Calculations
| Show your payback period calculations here. | ||||||
| Description | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Cash flows | (156,480.00) | 466,314.00 | 500,360.69 | 536,819.47 | 575,860.55 | 617,666.04 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (156,480.00) | 416,418.40 | 398,787.47 | 382,215.47 | 366,247.31 | 350,216.65 |
| Payback period = | 0.38 |
5.0 Pro Forma Financials
| Put your pro forma income statement, balance sheet, and cash budget here, along with any other supporting calculations or schedules. |
