help

5. Analyzing Profitability by FTE-Contribution Margin Analysis You now know that SDC has to increase the number of students enrolled in the Center in order to make a profit (assuming they don't change the cost function). So, how much is an additional FTE worth to the bottom line? The answer to that question is: it depends on the contribution margin. The contribution margin per FTE is the revenue per FTE minus the variable costs per FTE. The contribution margin covers fixed costs and (hopefully) profit. Use the format below to prepare (in Excel) a contribution margin income statement based on SDC's budget for 2020-21: -- Average, rather than total, for FTEs Repeat the process to prepare a contribution margin income statement based on your 2020-21 forecast for SDC (from step 4). Calculate (in Excel) the breakeven point (in FTEs) for the year based on SDC's budget. Finally, determine the breakeven point based on your forecast. Show the breakeven point calculations in the same spreadsheet as your contribution margin format income statements. You should have two contribution margin and break-even point analyses at the end of this step: one based on the SDC's 2020-21 budget and the other based on your 2020-21 forecast. 6. Evaluating Possible Improvements The Center board has proposed 4 possible improvement initiatives, each of which can be pursued independently of the others. The proposed initiatives are summarized in Exhibit 3. Prepare an analysis (in Excel) showing the impact of each individual proposed improvement on SDC's forecasted net income for 2020-21. It will be helpful to use the contribution margin income statement approach in performing the analysis. You may wish to use the following format to summarize your results: \begin{tabular}{|c|c|c|c|c|c|} \hline \begin{tabular}{l} Exniort 1 \\ Sunshine Day Care Center \end{tabular} & & & & & \\ \hline \multicolumn{6}{|l|}{ Budget, 2020-21 } \\ \hline & & & & & \\ \hline & Budget & Budget & Budget & Budget & Budget \\ \hline & Q1 & Q2 & Q3 & Q4 & Year \\ \hline Average FTEs & 31 & 39 & 46 & 45 & 40.25 \\ \hline Revenue & 77,500 & 98,397 & 116,058 & 113,535 & 405,490 \\ \hline \multicolumn{6}{|l|}{ Expenses: } \\ \hline Advertising/Promotion & 375 & 375 & 375 & 375 & 1,500 \\ \hline Appliances & 0 & 0 & 0 & 500 & 500 \\ \hline Bad Debt & 79 & 101 & 119 & 126 & 425 \\ \hline Head-Quarters Assessments & 16,425 & 16,425 & 16,425 & 16,425 & 65,700 \\ \hline Depreciation & 534 & 534 & 534 & 534 & 0,136 \\ \hline Dues \& Subscriptions & 0 & 0 & 0 & 0 & 0 \\ \hline Food \& Catering & 1,098 & 1,405 & 1,664 & 1,772 & 5,939 \\ \hline License \& Permits & 0 & 650 & 100 & 0 & 750 \\ \hline Insurance & 1,140 & 1,140 & 1,140 & 1,140 & 4,560 \\ \hline Interest & 0 & 0 & 0 & 0 & 0 \\ \hline Maintenance \& Repairs & 3,750 & 3,750 & 3,750 & 3,750 & 15,000 \\ \hline Office Supplies & 300 & 300 & 300 & 300 & 1,200 \\ \hline Personnel & 31,433 & 45,072 & 46,245 & 55,405 & 178,155 \\ \hline Postage \& Deliveries & 0 & 0 & 0 & 0 & 0 \\ \hline Professional Fees & 0 & 0 & 0 & 0 & 0 \\ \hline Property Tax & 2,745 & 2,745 & 2,745 & 2,745 & 10,980 \\ \hline Rent & 14,664 & 14,664 & 14,664 & 14,664 & 58,656 \\ \hline School Supplies & 600 & 600 & 600 & 600 & 2,400 \\ \hline Taxes & 1,650 & 1,650 & 1,650 & 1,650 & 6,600 \\ \hline Telephone & 1,275 & 1,275 & 1,275 & 1,275 & 5,100 \\ \hline Training & 0 & 300 & 200 & 0 & 500 \\ \hline Travel & 0 & 0 & 0 & 0 & 0 \\ \hline Utilities & 3,000 & 3,000 & 3,000 & 3,000 & 12,000 \\ \hline Total Expenses & 79,068 & 23,986 & 94,786 & 104,261 & 372,101 \\ \hline Net Income (Loss) & (1.568) & 4,411 & 21,272 & 9.274 & 33,389 \\ \hline Cost/FTE & 2,551 & 2,410 & 2,061 & 2,317 & 9,245 \\ \hline \end{tabular} To get a more complete picture of the financial shape of the daycare center, you should prepare a forecast for the remainder of 2020-21, assuming nothing in the cost function is changed by management, by adding estimates for the third and fourth quarters to the actual results for the first two quarters. It looks like SDC enrollment is increasing by approximately six FTEs per quarter. Use 36 FTEs for the third quarter and 34 FTEs for the fourth quarter. The fourth quarter estimate decreases because the budget shows that enrollment is expected to decrease during July and August. You can prepare a pro forma forecast for the year by completing the following table (in Excel; using formulas where appropriate, including to calculate revenue and variable cost). For the third and fourth quarter forecasts, use your quarterly actual cost function developed in step 2 and the forecasted FTE enrollments for quarters 3 and 4 indicated above. Add the original budgeted net income for the year to the bottom of the spreadsheet so that you can compare your forecast for the year to the budget for the year. By how much are you forecasting that SDC will miss its budgeted net income target for the year 2020-21