Excel Assignment 2
jalqattan
assignment_5_part_2_template_2014_10_18.xlsxInitial Parameters
| Profitability and Cash Budgets | Your Name | ||||
| Assignment 5 - Part 2 - Template | |||||
| BSIS 105 | |||||
| Fall 2014 | |||||
| New Product Development Profitability and Cash Budgets | |||||
| Initial Parameters | Economic Conditions | Initial Monthly Sales | Monthly Percentage Growth in Sales | ||
| Monthly Sales (in units) | Depression | 500 | -2.00% | ||
| Per Unit Raw Materials Production Cost | $450 | Deep Resession | 800 | -1.00% | |
| Per Unit Labor Production Cost | $200 | Resession | 1000 | 0.00% | |
| Sales Price | $850 | Shallow Resession | 1050 | 1.00% | |
| Advertising Cost per Month | $50,000 | Slow Growth | 1100 | 1.50% | |
| Additional Sales Costs per Month | $20,000 | Growth | 1200 | 2.50% | |
| Monthly Percentage Growth in Sales | Fast Growth | 1500 | 4.00% | ||
| Equipment purchase & installation | $650,000 | ||||
| Beginning Cash Balance | $0 | ||||
| Economic Condition | |||||
| Resulting Total Income for the Year | -$840,000 | ||||
| Ending Cash Balance | -$1,540,000 |
Profitability by Month
| Assignment 5 | |||||||||||||
| BSIS 105 | |||||||||||||
| Fall 2014 | |||||||||||||
| New Product Development Profitability by Month | |||||||||||||
| Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | Yearly Total | |
| Monthly Unit Sales | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Revenue | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| Cost of Goods Sold | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| Gross Profit | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| Less: | |||||||||||||
| Avertising Cost | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $600,000 |
| Additional Sales Costs per Month | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $240,000 |
| Net Income | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$70,000 | -$840,000 |
Cash Flow by Month
| Assignment 5 | |||||||||||||||||
| BSIS 105 | |||||||||||||||||
| Fall 2014 | |||||||||||||||||
| New Product Development Cash Flow by Month | |||||||||||||||||
| Month -1 | Month 0 | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | Month 13 | Month 14 | Yearly Total (Months 1 thru 12) | |
| Monthly Unit Sales | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
| Beginning Cash | $0 | -$650,000 | -$700,000 | -$770,000 | -$840,000 | -$910,000 | -$980,000 | -$1,050,000 | -$1,120,000 | -$1,190,000 | -$1,260,000 | -$1,330,000 | -$1,400,000 | -$1,470,000 | |||
| Outflows: | |||||||||||||||||
| Equipment purchase & installation | $650,000 | $650,000 | |||||||||||||||
| Raw material purchases | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Production labor | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Purchase of Out-Sourced Units | |||||||||||||||||
| Advertising | $0 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $50,000 | $650,000 | ||
| Sales Expenses | $0 | $0 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $20,000 | $240,000 | ||
| Inflows: | |||||||||||||||||
| Sales Revenue | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Ending Cash | -$650,000 | -$700,000 | -$770,000 | -$840,000 | -$910,000 | -$980,000 | -$1,050,000 | -$1,120,000 | -$1,190,000 | -$1,260,000 | -$1,330,000 | -$1,400,000 | -$1,470,000 | -$1,540,000 | -$1,540,000 |
