50.1K

Verified Solution

Link Copied!

DAY CARE COMES TO NORTHVILLE (show formulas in excel spreadsheet) DESCRIPTION

Part 1

Daycare Comes to Northville

Northville, a medium-sized city in the midwestern part of the United States, has experienced a significant change in the composition of its municipal work force of 1,800 employees. Approximately 35 percent of the city employees are now female. In a recent survey of city employees, over 40 percent have said that affordable day care for children was important to them. Meanwhile Director of the Office of Personnel Mary Lux has become increasingly convinced that the lack of affordable day care is one of the main reasons for absenteeism and lateness among city employees. Mayor Petula Spark, some of the members of the city council, and the leader of the major city employees union, Denardo Legato, all agree that something should be done. The question they are trying to answer is, what should it be?

Mayor Spark is in favor of doing something, in principle, but she is not in favor of incurring a major new expense, given the many legitimate claims on the citys already strained budget. She has told Legato, who is negotiating the day care program on behalf of the city employees. Well give you space and utilities for a year at no cost. It is up to you to come up with a suitable day care center that conforms to state and federal law. Several regulatory mandates and non-discrimination laws fall into this category. The only requirements specific to day care centers are that (a) they be licensed and inspected once a year, (b) all new day care workers take part in a three day state-certified training program and (c) the child/day care giver ratio be no greater than 8 to 1. The annual inspection fee is $300. The total cost of the three-day training program is estimated to be $250 per employee.

Mary Lux is responsible for planning the details of the day care program for the children of city employees. With Mr. Legatos approval, Ms. Lux has negotiated an arrangement with a local non-profit agency that is already providing day care services in the Northville metropolitan area.

Tiny Tots has three locations: the contract with the City Northville would be a fourth center. The Director of Tiny Tots, Klara Nemet, is enthusiastic about the prospects of a new center specifically for city employees. While discussing the proposed arrangements with Ms. Lux, Ms. Nemet requested $3,000 per month to compensate her for the administration costs associated with the Northville location.

The additional details of the contract are as follows:

-The day care center will be open 20 days every month. Parents pay a monthly fee of $350 per month.

-Based on projected demand, it is expected that the day care center will open in January 2017 with 150 children. Ms. Nemet has been successful in negotiating a ratio of 6 children to 1 day care worker for the first year of operation.

-Day care workers earn $17.00 per hour. They work from 9 to 5 and get paid for 8 hours. Tiny Tots also must pay 7 percent of their salaries in the form of a Social Security contribution, along with 8 percent for unemployment benefits. Each day care worker receives $400 per month for health insurance. The city will have to cover these costs for the employees who work at the facility for the children of city employees. It does not have to cover the Social Security, unemployment, or health benefits for Ms. Nemet since Tiny Tots, Inc. is doing so on the basis of her total compensation.

-Children get a snack and lunch. The food cost is $10.00 per child per day. The cost of supplies is $5 per child per day. The City of Northville has purchased certain equipment (such as cots and desks) for the first 150 children. However, these costs are estimated to be $50 per child as the enrollment at the day care center goes up. For the first four months, it is expected that the number of children will grow by 1.5 percent, beginning in February 2019.

-Beginning June 2019, the monthly growth is expected to be 1 percent.

-The City of Northville is donating space and utilities.

-Mr. Legato says that the union will contribute to the cost of the citys new day care center by providing $15.00 per child per day for the children of union members. It is estimated that 75 percent of the children will be children of union members.

-The state will provide an annual grant of $300,000.

Day Care Budget Analysis

You are a budget analyst in the Budget Office of the City of Northville. Mayor Spark just asked you to run some numbers so that she can take a look at the arrangement that was just negotiated between Ms. Lux, Mr. Legato, and Ms. Nemet. You should prepare the budget in a spreadsheet. Complete the following tasks and a brief one-page memo describing your findings.

1. Prepare the baseline 2019 monthly budget for the day care. Determine the budget balance for each month (i.e. total revenues minus total costs). The spreadsheet that you create should consist entirely of formulas. This will make it easier to answer the other questions and will give you an opportunity to learn the basic capabilities of Excel. A copy of the correct baseline budget is presented on page 4 of this document for you to check your work against.
2. Suppose the child/staff ratio were changed to the maximum allowed by law. What impact would this have on the budget?
3. What changes can be made to balance the budget? What are the advantages and disadvantages of these changes? Produce a balanced budget and defend your choice of changes in your memo. Remember to report specific changes in the revenue and cost variables in your memo. For example, if you decide to reduce the daycare workers hourly wage from $17 to $15, report that in the memo. Just writing that you plan to reduce wages isnt enough. Any other changes that you propose should be described with a similar degree of detail.

Excel allows you to round up the solution of a calculation by simply writing roundup before the parentheses of the calculation. Recall that the number of children is expected to increase by 3 percent between January and February. Suppose that you have the number of children in January in cell B2. You can calculate the expected number of children in February and round up that calculation by entering the following formula into cell C2

=ROUNDUP(B2*(1+Parameters!$B4),0)

This formula calculates the estimated number of children for February and rounds it up to the next highest integer (remember that you have to have the Parameters worksheet filled out for this formula to work). You can use autofill to copy the formula from C2 down the row to F2. In G2, the formula must be changed to:

=ROUNDUP(F2*(1+Parameters!$B5),0)

since the expected growth rate of enrollment is different from June through the end of the year. Use autofill to copy this formula down the row to cell M2.

You should also round up to the next highest integer the number of workers for each month. You can do this for January with the following formula:

=ROUNDUP((B2/Parameters!$B7),0)

Use autofill to copy this formula from B3 to M3.

The best way to get through this is to fill out the Parameters worksheet in the starter spreadsheet that I sent you. Thatll help you organize the key details of the case. After that, the best strategy is to take on one piece at a time. Start with estimating the number of children for each month. Then estimate the number of workers for each month. After that, you can start working through each expenditure item. Use Excel formulas to obtain your estimates. Do not simply type in the numbers from the attached baseline budget.

Attachment: Baseline Budget Jan. Feb. March April May June July Aug. Sept. Oct. Nov. Dec. Total Children (rounded up 150 153 1561 159 162 164 166 1681 170 172 174 176 Employees (rounded up) 251 261 26 27 27 28 28 281 29 29 29 30 Labor Expenditures Training $6,250 $250 90 $250 $0 $250 SO SO $250 $0 90 $250 $7,500 Wages $68,000 $70,720 $70,720 $73,440 $73,440 $76,160 $76,160 $76,160 $78,880 $78,880 $78,880 $81,600 $903,040 Compensation for Director $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $36,000 Social Security 54,760 $4,950 $4.950 $5,141 $5141 $5,331 $5,331 $5,331 $5,522 $5,522 $5522 $5,712 $63,213 Unemployment $5,440 $5,658 $5,658 $5,875 $5,875 $6,093 $6,093 $6,093 $6,310 S6,310 $6,310 $6,528 $72,243 Health Insurance $10,000 $10,400 $10,400 $10,800 $10,800 $11,200 $11,200 $11,200 $11,600 $11,600 $11,600 $12,000 $132,800 Service Expenditures Food $30,000 $20,600 $31,200 $31,800 $32,400 $32,800 $33,200 $33,600 $34,000 $34,400 $34,800 $25,200 $394,000 Supplies $15,000 $15,300 $15,600 $15,900 $16,200 $16,400 $16,600 $16,800 $17,000 $17,400 $17,60 $197,000 Equipment 0 $150 $150 $150 $150 $100 $100 $100 $100 $100 $100 $100 $1,300 Other Expenditures City Inspection Fee $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $300 Total Expenditures $142,475 $141,053 $141,703 $146,381 $147,031 S151359 $151,709 $152,309 $156,687 $157,037 $157,637 $162015 $1,807 396 Revenues Fees from Parents $52,500 $53,550 $54,600 $55,650 $56,700 $57,400 $58,100 $58,800 $59,500 $60,200 $60,900 $61,600 $689,500 Union Contributions $33,750 $34,425 $35,100 $35,775 $36,450 $36.900 $37 350 $37,800 $38,250 $38,700 $39150 $29,600 $443,250 Startup Grant $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $300,000 Total Revenues $111,250 $112,975 $114,700 $116,425 $118,150 $119,300 $120,450 $121,600 $122,750 $123,900 $125,050 $126,200 $1,432,750 Deficit ($31,225 ($28,078) ($27,003) ($29,956) ($28,881) ($32,059 ($31,259 ($30,709 ($33,937) ($33,137) ($32587) ($35,815) ($374,646 B G H K L Feb. D March E April F |May I. M Dec. N Total June July Aug. Sept. Oct. Nov. Jan. Children (rounded up) Employees (rounded up) Labor Expenditures Training Wages Compensation for Director Social Security Unemployment Health Insurance Service Expenditures Food Supplies Equipment Other Expenditures City Inspection Fee Total Expenditures Revenues Fees from Parents Union Contributions Startup Grant Total Revenues Deficit Attachment: Baseline Budget Jan. Feb. March April May June July Aug. Sept. Oct. Nov. Dec. Total Children (rounded up 150 153 1561 159 162 164 166 1681 170 172 174 176 Employees (rounded up) 251 261 26 27 27 28 28 281 29 29 29 30 Labor Expenditures Training $6,250 $250 90 $250 $0 $250 SO SO $250 $0 90 $250 $7,500 Wages $68,000 $70,720 $70,720 $73,440 $73,440 $76,160 $76,160 $76,160 $78,880 $78,880 $78,880 $81,600 $903,040 Compensation for Director $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $36,000 Social Security 54,760 $4,950 $4.950 $5,141 $5141 $5,331 $5,331 $5,331 $5,522 $5,522 $5522 $5,712 $63,213 Unemployment $5,440 $5,658 $5,658 $5,875 $5,875 $6,093 $6,093 $6,093 $6,310 S6,310 $6,310 $6,528 $72,243 Health Insurance $10,000 $10,400 $10,400 $10,800 $10,800 $11,200 $11,200 $11,200 $11,600 $11,600 $11,600 $12,000 $132,800 Service Expenditures Food $30,000 $20,600 $31,200 $31,800 $32,400 $32,800 $33,200 $33,600 $34,000 $34,400 $34,800 $25,200 $394,000 Supplies $15,000 $15,300 $15,600 $15,900 $16,200 $16,400 $16,600 $16,800 $17,000 $17,400 $17,60 $197,000 Equipment 0 $150 $150 $150 $150 $100 $100 $100 $100 $100 $100 $100 $1,300 Other Expenditures City Inspection Fee $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $300 Total Expenditures $142,475 $141,053 $141,703 $146,381 $147,031 S151359 $151,709 $152,309 $156,687 $157,037 $157,637 $162015 $1,807 396 Revenues Fees from Parents $52,500 $53,550 $54,600 $55,650 $56,700 $57,400 $58,100 $58,800 $59,500 $60,200 $60,900 $61,600 $689,500 Union Contributions $33,750 $34,425 $35,100 $35,775 $36,450 $36.900 $37 350 $37,800 $38,250 $38,700 $39150 $29,600 $443,250 Startup Grant $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $25,000 $300,000 Total Revenues $111,250 $112,975 $114,700 $116,425 $118,150 $119,300 $120,450 $121,600 $122,750 $123,900 $125,050 $126,200 $1,432,750 Deficit ($31,225 ($28,078) ($27,003) ($29,956) ($28,881) ($32,059 ($31,259 ($30,709 ($33,937) ($33,137) ($32587) ($35,815) ($374,646 B G H K L Feb. D March E April F |May I. M Dec. N Total June July Aug. Sept. Oct. Nov. Jan. Children (rounded up) Employees (rounded up) Labor Expenditures Training Wages Compensation for Director Social Security Unemployment Health Insurance Service Expenditures Food Supplies Equipment Other Expenditures City Inspection Fee Total Expenditures Revenues Fees from Parents Union Contributions Startup Grant Total Revenues Deficit

Answer & Explanation Solved by verified expert

See Answer