Budgeting Assigment question
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 thirty-five percent of the city employees are now female. In a
recent survey of city employees, over forty percent have said that “affordable daycare for
children” was important to them. Meanwhile Director of the Office of Personnel Mary
Lux has become increasingly convinced that the lack of affordable daycare 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 city’s already
strained budget. She has told Legato, who is negotiating the daycare program on behalf
of the city employees, “We’ll give you space and utilities for a year at no cost. It is up to
you to come up with a suitable daycare center that conforms to state and federal law.”
Several regulatory mandates and non-discrimination laws fall into this category. The only
requirements specific to daycare centers are that (a) they be licensed and inspected once a
year, (b) all new daycare workers take part in a three day state-certified training program
and (c) the child/daycare giver ratio be no greater than 8 to 1. The annual inspection fee is
$500. The total cost of the three-day training program is estimated to be $200 per
employee.
Mary Lux is responsible for planning the details of the daycare program for the children
of city employees. With Mr. Legato’s approval, Ms. Lux has negotiated an arrangement
with a local non-profit agency that is already providing daycare services in the Northville
metropolitan area. Tiny Tots, Inc. has three locations; the contract with the City of
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 said, “We will not need
2
any additional administrative staff, since Ms. Perfekt, my administrative secretary, and I
could certainly handle the additional administrative work.” Ms. Perfekt earns $1,300 a
month. Ms. Nemet’s salary is $2,400 a month. Tiny Tots, Inc. also must pay 7.15 percent
of their salaries in the form of a social security contribution, 8 percent for unemployment
and disability benefits; 6 percent of salaries goes to a pension fund, and $60 per month for
health benefits is paid for each of them. These fringe benefits apply to all employees of
Tiny Tots, Inc.
The additional details of the contract are as follows: the daycare center will be open 20
days every month. Parents pay a monthly fee based on an 8-hour day (9:00 a.m. to 5:00
p.m.). Fees do not vary if less than a full day or less than a month of daycare is used by
the parents. Based on projected demand, it is expected that the daycare center will open
in January 1997 with 120 children. Ms. Nemet has been successful in negotiating a ratio
of 6 children to 1 daycare worker for the first year of operation.
Daycare workers earn $6.50 per hour. They work from 9 to 5 and get paid for eight hours.
Children get a snack and lunch. The food cost is $3.00 per child per day. The cost of
supplies is $1.50 per child per day. The City of Northville has purchased certain
equipment (such as cots and desks) for the first 120 children. However, these costs are
estimated to increase by $50 per child as the enrollment at the daycare center goes up.
For the first four months, it is expected that the number of children will grow by 10
percent, beginning in February 2000. Beginning June 2000, the monthly growth is
expected to be 5 percent.
Parents pay $200 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. Mr. Legato
says that the union will contribute to the cost of the city’s new daycare center by
providing $1.00 per child per day for the children of union members. It is estimated that
70 percent of the children will be children of union members. The state has a subsidized
daycare start-up grant for the first year of operation. This grant is $90,000 a year.
3
DAYCARE COMPUTER EXERCISE, PART 1
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. Since Mayor Spark may ask you some questions about the day
care budget, you should prepare the budget using parameters and as many formulas as
possible. A well-designed (and flexible) spreadsheet will simplify your task later.
Complete the following tasks and provide the tables and a brief one-page memo
describing your findings:
1) Prepare the baseline 2000 monthly budget for the daycare. (You can assume a calendar
year.) Determine the total surplus and deficit for each month.
2) Suppose the child/staff ratio were changed to the maximum allowed by law. What
impact would this have on the budget?
3) What would happen to the deficit if the enrollment increased by only 5 percent per
month for all months?
4) What other 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.