Excel Assignment
jalqattan
assignment_5_template.xlsxInitial Parameters
| Profitability Budget | |
| Assignment 5 - Part 1 | |
| BSIS 105 | |
| Fall 2014 | |
| New Product Development Profitability Budget | |
| Initial Parameters | |
| Monthly Sales (in units) | 1000 |
| Per Unit Raw Materials Production Cost | $450 |
| Per Unit Labor Production Cost | $200 |
| Sales Price | $850 |
| Advertising Cost per Month | $50,000 |
| Additional Sales Costs per Month | $60,000 |
| Monthly Percentage Growth in Sales | 1.00% |
| Equipment purchase & installation | $650,000 |
| Beginning Cash Balance | $0 |
| Resulting Total Income for the Year | $1,216,200 |
| Ending Cash Balance |
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 | 1000 | 1010 | 1020 | 1030 | 1040 | 1050 | 1061 | 1072 | 1083 | 1094 | 1105 | 1116 | 12681 |
| Revenue | $850,000 | $858,500 | $867,000 | $875,500 | $884,000 | $892,500 | $901,850 | $911,200 | $920,550 | $929,900 | $939,250 | $948,600 | $10,778,850 |
| Cost of Goods Sold | $650,000 | $656,500 | $663,000 | $669,500 | $676,000 | $682,500 | $689,650 | $696,800 | $703,950 | $711,100 | $718,250 | $725,400 | $8,242,650 |
| Gross Profit | $200,000 | $202,000 | $204,000 | $206,000 | $208,000 | $210,000 | $212,200 | $214,400 | $216,600 | $218,800 | $221,000 | $223,200 | $2,536,200 |
| 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 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $720,000 |
| Net Income | $90,000 | $92,000 | $94,000 | $96,000 | $98,000 | $100,000 | $102,200 | $104,400 | $106,600 | $108,800 | $111,000 | $113,200 | $1,216,200 |
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 | |||||||||||||||||
| Beginning Cash | |||||||||||||||||
| Outflows: | |||||||||||||||||
| Equipment purchase & installation | |||||||||||||||||
| Raw material purchases | |||||||||||||||||
| Production labor | |||||||||||||||||
| Advertising | |||||||||||||||||
| Sales Expenses | |||||||||||||||||
| Inflows: | |||||||||||||||||
| Sales Revenue | |||||||||||||||||
| Ending Cash |
