DAY CARE COMES TO NORTHVILLE (show formulas in excelspreadsheet)
DESCRIPTION

Northville, a medium-sized city in the midwestern part of theUnited States, has experienced a significant change in thecomposition of its municipal work force of 1,800 employees.Approximately thirty-five percent of the city employees are nowfemale. In a recent survey of city employees, over forty percenthave said that "affordable day care for children" was important tothem. Meanwhile Director of the Office of Personnel Mary Lux hasbecome increasingly convinced that the lack of affordable day careis one of the main reasons for absenteeism and lateness among cityemployees. Mayor Petula Spark, some of the members of the citycouncil, and the leader of the major city employees' union, DenardoLegato, all agree that something should be done. The question theyare trying to answer is, what should it be?

Mayor Spark is in favor of doing something, in principle, butshe is not in favor of incurring a major new expense, given themany legitimate claims on the city's already strained budget. Shehas told Legato, who is negotiating the day care program on behalfof the city employees, "We’ll give you space and utilities for ayear at no cost. It is up to you to come up with a suitable daycare center that conforms to state and federal law." Severalregulatory mandates and non-discrimination laws fall into thiscategory. The only requirements specific to day care centers arethat (a) they be licensed and inspected once a year, (b) all newday care workers take part in a three day state-certified trainingprogram and (c) the child/day care giver ratio be no greater than 8to 1. The annual inspection fee is $500. The total cost of thethree-day training program is estimated to be $200 peremployee.

Mary Lux is responsible for planning the details of the day careprogram for the children of city employees. With Mr. Legato'sapproval, Ms. Lux has negotiated an arrangement with a localnon-profit agency that is already providing day care services inthe Northville metropolitan area.

Tiny Tots, Inc. has three locations; the contract with the Cityof Northville would be a fourth center. The Director of Tiny Tots,Klara Nemet, is enthusiastic about the prospects of a new centerspecifically for city employees. While discussing the proposedarrangements with Ms. Lux, Ms. Nemet said, "We will not need anyadditional administrative staff, since Ms. Perfekt, myadministrative secretary, and I could certainly handle theadditional administrative work."

Ms. Perfekt earns $2,300 a month. Ms. Nemet's salary is $3,400 amonth. Tiny Tots, Inc.

also must pay 7.15 percent of their salaries in the form of asocial security contribution, 8

percent for unemployment and disability benefits; 6 percent ofsalaries goes to a pension fund, and $60 per month for healthbenefits is paid for each of them. These fringe benefits apply toall employees of Tiny Tots, Inc.

The additional details of the contract are as follows:

The day care center will be open 20 days every month. Parentspay a monthly fee based on an 8-hour day (9:00 a.m. to 5:00p.m.).

Fees do not vary if less than a full day or less than a month ofday care is used by the parents.

Based on projected demand, it is expected that the day carecenter will open in January 2019 with 130 children. Ms. Nemet hasbeen successful in negotiating a ratio of 6 children to 1 day careworker for the first year of operation.

Day care workers earn $8.50 per hour. They work from 9 to 5 andget paid for eight hours.

Children get a snack and lunch. The food cost is $3.50 per childper day. The cost of supplies is $2.00 per child per day. The Cityof Northville has purchased certain equipment (such as cots anddesks) for the first 130 children. However, these costs areestimated to increase by $50 per child as the enrollment at the daycare center goes up. For the first four months, it is expected thatthe number of children will grow by 10 percent, beginning inFebruary 2019.

Beginning June 2019, the monthly growth is expected to be 5percent. (5% of mays total)

Parents pay $250 per month per child.

In the first year, Northville is "donating" space and utilities.Ms. Lux says that this city contribution is worth $2,000 a month.(everything needs to be showed in budget)

Mr. Legato says that the union will contribute to the cost ofthe city's new day care center by providing $1.00 per child per dayfor the children of union members. It is estimated that 70 percentof the children will be children of union members.

The state has a subsidized day care start-up grant for the firstyear of operation. This grant is $90,000 a year.

DAY CARE COMPUTER EXERCISE, PartI

You are a budget analyst in the Budget Office of the City ofNorthville. Mayor Spark just

asked you to "run some numbers" so that she can take a look atthe arrangement that was just negotiated between Ms. Lux, Mr.Legato, and Ms. Nemet. You should prepare the budget in aspreadsheet. Since Mayor Spark may ask you some questions about theday care budget, you should prepare the budget using parameters andas many formulas as possible. A well-designed (and flexible)spreadsheet will simplify your task later.

Complete the following tasks and a brief memo describing yourfindings (you can do both parts of this assignment in a single memoif you would like): part 1 and part 2 in one memo.

1) Prepare the baseline 2019 monthly budget for the day care.(You can assume a calendar year.)  

(monthly budget)

1 column items, second jan, feb, march ..... grand total foreach individual line items

total cost

total revenues

surplus deficit

total surplus

Determine the total surplus and deficit for each month andprovide an annual total (for all expenditures and revenues).

(3rd spreadsheet)

2) Suppose the child/staff ratio were changed to the maximumallowed by law.(8 kids one teacher) On another sheet (within thesame excel workbook), create a monthly budget that would allow forthis change. What impact would this have on the budget?

deficit shrink? surplus?

3) On another sheet (within the same excel workbook), show whatwould happen to the deficit if the enrollment increased by only 5percent per month for all months? isolation of staff ratio

8:1 ... next to each other

4) What other changes can be made to balance the budget? Whatare the advantages and

disadvantages of these changes? On a final sheet (within thesame excel workbook), produce a balanced budget and defend yourchoice of changes. freewill you decide how would you balance books,get to where we are breaking even

8:1 ratio, slow growth, hike union contribution

DAY CARE COMPUTER EXERCISE, Part II

It is now September of 2019, and the day care center has beensuccessfully operating for 8

months. The demand for day care has been as strong asanticipated, with enrollment growing

by 5 percent per month and significant unmet demand. While mostof the parameters used to

construct the 2019 budget were correct, there are a fewadjustments which must be made to

personnel scheduling and other cost factors for 2020. Create amonthly budget for 2020 that allows for the followingadjustments.

Budget Adjustments:

create seperate workbook

1) Tiny Tots, Inc. has started providing paid vacation/personaldays to their employees. Day care workers get 2 weeks of paid time,based on 40 hours per week. fringe benefit

2) In 2019, the budget did not include coverage for a lunch hourfor each employee. As a

result, employees had to work during lunch; that violates unionrules. This year, the staff will

be provided a one-hour paid lunch (they still get paid for 8hours/day), and the director is

considering using part-time work study college students to coverthe lunch hours. The

college students have to work at least 15 hours per week andwill get paid at the going rate

for day care workers. Lunch hours are from 11 to 2, andemployees can be assigned to any

lunch hour. (The objective is to schedule lunch hours for thestaff to minimize staffing

requirements)how many students needed to cover workers pay.

3) Day care workers can work up to 9 hours per day, 40 hours perweek, without being paid

overtime (time and a half). The personnel costs will riseto:

Wage: $9.50 per hour

Social Security: 7.5%

Unemployment: 8%

Pension: 6.5%

Health insurance: $100

4) In addition, the center has had to add liability insurance of$2,100 per month and fire and

theft insurance of $500 per month that will cover any servicesprovided by the center.

5) All other costs are expected to increase by an inflation rateof 5 percent. modified from part one

ASSIGNMENT: Modify your spreadsheet from Part 1 to incorporatethese changes. Please submit a separate excel workbook for Part Iand Part II separately! Assume that the child/staff ratio fortoddlers will be maintained at 6:1 for Part II. You may carry overany other alterations you made to balance the budget from Part Isuch as fee increases. Carry out the following tasks and youprovide a single memo summarizing your results for both Part I andPart II .