70.2K

Verified Solution

Link Copied!

Sunshine Daycare Center

I need to find the Estimating Cost Functions - Budget and Actual, Bariance Analysis - Fixed and Variable Costs, Forecasting, Analyzing Profitability By FTE -Contribution Margin Analysis, Evaluating Possible Improvements and 2021-22 Budget Preparation and Analysis. All into an excel sheet.

Exhibit 1 Sunshine Day Care Center Budget, 2020-21 \begin{tabular}{|c|c|c|c|c|c|} \hline & Budget & Budget & Budget & Budget & Budget \\ \hline & Q1 & Q2 & Q3 & Q4 & Year \\ \hline & & - & & & \\ \hline Average FTEs & 31 & 39 & 46 & 45 & 40.25 \\ \hline \multirow[t]{2}{*}{ Revenue } & 77,500 & 98,397 & 116,058 & 113,535 & 405,490 \\ \hline & & & & = & \\ \hline Expenses: & & 8 & & & \\ \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 & 2,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 & 93,986 & 94,786 & 104,261 & 372,101 \\ \hline & & & & & 3 \\ \hline Net Income (Loss) & (1,568) & 4,411 & 21,272 & 9,274 & 33,389 \\ \hline \end{tabular} Cost/FTE \begin{tabular}{|l|l|l|l|l|} 2550.581 & 2409.897 & 2060.565 & 2316.911 & 9244.745 \end{tabular} Exhibit 2 Sunshine Day Care Center 2020-21 Actual, Quarters 1 \& 2 \begin{tabular}{|c|c|c|} \hline & Quarter 1 & Quarter 2 \\ \hline & & \\ \hline FTES & 25 & 28 \\ \hline & & \\ \hline Revenue & 60,000 & 67,200 \\ \hline & & \\ \hline \multicolumn{3}{|l|}{ Expenses: } \\ \hline Advertising/Promotion & 0 & 0 \\ \hline Appliances & 0 & 0 \\ \hline Bad Debt & 0 & 0 \\ \hline Head-Quarters Assessments & 16,425 & 16,425 \\ \hline Depreciation & 534 & 534 \\ \hline Dues \& Subscriptions & 0 & 0 \\ \hline Food \& Catering & 1,044 & 1,247 \\ \hline License \& Permits & 20 & 124 \\ \hline Insurance & 1,140 & 1,140 \\ \hline Interest & 0 & 0 \\ \hline Maintenance \& Repairs & 2,251 & 1,957 \\ \hline Office Supplies & 275 & 240 \\ \hline Personnel & 26,782 & 28,450 \\ \hline Postage \& Deliveries & 0 & 0 \\ \hline Professional Fees & 0 & 0 \\ \hline Property Tax & 2,745 & 2,745 \\ \hline Rent & 14,664 & 14,664 \\ \hline School Supplies & 520 & 570 \\ \hline Taxes & 1,650 & 1,650 \\ \hline Telephone & 1,275 & 1,165 \\ \hline Training & 100 & 0 \\ \hline Travel & 0 & 0 \\ \hline Utilities & 2,650 & 2,850 \\ \hline Total Expenses & 72,075 & 73,761 \\ \hline Net Income (Loss) & (12,075) & (6,561) \\ \hline \end{tabular}

Answer & Explanation Solved by verified expert

See Answer