prepare a report for the director of the Daycare Center that discusses the opportunities for improvement and provides general suggestions of how the Daycare Center's financial situation could be improved. Please see attachment for guidance. I will be working on some of it as well. So between the both of us-we should be able to put it together.

Attached please find the case study and exhibits that provide the information that is available for the Daycare Center. The report to the director should provide an introduction that explains what you will talk about followed by the details of the current financial situation. Discuss the opportunities for improvement and provide general suggestions of how the financial situation could be improved. Conclude with your recommendations. Be sure to include the following: A Fixed versus Variable Analysis Cost Function Analysis: Calculate the cost function utilizing the following three methods. Provide a discussion of which of the three methods gives the most accurate cost function and why. Account Analysis Method High-Low Method Regression Analysis Method Forecast: Using the Account Analysis Method prepare a forecast for the third and fourth quarters based on current management strategy - include a row for expected FTEs by quarter. Prepare a Proforma Forecast versus Budget for the current year Determine Profitability by FTE Calculate Breakeven Prepare a Cost/Volume/Profit Chart using the budgeted data and one using the forecasted data. Explain any differences. A Case Study of a Daycare Center Adapted from: Hughes, P.A. (2013, January). Sunshine Daycare Center: Growing a business to profitability. Issues in Accounting Education, 28(2), 323335. doi: 10.2308/iace50407 INTRODUCTION The Daycare Center has been in business for a number of years and until recently has been profitable. A few months ago, regional management dismissed a director who had a confrontational style, potentially causing a decrease in attendance. During the former director's employ, many clients were lost and a complete staff turnover occurred. The new director is enthusiastic and energetic, and while experienced in daycare operations, has no business training. You have been retained as a consultant for the Daycare Center. This will give you an opportunity to use some of the skills you have recently learned in ACCT 551 Accounting for Managers. Your ultimate goal will be a report to the director outlining a strategy for improved profitability for the Daycare Center. To do that, you will need to understand how the business is operating now and decide what areas need improvement. You will need a new forecast of the next two quarters, assuming no change in the current operations. By comparing this to the current yearly plan, you will see how far off the plan the Daycare Center is operating. The Daycare Center is open Monday through Friday and provides care for children in the following categories: Infants (ages 0-18 months), Toddlers (ages 18-36 months), Preschool (ages 3-5 years), Kindergarten (ages 6-7 years), and SchoolAge Children (ages 8-11 years). Children enroll for either fullday or halfday care. Fulltime billing rates vary from infants at $262 per week to $127 for schoolage children. Halfday rates vary from $183 for toddlers to $89 per week for schoolage children. Note that two halfday students equal one fulltimeequivalent student (FTE) for headcount purposes. The center is licensed by the state and meets all regulatory requirements for health and safety. The center is located in an affluent community and has four other competitors in a fivemile radius. All centers offer comparable programs, at comparable rates. The challenge to the Daycare Center is to increase enrollment. The director does not have balance sheet information, but has provided you with the following information: EXHIBIT 1: Daycare Center Budget 2012-2013 by Quarter EXHIBIT 2: Actual Results by Quarter for Q1 and Q2, 2012-2013 EXHIBIT 3: Variance Report of Current Versus Budget Numbers by Expense Other Information Every quarter has 13 weeks. The Daycare Center's accounting period is broken into a fiveweek, fourweek, and fourweek pattern per quarter. The difference in the number of weeks can make comparisons by month difficult. You will find it easier to work with this data if they are in a quarterly format. The fiscal year starts in September and closes in August. UNDERSTANDING THE CURRENT ''NUMBERS'' One way of starting to understand what the financial condition of the Daycare Center is, is to compare what is happening to what was planned in the budget. A common way to do that is to compare actual expenses to budgeted expenses. A variance report of current versus budget numbers by expense for the first two quarters has been prepared for you and is attached as Exhibit 4. What are your observations about the differences? It is obvious that the daycare center is not achieving budgeted sales and net income, but let's looks deeper. A simple guess might be that they need to cut expenses. But that is not the right answer. What expenses are the ones causing the large losses? Are they fixed or variable expenses? If they are fixed expenses, they cannot be cut. FIXED VERSUS VARIABLE ANALYSIS It is important to understand which costs cannot be cut because they are fixed costs. Make an analysis of the expenses on an Excel spreadsheet. In the first column, list the expense name. Fill in the second column with the totalyear budget numbers. You can get those from Exhibit 1. In the third column, put a V in the column next to the identified variable expenses. Put an F in the third column for the identified fixed expenses. Sort the spreadsheet by the third column. Put subtotals in for fixed and variable categories. Why are we using these numbers? They reflect a full year, while any quarter would reflect seasonality. It would be better to use a full year of actual expenses, but we do not have those. If an expense does not vary with the cost driverthe children, it cannot be considered variable. For example, the weather may affect the cost of utilities, but the number of children is unlikely to affect the utilities. We would have to put the utilities in the fixed category. For the budget, the utility expense can have the same number every month, but it is unlikely the actual numbers will come in that way. Nonetheless, they should be considered fixed for our analysis. From these numbers you can create a cost function for the Daycare Center. Why do you want a cost function? This is a great timesaving tool to estimate the costs at any level of capacity, in this case at any number of children in the daycare center. DETERMINING THE COST FUNCTION There are three basic ways to compute a cost function. One may be a better fit than another, depending on what you are evaluating. Account Analysis Method Total cost from Exhibit 1 or your fixed/variable analysis is $372,101. $372,101 = total fixed costs + total variable costs. We need to go one step further to break total variable costs into variable cost per unit. We do this by dividing total variable cost by total units. In this case, these will be average FTEs (40) to obtain the variable cost per FTE per year. Therefore, total cost = fixed cost + units (variable cost). This can be expressed as TC = FC + VC (units). What is the yearly cost function for the Daycare Center for the current year using the account analysis method? Use the total year column from Exhibit 1 for the numbers. By varying the units you get the expense total for any number of FTEs on a yearly basis. Calculate what the total cost would be if there were an average of 35 children in the center for the year. In this case there were not many expenses, so the account analysis method worked very well. If this were a business with hundreds of expenses, this would be very laborious. HighLow Method We might want to use the highlow method. This method of developing a cost function is useful when you do not know the fixed and variable components for a time period, but we do know the total cost for at least two time periods. It is best when the activities of each period are very similar, such as electricity rates. We prefer that the time periods would not be sequential. Use the Q1 budget and the Q3 budget to calculate the cost function for a quarter's expenses. You will not have to sort the data into fixed and variable components because the highlow method will do that for you. The highlow method has you compare one total cost to another. If the fixed costs in the totals are the same, then the difference between the two numbers has to be incremental variable cost. If you divide the incremental variable cost by the difference in units (FTEs in this case), you get the variable cost per unit. Multiply the variable cost per unit by the number of FTEs. This will give you the variable total cost by quarter. Develop the quarterly cost function for the Daycare Center, TC = FC + VC (units). Refer to the textbook if you need additional instructions on the highlow method. Fill out the following format for the highlow analysis: Quarterly Budget Cost Data FTEs Total Cost Quarter 1 Budget 31 $79,068 Quarter 3 Budget 46 $94,786 Difference 15 $15,718 Variable Cost Fixed Cost Variable Cost/FTE What is the variable cost per average FTE for a quarter? What would be the variable cost per average FTE for a year? Is the variable cost calculated by the highlow method different from the variable cost calculated by the account analysis method? Why? Regression Analysis Method Excel was utilized to calculate the intercept, slope, and R2. The quarterly budgeted expenses were plotted against FTEs in an Excel spreadsheet. The results were an intercept of 39,058, a slope of 1,340, and an R2 of 0.7894. This is a quarterly function so the intercept and slope need to be multiplied by four and use 40 FTEs to obtain the yearly function. You can try to run the regression analysis in Excel to see if you come up with the same results. Plot the center's quarterly budgeted expenses versus FTEs in an Excel spreadsheet, and using regression calculate the center's cost function. Insert a trend line. Hint: the y intercept is the fixed cost, and the slope of the line is the variable cost per unit. What is the yearly cost function using this method? CHOOSING THE BEST COST FUNCTION Which of the three methodologies gives the most accurate cost function? Do the differences matter much? To do this you might want to look at the fixed cost computed for each of the three methods. Are they very different? You know the account method would be the most accurate for a fixed/variable analysis. Compare the total expenses in each method. Now determine which method is the best to use going forward. You will see that the cost function for the Daycare Center can be changed to work for any time period, such as for a year, a quarter, or a week. This is a strength of the cost function tool. What is the cost function for Daycare Center using the expense analysis method for half a year? You would use half the fixed cost for the year and half the variable cost per FTE per year to do this. Why would we want to know that? We might want to compare the cost function we planned with the actual data. We only have six months of actual data, so we would need a budget cost function for the same time period. If they were very similar, you could trust the budget account analysis method cost function to forecast the third and fourth quarters. The only real difference should be the number of FTEs. You could also use the actual data for the first six months to generate a cost function to use. What is the cost function for the Daycare Center using the expense analysis method on the actual first half of the year? To do that you would have to sort the analysis you did for Q1 and Q2 actual data. Fill in the box below. How does this compare to the budgeted sixmonth cost function? Is the fixed cost similar? Is the variable cost per FTE similar? SixMonth Cost Function Fixed Variable FTEs Budget Actual Difference 40 26 14 This demonstrates that the budget cost function is reasonable to use as a proxy for the actual cost function for the year. The only real difference is the number of FTEs. What is the cost function for the Daycare Center using the account analysis method on the budget per week? You might want to compare the actual weekly cost function to the budgeted weekly cost function. You would take the yearly fixed cost and divide by 52 weeks. You also might want to compare the actual weekly cost function to the budgeted weekly cost function when we look at incremental changes in volume. Is the fixed cost similar? Is the variable cost per FTE similar? Weekly Cost Function Fixed Variable FTEs Budget Actua l Difference 40 26 14 You should be able to see that the cost function for the budget is not very different than the cost function for actual data. The only major difference is that there are fewer actual students than planned. That significantly affects the bottom line because there is a high level of fixed costs as a burden to the Daycare Center. These cannot be reduced. The director has reduced variable costs as much as possible. Was that wise? FORECASTING To get a full picture of the financial shape of the daycare center, you should forecast out how the rest of the year will go, assuming nothing is changed by management. Forecast expenses and revenue out to the end of the 2012-2013 fiscal year. To do this, copy the Q1 and Q2 actual data columns from your previous exercise and paste them into a new Excel page. Sort them by fixed and variable components. You will prepare a pro forma forecast for the year by including the first two actual quarters, then adding estimates for the third and fourth quarters on your Excel spreadsheet. Include a row for the expected number of students expressed as FTEs by quarter. It looks like the Daycare Center is increasing 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. Your results would look something like Exhibit 1 if you were going to do this the long way, except the columns would be Actual Q1, Actual Q2, Forecasted Q3, Forecasted Q4, and Forecasted Year. Using your fixed and variable splits from the earlier exercise, complete the following table format. However, for the third and fourth quarter forecasts, use a quarterly cost function developed from the account analysis method. Fill out the following format: Actual Quarter 1 FTEs Revenue Variable Cost Fixed Cost Total Cost Net Income Budget Income Difference 25 $64,042 $27,826 $39,906 $67,732 $(3,690) $(676) $(3,014) Actual Quarter 2 28 $71,358 $26,911 $40,182 $67,093 $4,265 $6,347 $(2,082) Forecast Quarter 3 Forecast Quarter 4 Forecast Total Year 36 34 31 $317,663 $24,097 $22,309 $52,077 Compare your forecast for the year to the budget for the year. How much are you forecasting that the Daycare Center will miss its budgeted revenue and net profit targets for the year? Compare your forecast for the year to the budget for the year on an Excel spreadsheet. How much are you forecasting that the Daycare Center will miss its budgeted revenue and net profit targets for the year 2012-2013? PROFITABILITY BY FTE You now know that the Daycare Center has to increase the number of clients coming to the center to make a profit. So, how much is an extra FTE worth to the bottom line? The answer to that question is, it depends on whether fixed costs have been recovered by revenue. 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. What is the budgeted average revenue and expense per average student FTE per year? What is the budgeted contribution margin per FTE per year? How many FTE contribution margins are needed to pay for the full fixed costs in the budget? That answer is the breakeven point in number of average FTEs for the year per the budget. Breakeven Analysis What is the budgeted breakeven number of FTEs? What is the actual breakeven number of FTEs? Why is the actual breakeven lower than the budgeted breakeven? Why is the actual net income (loss) per FTE different than the budgeted net income per FTE? Developing the Cost/Volume/Profit Chart Many times a picture communicates more than words or calculations. A cost/volume/profit chart shows you all the possible profit and loss positions for your business at a particular moment in time. It visually shows you how much profit or loss you would earn for a given number of FTEs. Prepare a cost/volume/profit chart for the Daycare Center using the budgeted data. The possible range of FTEs should be on your x axis; also include dollars showing fixed variable cost, as well as sales by FTEs. Show the breakeven point in terms of FTEs. What do you estimate the relevant range to be? Prepare a cost/volume/profit chart for the Daycare Center using the forecasted year's actual sixmonth data plus your forecasted Q3 and Q4 data to form the year. Show the breakeven point in terms of FTEs. Explain the reasons for the differences between forecast and budget. PAPER Prepare a report for the director that discusses the opportunities for improvement and provide general suggestions of how the financial situation could be improved. The paper should provide an introduction that explains what you will talk about followed by the details of the current financial situation. Discuss the opportunities for improvement and provide general suggestions of how the financial situation could be improved. Conclude with your recommendations. Be sure to include the following: A Fixed versus Variable Analysis Cost Function Analysis: Calculate the cost function utilizing the following three methods. Provide a discussion of which of the three methods gives the most accurate cost function and why. Account Analysis Method HighLow Method Regression Analysis Method Forecast: Using the Account Analysis Method prepare a forecast for the third and fourth quarters based on current management strategy - include a row for expected FTEs by quarter. Prepare a Proforma Forecast versus Budget for the current year Determine Profitability by FTE Calculate Breakeven Prepare a Cost/Volume/Profit Chart using the budgeted data and one using the forecasted data. Explain any differences. Daycare Center Exhibit 1 Daycare Center Budget Fiscal Year 2012-2013 Budget Q1 Average FTEs Revenue Expenses Advertising/Promotion Appliances Auto Expense Bad Debt HQ Assessments Depreciation Dues & Subscriptions Food & Catering License & Permits Insurance Interest Maintenance & Repairs Office Supplies Personnel Postage & Delivery Professional Fees Property Tax Rent School Supplies Taxes Telephone Training Travel Utilities Total Expense Net Income (Loss) Budget Q2 31 Budget Q3 Budget Q4 Budget Year 39 46 45 40 $78,392 $100,333 $118,883 $126,570 $424,178 375 375 375 375 500 79 16,425 534 101 16,425 534 119 16,425 534 126 16,425 534 1,098 1,664 100 1,140 1,772 1,140 1,405 650 1,140 3,750 300 31,433 3,750 300 45,072 3,750 300 46,245 3,750 300 55,405 2,745 14,664 600 1,650 1,275 2,745 14,664 600 1,650 1,275 300 2,745 14,664 600 1,650 1,275 200 2,745 14,664 600 1,650 1,275 3,000 79,068 $(676) 3,000 93,986 $6,347 3,000 94,786 $24,097 3,000 104,261 $22,309 1,500 500 425 65,700 2,136 5,939 750 4,560 15,000 1,200 178,155 10,980 58,656 2,400 6,600 5,100 500 12,000 372,101 $52,077 1,140 Daycare Center Exhibit 2 Quarter 1 and Quarter 2 Actual Data Quarter 1 FTEs Revenues Expenses Advertising/Promotion Appliances Auto Expense Bad Debt HQ Assessments Depreciation Dues & Subscriptions Food & Catering License & Permits Insurance Interest Maintenance & Repairs Office Supplies Personnel Postage & Delivery Professional Fees Property Tax Rent School Supplies Taxes Telephone Training Travel Utilities Total Expense Net Income (Loss) Quarter 2 25 28 $64,042 $71,358 14,936 501 14,398 501 1,044 20 1,227 471 124 1,230 2,251 441 26,782 1,957 446 26,440 1,790 14,664 198 640 1,373 100 1,983 14,664 213 702 789 1,765 67,732 $(3,690) 3,175 67,093 $4,265 Daycare Center Variance Report of Current Versus Budget Numbers by Expense Q1-Q2 2012/2013 ACTUAL Q1 Average FTEs Revenue Expenses Advertising/Promotion Appliances Auto Expense Bad Debt HQ Assessments Depreciation Dues & Subscriptions Food & Catering License & Permits Insurance Interest Maintenance & Repairs Office Supplies Personnel Postage & Delivery Professional Fees Property Tax Rent School Supplies Taxes Telephone Training Travel Utilities Total Expense Net Income (Loss) BUDGET Q1 25 $64,042 DIFFERENCE Q1 31 -6 $(14,350) 375 14,936 501 $78,392 (375) (79) (1,489) (33) (54) 20 87 (1,499) 141 (4,651) (955) (402) (1,010) 98 100 (1,235) (11,336) $(3,014) 79 16,425 534 1,044 20 1,227 1,098 2,251 441 26,782 3,750 300 31,433 1,790 14,664 198 640 1,373 100 2,745 14,664 600 1,650 1,275 1,765 67,732 $(3,690) 3,000 79,068 $(676) 1,140 ACTUAL Q2 BUDGET Q2 28 $71,358 DIFFERENCE Q2 39 -11 $100,333 $(28,975) 375 (375) (101) 4,214 (33) (934) (526) 90 (1,793) 146 (18,632) (762) (387) (948) (486) (300) 175 (20,652) $(8,323) 20,639 501 101 16,425 534 471 124 1,230 1,405 650 1,140 1,957 446 26,440 3,750 300 45,072 1,983 14,664 213 702 789 2,745 14,664 600 1,650 1,275 300 3,175 73,334 $(1,976) 3,000 93,986 $6,347