analyze
zeel906
FIN5823DAYCAREWONDERSEXAMPLE.xlsxDocumentation
| Day Care Wonders | |||||||||||||||||||
| Income Statement | Solution | ||||||||||||||||||
| Author | Student Name | ||||||||||||||||||
| Date Created | |||||||||||||||||||
| Purpose | This spreadsheet provides What-If analysis based on the Income Statement of Jane Morales. This What-If analysis will help Ms. Morales determine whether it is viable for her to start this business. | ||||||||||||||||||
| Contents | Income Statement with One and Two Variable Data Tables | ||||||||||||||||||
| Scenario Summary | |||||||||||||||||||
| Recommendation | This is a recommendation as to Ms. Morales ability to make ??? profit from her Day Care Center. |
Income Statement
| Day Care Wonders | |||||||||||||||
| Income Statement | Teacher:Student Ratio Required | ||||||||||||||
| Students | Teachers | Solution | |||||||||||||
| 1 | 1 | ||||||||||||||
| Assumptions | 7 | 2 | |||||||||||||
| Number of Children/Day | 7 | 13 | 3 | ||||||||||||
| Average Days per Year | 250 | 19 | 4 | ||||||||||||
| Teacher Cost per Year | 26,000 | ||||||||||||||
| Food per Child per Day | 1.3 | Effect of Varying Number of Children on Expenses and Net Income | |||||||||||||
| Supplies per Child per Year | 75 | Initial Values | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | |||
| Expenses | $79,213 | $52,825 | $79,213 | $79,600 | $79,988 | $80,375 | $80,763 | $81,150 | $107,538 | $107,925 | $108,313 | ||||
| Annual Revenue | Net Income | $8,288 | $22,175 | $8,288 | $20,400 | $32,513 | $44,625 | $56,738 | $68,850 | $54,963 | $67,075 | $79,188 | |||
| Tuition per Day | 50 | ||||||||||||||
| Annual Revenue | $ 87,500 | ||||||||||||||
| Annual Variable Expenses | Effect on Net Income of Varying Fee and Number of Children | ||||||||||||||
| Food Expenses | 2,188 | 8287.5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | |||
| Supplies per Year | 525 | 35 | -$325 | -$17,963 | -$9,600 | -$1,238 | $7,125 | $15,488 | $23,850 | $6,213 | $14,575 | $22,938 | |||
| Teacher Cost | 52,000 | 40 | $7,175 | -$9,213 | $400 | $10,013 | $19,625 | $29,238 | $38,850 | $22,463 | $32,075 | $41,688 | |||
| Total Variable Expenses | $ 54,713 | 45 | $14,675 | -$463 | $10,400 | $21,263 | $32,125 | $42,988 | $53,850 | $38,713 | $49,575 | $60,438 | |||
| 50 | $22,175 | $8,288 | $20,400 | $32,513 | $44,625 | $56,738 | $68,850 | $54,963 | $67,075 | $79,188 | |||||
| Annual Fixed Expenses | 55 | $29,675 | $17,038 | $30,400 | $43,763 | $57,125 | $70,488 | $83,850 | $71,213 | $84,575 | $97,938 | ||||
| Insurance | 5,000 | 60 | $37,175 | $25,788 | $40,400 | $55,013 | $69,625 | $84,238 | $98,850 | $87,463 | $102,075 | $116,688 | |||
| Maintenance | 6,500 | 65 | $44,675 | $34,538 | $50,400 | $66,263 | $82,125 | $97,988 | $113,850 | $103,713 | $119,575 | $135,438 | |||
| Administrative & Advertising | 1,000 | 70 | $52,175 | $43,288 | $60,400 | $77,513 | $94,625 | $111,738 | $128,850 | $119,963 | $137,075 | $154,188 | |||
| Est. Taxes | 12,000 | 75 | $59,675 | $52,038 | $70,400 | $88,763 | $107,125 | $125,488 | $143,850 | $136,213 | $154,575 | $172,938 | |||
| Total Fixed Expenses | $ 24,500 | ||||||||||||||
| Summary | |||||||||||||||
| Total Revenue | 87,500 | ||||||||||||||
| Total Expenses | 79,213 | ||||||||||||||
| Net Income | $ 8,288 |
Scenario Summary
| Scenario Summary | ||||||
| Current Values: | Economy | Midrange | High | |||
| Created by DVUO on 9/4/2007 Modified by Nancy LaChance on 9/4/2007 | Created by DVUO on 9/4/2007 Modified by Nancy LaChance on 9/4/2007 | Created by Nancy LaChance on 9/4/2007 | ||||
| Changing Cells: | ||||||
| Number_of_Children_Day | 7 | 15 | 8 | 6 | ||
| Teacher_Cost_per_Year | 26,000 | 15,000 | 26,000 | 38,000 | ||
| Supplies_per_Child_per_Year | 75 | 25 | 60 | 100 | ||
| Tuition_per_Day | 50 | 35 | 50 | 100 | ||
| Result Cells: | ||||||
| Total_Revenue | 87,500 | 131,250 | 100,000 | 150,000 | ||
| Total_Expenses | 79,213 | 74,563 | 79,480 | 64,975 | ||
| Net_Income | $ 8,288 | $ 56,688 | $ 20,520 | $ 85,025 | ||
| Notes: Current Values column represents values of changing cells at | ||||||
| time Scenario Summary Report was created. Changing cells for each | ||||||
| scenario are highlighted in gray. |
