Week 8 Project Activity: Final Budget Proposal Presentation
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 | 150,000.00 | 165,000.00 | 181,500.00 | 199,650.00 | 219,615.00 |
| Salaries - Employees | - 0 | 75,000.00 | 82,500.00 | 90,750.00 | 99,825.00 | 109,807.50 |
| Health and other benefits | - 0 | 30,000.00 | 33,000.00 | 36,300.00 | 39,930.00 | 43,923.00 |
| 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 |
| Depreciation | - 0 | 3,000.00 | 6,000.00 | 9,000.00 | 12,000.00 | 15,000.00 |
| Total Cost | 75,160.00 | 330,687.50 | 370,888.31 | 417,078.08 | 470,677.35 | 533,537.82 |
| 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) | 171,414.00 | 167,245.69 | 159,646.72 | 147,378.21 | 128,782.98 |
| Net Cash flow | 571,987.60 | |||||
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) | 171,414.00 | 167,245.69 | 159,646.72 | 147,378.21 | 128,782.98 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (202,480.00) | 153,072.70 | 133,294.81 | 113,668.47 | 93,732.54 | 73,019.95 |
| NPV = | 364,308.48 | |||||
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) | 171,414.00 | 167,245.69 | 159,646.72 | 147,378.21 | 128,782.98 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (202,480.00) | 153,072.70 | 133,294.81 | 113,668.47 | 93,732.54 | 73,019.95 |
| IRR = | 58% | |||||
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) | 171,414.00 | 167,245.69 | 159,646.72 | 147,378.21 | 128,782.98 |
| Discount Factor @ 12% | 1 | 0.893 | 0.797 | 0.712 | 0.636 | 0.567 |
| PV of Cash flows | (202,480.00) | 153,072.70 | 133,294.81 | 113,668.47 | 93,732.54 | 73,019.95 |
| Payback period = | 1.35 |
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 20xx | Projected for the year ending 20xx | December 20xx | |||||||||||||||||||||||||
| Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |||||||||||||||||||||||
| Revenues: | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Retained Earnings, March 1 | $0 | $0 | $0 | $0 | $0 | Assets: | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | ||||||||||
| Sales - Product | 500,000 | 535,500 | 573,521 | 614,240 | 657,852 | Add: Net Income | 119,990 | 117,072 | 111,753 | 103,165 | 90,148 | Cash | $12,150 | $13,032 | $5,000 | $4,000 | $6,000 | ||||||||||
| Sales - Services | 7,500 | 8,033 | 8,603 | 9,214 | 9,868 | Subtotal | 119,990 | 117,072 | 111,753 | 103,165 | 90,148 | Prepaid Insurance | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | ||||||||||
| Total Revenue | 507,500 | 543,533 | 582,123 | 623,454 | 667,719 | Less: Dividends | - 0 | - 0 | - 0 | - 0 | - 0 | Supplies | 27,000 | 36,450 | 49,208 | 66,430 | 89,681 | ||||||||||
| Drawings | (110,000) | (100,000) | (100,000) | (100,000) | (100,000) | Equipment | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | ||||||||||||||||
| Expenses: | Retained Earnings, March 31 | $9,990 | $17,072 | $11,753 | $3,165 | ($9,852) | Less: Accum. Depr. | (3,000) | (6,000) | (9,000) | (12,000) | (15,000) | |||||||||||||||
| Cost of Sales | 39,688 | 50,038 | 63,761 | 82,016 | 106,374 | Total Assets | $67,150 Author: $26,140 | $74,482 Author: $26,140 | $76,208 Author: $26,140 | $89,430 Author: $26,140 | $111,681 Author: $26,140 |
||||||||||||||||
| Rent | 20,000 | 21,000 | 22,050 | 23,153 | 24,310 | ||||||||||||||||||||||
| Advertisement | 5,000 | 5,000 | 5,000 | 5,000 | 5,000 | Liabilities and Stockholders' Equity | |||||||||||||||||||||
| Business Insurance | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | Liabilities: | |||||||||||||||||||||
| Utilities | 7,000 | 7,350 | 7,718 | 8,103 | 8,509 | Accounts Payable | $5,000 | $5,250 | $12,295 | $34,105 | $69,373 | ||||||||||||||||
| Salaries & Benefits | 255,000 | 280,500 | 308,550 | 339,405 | 373,346 | Income Taxes Payable | - 0 | - 0 | - 0 | - 0 | - 0 | ||||||||||||||||
| Depreciation | 3,000 | 6,000 | 9,000 | 12,000 | 15,000 | Total Liabilities | 5,000 | 5,250 | 12,295 | 34,105 | 69,373 | ||||||||||||||||
| Interest expense | 5,399 | 5,399 | 5,399 | 5,399 | 5,399 | ||||||||||||||||||||||
| 336,086 | 376,287 | 422,477 | 476,076 | 538,936 | Stockholders' Equity: | ||||||||||||||||||||||
| Operating Income | 171,414 | 167,246 | 159,647 | 147,378 | 128,783 | Common Stock | 52,160 | 52,160 | 52,160 | 52,160 | 52,160 | ||||||||||||||||
| Tax @ 30% | 51,424 | 50,174 | 47,894 | 44,213 | 38,635 | Retained Earnings | 9,990 | 17,072 | 11,753 | 3,165 | (9,852) | ||||||||||||||||
| Total Stockholders' Equity | 62,150 | 69,232 | 63,913 | 55,325 | 42,308 | ||||||||||||||||||||||
| Net Income | 119,990 | 117,072 | 111,753 | 103,165 | 90,148 | ||||||||||||||||||||||
| Total Liabilities & Stockholders' Equity | $67,150 Author: $26,140 | $74,482 Author: $26,140 | $76,208 Author: $26,140 |
Author: $26,140 | $89,430 Author: $26,140 |
Author: $26,140 | $111,681 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 | 150,000.00 | 165,000.00 | 181,500.00 | 199,650.00 | 219,615.00 | |||||||||||||||||||||
| Salaries - Employees | - 0 | 75,000.00 | 82,500.00 | 90,750.00 | 99,825.00 | 109,807.50 | |||||||||||||||||||||
| Health and other benefits | - 0 | 30,000.00 | 33,000.00 | 36,300.00 | 39,930.00 | 43,923.00 | |||||||||||||||||||||
| Credit card company share | - 0 | 12,875.00 | 13,789.13 | 14,768.15 | 15,816.69 | 16,939.68 | |||||||||||||||||||||
| Inventory Requirements | 20,000.00 | 27,000.00 | 36,450.00 | 49,207.50 | 66,430.13 | 89,680.67 | |||||||||||||||||||||
| Depreciation | - 0 | 3,000.00 | 6,000.00 | 9,000.00 | 12,000.00 | 15,000.00 | |||||||||||||||||||||
| Interest payable @ 3.50% | - 0 | 5,398.50 | 5,398.50 | 5,398.50 | 5,398.50 | 5,398.50 | |||||||||||||||||||||
| Total Cash outflow | 75,160.00 | 327,875.00 | 365,089.13 | 408,293.15 | 458,907.69 | 518,784.51 | |||||||||||||||||||||
| Net Cash flow | (75,160.00) | 179,625.00 | 178,443.38 | 173,830.15 | 164,546.37 | 148,934.79 | |||||||||||||||||||||
| Cash borrowing | 75,160.00 | - 0 | - 0 | - 0 | - 0 | - 0 | |||||||||||||||||||||
| Repayment | - 0 | 7,516.00 | 7,516.00 | 7,516.00 | 7,516.00 | 7,516.00 | |||||||||||||||||||||
| Cash flows | - 0 | 187,141.00 | 185,959.38 | 181,346.15 | 172,062.37 | 156,450.79 |
