Course Project - Final Budget Proposal
whtifields
AuthorleeS_WK7BudgetProposalWorkbook.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 |
| Furniture, Fixture and Equipments | 30,000 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Total Cost | 55,160 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Initial Investment from owner | 55,160 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Interest payable @ 3.50% | 57,091 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Total CAPITAL EXPENDITURE | 55,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 |
| Furniture, Fixture and Equipments | 30,000 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Rent | - 0 | 20,000.00 | 21,000.00 | 22,050.00 | 23,152.50 | 24,310.13 |
| Advertisement | - 0 | 5,000.00 | 5,000.00 | 5,000.00 | 5,000.00 | 5,000.00 |
| Business Insurance | - 0 | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 |
| Utilities | - 0 | 7,000.00 | 7,350.00 | 7,717.50 | 8,103.38 | 8,508.54 |
| Salary - Manager & Asst. Manager | - 0 | 120,000.00 | 132,000.00 | 145,200.00 | 159,720.00 | 175,692.00 |
| Salaries - Employees | - 0 | 68,000.00 | 74,800.00 | 82,280.00 | 90,508.00 | 99,558.80 |
| Health and other benefits | - 0 | 24,000.00 | 26,400.00 | 29,040.00 | 31,944.00 | 35,138.40 |
| 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 | 27,000.00 | 36,450.00 | 49,207.50 | 66,430.13 | 89,680.67 |
| Total Cost | 75,160.00 | 284,687.50 | 317,588.31 | 356,048.08 | 401,444.35 | 455,581.52 |
| 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 | (202,480.00) | 217,414.00 | 220,545.69 | 220,676.72 | 216,611.21 | 206,739.28 |
| Net Cash flow | 879,506.90 | |||||
4.2 NPV Analysis
| Create an NPV analysis here. | ||||||
| Description | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Cash flows | (202,480.00) | 387,414.00 | 407,545.69 | 426,376.72 | 442,881.21 | 455,636.28 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (202,480.00) | 345,960.70 | 324,813.91 | 303,580.23 | 281,672.45 | 258,345.77 |
| NPV = | 1,311,893.06 | |||||
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 | (202,480.00) | 387,414.00 | 407,545.69 | 426,376.72 | 442,881.21 | 455,636.28 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (202,480.00) | 345,960.70 | 324,813.91 | 303,580.23 | 281,672.45 | 258,345.77 |
| IRR = | 164% | |||||
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 | (202,480.00) | 387,414.00 | 407,545.69 | 426,376.72 | 442,881.21 | 455,636.28 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (202,480.00) | 345,960.70 | 324,813.91 | 303,580.23 | 281,672.45 | 258,345.77 |
| Payback period = | 0.59 |
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. | ||||||||||
| Wireless World | Wireless World | Wireless World | ||||||||
| Income Statement | Statement of Retained Earnings | Balance Sheet | ||||||||
| Projected for the year ending 2021 | Projected for the year ending 2021 | December 2021 | ||||||||
| Revenues: | Retained Earnings, March 1 | $0 | Assets: | |||||||
| Sales - Product | 500,000 | Add: Net Income | 152,190 | Cash | $52,160 | |||||
| Sales - Services | $7,500 | Subtotal | 152,190 | Prepaid Insurance | 1,000 | |||||
| Total Revenue | 507,500 | Less: Dividends | - 0 | Supplies | 100,000 | |||||
| 507,500 | Drawings | (150,000) | Equipment | 30,000 | ||||||
| Expenses: | Retained Earnings, March 31 | $2,190 | Less: Accum. Depr. | (3,000) | 27,000 | |||||
| Cost of Sales | 39,688 | Total Assets | $180,160 Author: $26,140 |
|||||||
| Rent | 20,000 | |||||||||
| Advertisement | 5,000 | Liabilities and Stockholders' Equity | ||||||||
| Business Insurance | 1,000 | Liabilities: | ||||||||
| Utilities | 7,000 | Accounts Payable | $6,810 | |||||||
| Salaries & Benefits | 212,000 | Income Taxes Payable | - 0 | |||||||
| Interest expense | 5,399 | Total Liabilities | 6,810 | |||||||
| 290,086 | ||||||||||
| Operating Income | 217,414 | Stockholders' Equity: | ||||||||
| Tax @ 30% | 65,224 | Common Stock | 52,160 | |||||||
| Retained Earnings | 2,190 | |||||||||
| Net Income | 152,190 | Total Stockholders' Equity | 54,350 | |||||||
| Total Liabilities & Stockholders' Equity |
Author: $26,140 | $61,160 Author: $26,140 |
||||||||
|
Author: $26,140 |
Author: $26,140 | Cash Budget | ||||||||
| 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 | ||||
| Total Cash inflow | - 0 | 507,500.00 | 543,532.50 | 582,123.31 | 623,454.06 | 667,719.30 | ||||
| Cash outflow | ||||||||||
| Filling Fees | 160.00 | - 0 | - 0 | - 0 | - 0 | - 0 | ||||
| Renovation | 25,000.00 | - 0 | - 0 | - 0 | - 0 | - 0 | ||||
| Furniture, Fixture and Equipments | 30,000 | - 0 | - 0 | - 0 | - 0 | - 0 | ||||
| Rent | - 0 | 20,000.00 | 21,000.00 | 22,050.00 | 23,152.50 | 24,310.13 | ||||
| Advertisement | - 0 | 5,000.00 | 5,000.00 | 5,000.00 | 5,000.00 | 5,000.00 | ||||
| Business Insurance | - 0 | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 | 1,000.00 | ||||
| Utilities | - 0 | 7,000.00 | 7,350.00 | 7,717.50 | 8,103.38 | 8,508.54 | ||||
| Salary - Manager & Asst. Manager | - 0 | 120,000.00 | 132,000.00 | 145,200.00 | 159,720.00 | 175,692.00 | ||||
| Salaries - Employees | - 0 | 68,000.00 | 74,800.00 | 82,280.00 | 90,508.00 | 99,558.80 | ||||
| Health and other benefits | - 0 | 24,000.00 | 26,400.00 | 29,040.00 | 31,944.00 | 35,138.40 | ||||
| Credit card company share | - 0 | 12,500.00 | 13,387.50 | 14,338.01 | 15,356.01 | 16,446.29 | ||||
| Inventory Requirements | 20,000.00 | 27,000.00 | 36,450.00 | 49,207.50 | 66,430.13 | 89,680.67 | ||||
| Interest payable @ 3.50% | - 0 | 7,516.00 | 7,516.00 | 7,516.00 | 7,516.00 | 7,516.00 | ||||
| Total Cash outflow | 75,160.00 | 284,500.00 | 317,387.50 | 355,833.01 | 401,214.01 | 455,334.83 | ||||
| Net Cash flow | (75,160.00) | 223,000.00 | 226,145.00 | 226,290.29 | 222,240.05 | 212,384.48 | ||||
| Cash borrowing | 75,160.00 | |||||||||
| Repayment | - 0 | 7,516.00 | 7,516.00 | 7,516.00 | 7,516.00 | 7,516.00 | ||||
| Cash flows | - 0 | 230,516.00 | 233,661.00 | 233,806.29 | 229,756.05 | 219,900.48 |
