Based on the excel spreadsheet above answer the following:
B C D E F G H Quarter 1 Quarter 2 25 28 60,000 67,200 0 0 o od 0 16,425 16,425 534 534 0 1 Exhibit 2 2 Sunshine Day Care Center 3 2020-21 Actual, Quarters 1 & 2 4 5 6 7 FTES 8 9 Revenue 20 .1 Expenses: .2 Advertising/Promotion .3 Appliances 4 Bad Debt -5 Head-Quarters Assessments 26 Depreciation -7 Dues & Subscriptions -8 Food & Catering 9 License & Permits 0 Insurance 1 Interest 22 Maintenance & Repairs 23 Office Supplies 24 Personnel 25 Postage & Deliveries 26 Professional Fees 7 Property Tax 28 Rent -9 School Supplies 90 Taxes -1 Telephone 2 Training 3 Travel 34 Utilities 5 Total Expenses 36 7 Net Income (Loss) 8 9 10 11 12 13 1,247 124 1,140 0 0 1,044 20 1,140 0 2,251 275 26,782 0 1,957 240 28,450 0 0 0 2,745 14,664 2,745 14,664 520 570 1,650 1,275 100 1,650 1,165 0 0 0 2,650 72,075 2,850 73,761 (12,075) (6,561) 2. Estimating the Cost FunctionsBudgeted and Actual Use (in Excel) the high-low method to determine the variable cost rate/FTE student, and the total fixed costs based on the quarterly expense budget for 2020-21 (Exhibit 1). Be sure to recognize the time period covered by your estimates (i.e., variable cost/FTE/ for what period? And total fixed costs for what period?) Now repeat the process to estimate the actual cost function using the actual results for the first two quarters of 2020-21 shown in Exhibit 2. Note the limitations of using this method with so few observations. However, results for the first two quarters are the only information available to us regarding the center's actual cost performance. The results of these analyses can be used to estimate the budgeted cost function of the SDC. The cost function will enable you to estimate the costs at any level of activity (in the case of SDC, the number of children enrolled in the center). Show both analyses in a separate spreadsheet. Present both cost functions in the standard form TC = FC + VC/unit (X), where: TC = Total Cost FC = Fixed Cost VC/ unit = Variable Cost per FTE X = Number of FTES Both cost functions should be based on total expenses; you should not do a line-by-line analysis. B C D E F G H Quarter 1 Quarter 2 25 28 60,000 67,200 0 0 o od 0 16,425 16,425 534 534 0 1 Exhibit 2 2 Sunshine Day Care Center 3 2020-21 Actual, Quarters 1 & 2 4 5 6 7 FTES 8 9 Revenue 20 .1 Expenses: .2 Advertising/Promotion .3 Appliances 4 Bad Debt -5 Head-Quarters Assessments 26 Depreciation -7 Dues & Subscriptions -8 Food & Catering 9 License & Permits 0 Insurance 1 Interest 22 Maintenance & Repairs 23 Office Supplies 24 Personnel 25 Postage & Deliveries 26 Professional Fees 7 Property Tax 28 Rent -9 School Supplies 90 Taxes -1 Telephone 2 Training 3 Travel 34 Utilities 5 Total Expenses 36 7 Net Income (Loss) 8 9 10 11 12 13 1,247 124 1,140 0 0 1,044 20 1,140 0 2,251 275 26,782 0 1,957 240 28,450 0 0 0 2,745 14,664 2,745 14,664 520 570 1,650 1,275 100 1,650 1,165 0 0 0 2,650 72,075 2,850 73,761 (12,075) (6,561) 2. Estimating the Cost FunctionsBudgeted and Actual Use (in Excel) the high-low method to determine the variable cost rate/FTE student, and the total fixed costs based on the quarterly expense budget for 2020-21 (Exhibit 1). Be sure to recognize the time period covered by your estimates (i.e., variable cost/FTE/ for what period? And total fixed costs for what period?) Now repeat the process to estimate the actual cost function using the actual results for the first two quarters of 2020-21 shown in Exhibit 2. Note the limitations of using this method with so few observations. However, results for the first two quarters are the only information available to us regarding the center's actual cost performance. The results of these analyses can be used to estimate the budgeted cost function of the SDC. The cost function will enable you to estimate the costs at any level of activity (in the case of SDC, the number of children enrolled in the center). Show both analyses in a separate spreadsheet. Present both cost functions in the standard form TC = FC + VC/unit (X), where: TC = Total Cost FC = Fixed Cost VC/ unit = Variable Cost per FTE X = Number of FTES Both cost functions should be based on total expenses; you should not do a line-by-line analysis
