Answer Happy

This is all one large problem and I am struggling with putting
the pieces together, can anyone help?
Northville, a medium-sized city in the midwestern part of the
United States, has experi- enced 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
This exercise is provided to The Electronic Hallway subscribers
with the express permission of the authors, Associate Professor
William Duncombe andJeffrey Straussman, Maxwell Professor of
Teaching Excellence, at The Maxwell School of Citizenship and
Public Affairs, Syracuse University.
This is a peer reviewed exercise, published in The Electronic
Hallway Journal (http://www.hallway.org/journal/) Vol. 1, 2000.
The Electronic Hallway is administered by the University of
Washington's Daniel J. Evans School of Public Affairs. This
material may not be altered or copied without written permission
from The Electronic Hallway. For permission,
email [email protected], or phone (206) 616-8777.
Electronic Hallway members are granted copy permission for
educational purposes per the Member’s Agreement
(www.hallway.org).
Copyright 2000 The Electronic Hallway
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.
2
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.
Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X V Arial Insart v ~AA 18 ab Wien Text Custom 47-O 4 Y Peste B T = = Warge & Center DEX Delete Et Format Conditional Format Cell Formatting as Table Styles > Sort & Filter Find & Selost Ane ye Data Sensitivity Y N P R S T u v w Total A2 x fx Tiny Tots Daycare-- City of Northville A D E H к L M 3 2000 Annual Budget-- Baseline Budget January February March Apri May June July Au Seuleber Dulover November Decours Oktober December 5 8 Children (rounded upl 7 Employees (rounded up! & Expenditures: 9 Salaries 10 Day Care Stall 11 Administrator 12 Secretary 13 14 Sub total 15 Fringe Benefits 18 Social Securty 17 Uhemp Dicablity 18 Health Insurance 119 Pension 20 21 Sub total 22 23 Sub-total Personal 24 Other Expenditures 25 | Fac FCC 26 Supplies 27 Capital Equipment 2년 Employee training 29 Annual Inspection 30 Facility rental 31 Sub-Total 32 33 Total Expenditures 35 Revenues 36 Startup grant 37 City tollywoody 30 Union subsidy 39 Foschid 20 1 Deficit -2 As X of Expenditures 43 25 28 17 29 50 51 A-1 A-2 + MTI

o.. AutoSave Or or EU- Daycare Center Budget Format.xls - Compatibility Mode a in " A A 10 Aral ab Wrap Text Custom Σ' Αγ. | 中日 SI Insert v I Delete Format v 10. 5 Y Pasta B TV - w Av === Morge & Center $ % ) 6,8 Conditional Format Cell Formatting as Table Styles Sort Filter Find Seleet Anaya Data Sensitity A50 Xfx X D E F G H 1 J K L M м N o P Q B S T U v A B С 1 Attachment A-1 2 Tiny Tots Daycare-City of Northville - 3 2000 Annual Budget-Baseline Budget 4 o Assumptions: 61) Alcay care workers got the same wage and work futme. 7 2) There is no tumover in daycare workers 8 3: Parents pay monthly fee even child mene some day 9 ) Staff receive no vacation or sick leave with pay. 105) Onc quarter of Director and Sccrctary salary boasted to center. 11 New Amployees are not paid for the 3 day training session, 12 ) Inspec.un fue and state tuleequally divided across ronths. 13 Parameters: 14 Children 15 Starting enrolment 16 Entalment growth Feb. May 17 Enrolment growth fauna- 18 Employees frounded up) 19 Child staff ratio 20 Viark days month 21 Four day employee 22 Administrave personnel 23 Salary Expenditures Hourly wage-dayor worker Administrator salary month 25 Secretary sa rimonth Alocation parcantonsacratory 20 Fringe Benefits 29 Social Securly 1941 80 Unemp/bability ) -31 Health insurance (month) 32 Pension() 33 Employee training employce 34 Other Expenditures 35 Food coets day chid 35 Supples nosticaychild 37 Captal equipment Costa 38 Annual Inspection 39 Facility renta month 40 Raven 11 Startup grunt 42 City facility subsidy month 43 Union subsidyerday Percent union members 45 Monthly fee per child 18 47 43 A-1 A-2 +