ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

Shangz Brown Excel Sample

?Download as XLS, PDF?
?
S
shangbaby

Jane owns a day care center and wants to analyze how changes to certain variables impact the center's net income. The document includes an income statement for the current assumptions as well as two data tables showing how net income is affected by 1) varying the number of enrolled students and 2) varying both the number of students and daily tuition fee. It also provides a scenario summary comparing the current values to three scenarios that alter teacher costs, supply costs per student, number of students, and daily tuition fee to model different economic conditions and their effect on net income.

1 of 7
Download to read offline
Jane's Day Care Analysis Author Shangz Brown Created 5/29/2009 Modified 5/31/2009 Contents Income Statement Two data tables. One Variable Data Table showing the effect on net income as the number of enrolled students changed. Two Variable Table showing the effect on net income as the number of enrolled students and daily tuition fee changed. Scenario Summary Jane is curious to see the effect of new assumptions. Analysis Recommendation I recommend that Jane take on fewer children. It would reduce her expenses and increase her net income. Shangz Brown 1 Curtis
Day Care Wonders Income Statement Assumptions Number of Children/Day 7 Average Days per Year 250 Teacher Cost / Year 26,000 Food/Child/Day $1.25 Supplies Child/Year $75.00 Annual Revenue Tuition per Day $50 Total Rev $87,500 Annual Variable Expenses Food Expenses $2,188 Supplies per Year 525 Teacher Cost $52,000 Total Variable Expenses $54,713 Annual Fixed Expenses Insurance 5,000 Maintenance 6,500 Administrative & Advertising 1,000 Est. Taxes 12,000 Total Fixed Expenses $24,500 Summary Total Revenue 87,500 Total Expenses 79,213 Net Income $8,288 Shangz Brown 2 Curtis
Teacher:Student Ratio Required Students Teachers 1 1 7 2 13 3 19 4 Effect of Varying Number of Children on Expenses and Net Income 76 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 Net Inc $8,288 $22,175 $8,288 $20,400 $32,513 $44,625 $56,738 $68,850 $54,963 $67,075 $79,188 Goal $75,000 Effect on Net Income of Varying Fees and Number of Children FEES 6 7 8 9 10 11 12 13 14 15 $35 ($325.00) ($17,962.50) ($9,600.00) ($1,237.50) $7,125.00 $15,487.50 $23,850.00 $6,212.50 $14,575.00 $22,937.50 $40 $7,175.00 ($9,212.50) $400.00 $10,012.50 $19,625.00 $29,237.50 $38,850.00 $22,462.50 $32,075.00 $41,687.50 $45 $14,675.00 ($462.50) $10,400.00 $21,262.50 $32,125.00 $42,987.50 $53,850.00 $38,712.50 $49,575.00 $60,437.50 $50 $22,175.00 $8,287.50 $20,400.00 $32,512.50 $44,625.00 $56,737.50 $68,850.00 $54,962.50 $67,075.00 $79,187.50 $55 $29,675.00 $17,037.50 $30,400.00 $43,762.50 $57,125.00 $70,487.50 $83,850.00 $71,212.50 $84,575.00 $97,937.50 $60 $37,175.00 $25,787.50 $40,400.00 $55,012.50 $69,625.00 $84,237.50 $98,850.00 $87,462.50 $102,075.00 $116,687.50 $65 $44,675.00 $34,537.50 $50,400.00 $66,262.50 $82,125.00 $97,987.50 $113,850.00 $103,712.50 $119,575.00 $135,437.50 $70 $52,175.00 $43,287.50 $60,400.00 $77,512.50 $94,625.00 $111,737.50 $128,850.00 $119,962.50 $137,075.00 $154,187.50 $75 $59,675.00 $52,037.50 $70,400.00 $88,762.50 $107,125.00 $125,487.50 $143,850.00 $136,212.50 $154,575.00 $172,937.50 Shangz Brown 3 Curtis
Economy 15 15000 25 35 Page 4
MidRange 8 26000 60 50 Page 5
High 6 38000 100 100 Page 6
Scenario Summary Current Values: Economy MidRange High Changing Cells: Teacher_Cost___Year 26,000 15,000 26,000 38,000 Supplies_Child_Year $75.00 $25.00 $60.00 $100.00 Number_of_Children_Day 7 15 8 6 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 Jane's original assumptions @ time Scenario Summary Report was created. Changing cells for each scenario are highlighted in yellow. Shangz Brown 7 Curtis

More Related Content

Shangz Brown Excel Sample

  • 1. Jane's Day Care Analysis Author Shangz Brown Created 5/29/2009 Modified 5/31/2009 Contents Income Statement Two data tables. One Variable Data Table showing the effect on net income as the number of enrolled students changed. Two Variable Table showing the effect on net income as the number of enrolled students and daily tuition fee changed. Scenario Summary Jane is curious to see the effect of new assumptions. Analysis Recommendation I recommend that Jane take on fewer children. It would reduce her expenses and increase her net income. Shangz Brown 1 Curtis
  • 2. Day Care Wonders Income Statement Assumptions Number of Children/Day 7 Average Days per Year 250 Teacher Cost / Year 26,000 Food/Child/Day $1.25 Supplies Child/Year $75.00 Annual Revenue Tuition per Day $50 Total Rev $87,500 Annual Variable Expenses Food Expenses $2,188 Supplies per Year 525 Teacher Cost $52,000 Total Variable Expenses $54,713 Annual Fixed Expenses Insurance 5,000 Maintenance 6,500 Administrative & Advertising 1,000 Est. Taxes 12,000 Total Fixed Expenses $24,500 Summary Total Revenue 87,500 Total Expenses 79,213 Net Income $8,288 Shangz Brown 2 Curtis
  • 3. Teacher:Student Ratio Required Students Teachers 1 1 7 2 13 3 19 4 Effect of Varying Number of Children on Expenses and Net Income 76 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 Net Inc $8,288 $22,175 $8,288 $20,400 $32,513 $44,625 $56,738 $68,850 $54,963 $67,075 $79,188 Goal $75,000 Effect on Net Income of Varying Fees and Number of Children FEES 6 7 8 9 10 11 12 13 14 15 $35 ($325.00) ($17,962.50) ($9,600.00) ($1,237.50) $7,125.00 $15,487.50 $23,850.00 $6,212.50 $14,575.00 $22,937.50 $40 $7,175.00 ($9,212.50) $400.00 $10,012.50 $19,625.00 $29,237.50 $38,850.00 $22,462.50 $32,075.00 $41,687.50 $45 $14,675.00 ($462.50) $10,400.00 $21,262.50 $32,125.00 $42,987.50 $53,850.00 $38,712.50 $49,575.00 $60,437.50 $50 $22,175.00 $8,287.50 $20,400.00 $32,512.50 $44,625.00 $56,737.50 $68,850.00 $54,962.50 $67,075.00 $79,187.50 $55 $29,675.00 $17,037.50 $30,400.00 $43,762.50 $57,125.00 $70,487.50 $83,850.00 $71,212.50 $84,575.00 $97,937.50 $60 $37,175.00 $25,787.50 $40,400.00 $55,012.50 $69,625.00 $84,237.50 $98,850.00 $87,462.50 $102,075.00 $116,687.50 $65 $44,675.00 $34,537.50 $50,400.00 $66,262.50 $82,125.00 $97,987.50 $113,850.00 $103,712.50 $119,575.00 $135,437.50 $70 $52,175.00 $43,287.50 $60,400.00 $77,512.50 $94,625.00 $111,737.50 $128,850.00 $119,962.50 $137,075.00 $154,187.50 $75 $59,675.00 $52,037.50 $70,400.00 $88,762.50 $107,125.00 $125,487.50 $143,850.00 $136,212.50 $154,575.00 $172,937.50 Shangz Brown 3 Curtis
  • 4. Economy 15 15000 25 35 Page 4
  • 5. MidRange 8 26000 60 50 Page 5
  • 6. High 6 38000 100 100 Page 6
  • 7. Scenario Summary Current Values: Economy MidRange High Changing Cells: Teacher_Cost___Year 26,000 15,000 26,000 38,000 Supplies_Child_Year $75.00 $25.00 $60.00 $100.00 Number_of_Children_Day 7 15 8 6 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 Jane's original assumptions @ time Scenario Summary Report was created. Changing cells for each scenario are highlighted in yellow. Shangz Brown 7 Curtis