Using the information above answer the question below. Please show excel formulas if possible

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) udsel, B C D E F G T 375 375 375 375 0 0 79 16,425 534 500 126 16,425 534 0 101 16,425 534 0 1,405 650 1,140 0 -2 Expenses: _3 Advertising/Promotion -4 Appliances 5 Bad Debt .6 Head-Quarters Assessments -7 Depreciation -8 Dues & Subscriptions _9 Food & Catering 0 License & Permits -1 Insurance 22 Interest 23 Maintenance & Repairs 24 Office Supplies 25 Personnel 26 Postage & Deliveries 27 Professional Fees 8 Property Tax 9 Rent Co School Supplies 1 Taxes 2 Telephone 3 Training 4 Travel 35 Utilities 56 Total Expenses 7 38 Net Income (Loss) 0 1,098 0 1,140 0 3,750 300 31,433 0 1,500 500 425 65,700 2,136 0 5,939 750 4,560 0 15,000 1,200 178,155 0 0 119 16,425 534 0 1,664 100 1,140 0 3,750 300 46,245 0 0 2,745 14,664 600 1,650 1,275 200 0 3,000 94,786 1,772 0 1,140 0 3,750 300 55,405 0 3,750 300 45,072 0 0 0 0 0 2,745 14,664 600 1,650 1,275 0 2,745 14,664 600 1,650 1,275 300 0 3,000 93,986 2,745 14,664 600 1,650 1,275 0 10,980 58,656 2,400 6,600 5,100 500 0 12,000 372, 101 0 0 3,000 79,068 3,000 104,261 (1,568) 4,411 21,272 9,274 33,389 39 2550.581 2409.897 2060.565 2316.911 9244.745 10 11 Cost/FTE 12 13 14 B D E Cost Benefit $550/week 1.5 FTE increase $2,000/month 1 FTE increase 1 FTE increase $500 initial fee plus $100/ week to update current events 1 Exhibit 3 2 Improvement Recommendations 3 4 5 Improvement Area Details 6 7 1. Newspaper Ads 100,000 adults 3 9 2. Television Ads Local station for Lifetime & Nickelodeon; 30 LO second commericals 1 2 3. New website Hire professional to create and update website .3 for center -4 -5 -6 4. Increase tuition Increase tuition by 10% for all age groups -7 -8 -9 0 These improvement options were developed by the SDC Board. Each can be pursued independently 1 -2 3 24 5 26 7 28 29 30 1 2 3 Enrollment decrease of 1 FTE Increased tuition revenue/FTE 1. Variance analysisIncome Statement Line Items One way of starting to understand the financial condition of SDC is to compare what is happening to what was planned as reflected in the budget; i.e., to conduct a basic variance analysis. Prepare a variance analysis of current actual versus budgeted FTEs, revenues, and expenses (showing individual expense items) for the first two quarters of 2020-21 in an Excel spreadsheet. Set up your spreadsheet as follows: Actual Total or Average Q1 & Q2* Budget Total or Average Q1 & Q2* Q1 & Q2 Actual Over/(Under) Budget Actual Q1 Actual Q2 Budget Q1 Budget Q2 FTE Revenues Expenses Advertising/ Promotion Bad Debt ... T Utilities Total Expenses Net Income FTEs should be shown at average; all other figures should be quarterly totals. -- you should include all line items from the budgeted and actual income statements in your analysis. Some line items are omitted here for brevity. What are your observations about the differences? It is obvious that the daycare center is not achieving budgeted revenue and net income, but you will need to look deeper. A simple statement might be that they need to cut overall expenses, but it's not that easy. Management 2 and the board are convinced that they can't cut their costs any further and still provide a competitive level of service. Rather, they want to pursue opportunities to grow their revenues. 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) udsel, B C D E F G T 375 375 375 375 0 0 79 16,425 534 500 126 16,425 534 0 101 16,425 534 0 1,405 650 1,140 0 -2 Expenses: _3 Advertising/Promotion -4 Appliances 5 Bad Debt .6 Head-Quarters Assessments -7 Depreciation -8 Dues & Subscriptions _9 Food & Catering 0 License & Permits -1 Insurance 22 Interest 23 Maintenance & Repairs 24 Office Supplies 25 Personnel 26 Postage & Deliveries 27 Professional Fees 8 Property Tax 9 Rent Co School Supplies 1 Taxes 2 Telephone 3 Training 4 Travel 35 Utilities 56 Total Expenses 7 38 Net Income (Loss) 0 1,098 0 1,140 0 3,750 300 31,433 0 1,500 500 425 65,700 2,136 0 5,939 750 4,560 0 15,000 1,200 178,155 0 0 119 16,425 534 0 1,664 100 1,140 0 3,750 300 46,245 0 0 2,745 14,664 600 1,650 1,275 200 0 3,000 94,786 1,772 0 1,140 0 3,750 300 55,405 0 3,750 300 45,072 0 0 0 0 0 2,745 14,664 600 1,650 1,275 0 2,745 14,664 600 1,650 1,275 300 0 3,000 93,986 2,745 14,664 600 1,650 1,275 0 10,980 58,656 2,400 6,600 5,100 500 0 12,000 372, 101 0 0 3,000 79,068 3,000 104,261 (1,568) 4,411 21,272 9,274 33,389 39 2550.581 2409.897 2060.565 2316.911 9244.745 10 11 Cost/FTE 12 13 14 B D E Cost Benefit $550/week 1.5 FTE increase $2,000/month 1 FTE increase 1 FTE increase $500 initial fee plus $100/ week to update current events 1 Exhibit 3 2 Improvement Recommendations 3 4 5 Improvement Area Details 6 7 1. Newspaper Ads 100,000 adults 3 9 2. Television Ads Local station for Lifetime & Nickelodeon; 30 LO second commericals 1 2 3. New website Hire professional to create and update website .3 for center -4 -5 -6 4. Increase tuition Increase tuition by 10% for all age groups -7 -8 -9 0 These improvement options were developed by the SDC Board. Each can be pursued independently 1 -2 3 24 5 26 7 28 29 30 1 2 3 Enrollment decrease of 1 FTE Increased tuition revenue/FTE 1. Variance analysisIncome Statement Line Items One way of starting to understand the financial condition of SDC is to compare what is happening to what was planned as reflected in the budget; i.e., to conduct a basic variance analysis. Prepare a variance analysis of current actual versus budgeted FTEs, revenues, and expenses (showing individual expense items) for the first two quarters of 2020-21 in an Excel spreadsheet. Set up your spreadsheet as follows: Actual Total or Average Q1 & Q2* Budget Total or Average Q1 & Q2* Q1 & Q2 Actual Over/(Under) Budget Actual Q1 Actual Q2 Budget Q1 Budget Q2 FTE Revenues Expenses Advertising/ Promotion Bad Debt ... T Utilities Total Expenses Net Income FTEs should be shown at average; all other figures should be quarterly totals. -- you should include all line items from the budgeted and actual income statements in your analysis. Some line items are omitted here for brevity. What are your observations about the differences? It is obvious that the daycare center is not achieving budgeted revenue and net income, but you will need to look deeper. A simple statement might be that they need to cut overall expenses, but it's not that easy. Management 2 and the board are convinced that they can't cut their costs any further and still provide a competitive level of service. Rather, they want to pursue opportunities to grow their revenues