Revenue and Expenditure Projection Assignment: Day Care Comes to Northville

Formulate defining criteria (defining attributes) for the concept. Include a discussion of the concepts antecedents and consequences for the concept.
June 28, 2019
Descriptive Statistics Part I: Normal Curves, Variability, and Plotting
June 28, 2019

Revenue and Expenditure Projection Assignment: Day Care Comes to Northville


SUBJECT: Revenue and Expenditure Projection Assignment: Day Care Comes to Northville

This assignment presents you with an opportunity to apply what you have been learned to this

point, review program information, and come up with simple budgets based on known facts,

assumptions, and projections.

The subject of this assignment is the Day Care Comes to Northville case which follows this

memorandum. You will complete and submit Part 1 and selected components of Part 2 of the

case. (I have shaded in gray the sections of Part 2 that you will NOT be responsible for. This

case is challenging for most students, so I have decided to simplify it somewhat by striking some

sections of the case that may be especially challenging.)

You will prepare budgets as required by the case study. You will submit your base and adjusted budgets in Excel spreadsheet tables, identify all of your assumptions in developing the budgets, and write a one-page memorandum to Mayor Spark about the budgets you create – one memo for the Part 1 budget and one memo for the Part 2 budget.

I recommend that you first read through this assignment and then carefully read through Part 1

and the relevant section of Part 2 before you start working on your budgets. Be sure to identify

all the required elements and make sure that you address each. (You may want to make a

checklist for yourself as you read through the materials.)

In preparing your budget, begin by making a list of known information. For example, the

starting enrollment, enrollment growth, child/staff ratio, etc.) Then, identify all the assumptions

that you make and the basis for the assumptions. In this regard, keep in mind that you must

make certain assumptions when preparing a budget, even a simple budget like this. Information

is often incomplete and imperfect. Given that, it is important that each assumption: 1) be

reasonable given what is known, and 2) be clearly documented and explained.

The last page of the case mentions “effective tax rates.” As you will see, this case is about user

fees, not taxes, but the concept can still be applied. “Effective tax rate” is defined and discussed

on pages 135-136 of your text.

Day Care Comes to Northville assignment

Your memoranda should be formatted like this memorandum (single spaced, one blank space

between paragraphs, one-inch margins, 12 point type), in Word or a Word-compatible format,

and no more than one page in length. (The page limitation places a responsibility on you to

determine what is most important to communicate to the Mayor; be clear and concise.)

In grading this assignment, I will consider the following:

  •  Is your work complete, in that you addressed all elements and of the assignment and all factors in the case within the constraints of the assignment?
  •  Is your analysis clear, in that you provide the required information in spreadsheets that are well organized and easy to follow and understand?
  •  Are your assumptions identified and reasonable given the information in the case?
  •  Is your analysis accurate, in that your calculations are correct and supported by the information in the case?
  •  Are your memoranda clear, concise, and accurate in that they address the assignment, do not exceed the page limitation, are well organized and written using conventions of the English language, and are persuasive for the intended audience?
  • Is your work timely, in that it was submitted on time?


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 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 city’s already strained budget. She

has told Legato, who is negotiating the day care 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 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 $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 day care 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 day care 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 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 day care 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 day care is used by the parents.

Based on projected demand, it is expected that the day care center will open in January 2000

with 120 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 $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 day care 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 day care 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 day care start-up grant for the

first year of operation. This grant is $90,000 a year.


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 day care. (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



It is now September of 2000, and the day care center has been successfully operating for 8

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

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

construct the 2000 budget were correct, there are a few adjustments which must be made to

personnel scheduling and other cost factors for 2001.

Budget Adjustments

1) Tiny Tots, Inc. has started providing paid vacation/personal days to their employees. Day

care workers get 2 weeks of paid time, based on 40 hours per week.

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

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

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

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

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

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

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


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

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

Wage: $7.0 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 $1,000 per month and fire and

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

5) All other costs are expected to increase by an inflation rate of 5 percent during 2001.

Additional Services:

Besides the additional staffing requirements, Tiny Tots is thinking of adding additional services to

the day care center:

1) Expanded hours for the day care from 8:00 am to 6:00 pm. The children are expected to

come in two shifts;

— 8:00 am: 70 percent of children

— 10:00 am: 30 percent of children

2) Adding care for infants during these expanded hours. (They are expected to arrive in the

same percent for each time as toddlers.) The staffing ratio can be no higher than 3 infants to

1 staff person. The staff would receive the same pay and benefits as those taking care of

toddlers. Food and equipment costs are expected to be the same per child. The city will

subsidize equipment for the first 100 infants. The director expects to start in January 2001

with 100 infants and would like to maintain roughly a ratio of 3 toddlers for each infant in the


Presently, the day care center is housed in an old school that is not being used by the city.

While the city agreed to provide free space and utilities the first year, the mayor has said this will

not be possible for 2001. The city is willing to lease the full facility and provide utilities for

$2,000 per month. The school has 8,000 square feet, and state regulations require the

following square footage for children;

15 square feet/toddler

10 square feet/infant

ASSIGNMENT: Modify your spreadsheet from Part 1 to incorporate these changes. Save

your original version for Part 1 just in case! You can assume that the child/staff ratio for

toddlers will be maintained at 6/1. Carry out the following tasks and provide a copy of your

tables and a one-page memo summarizing your results:

1) Develop the 2001 budget by month with these changes. Determine the total expenditures

and expenditures per child. The director would like the budget broken into the two “mission

centers” and variable, step, and fixed costs* indicated. You should assign indirect costs* to

each mission center using the most appropriate allocation factor. (Justify your choice.)

Enrollment in the center could continue to grow by 5 percent per month as long as space is

available. Assume that the monthly rate is $300/month for childcare.

2) Assuming that the center receives subsidies of;

— $3/day/child for day care.

How much would daily fees to parents have to be to break even? Assume that:

  1. a) the same fees for childcare are charged per child (whether infant or toddler).
  2. b) different fees can be charged for each mission center to cover the costs of this center.

(* See the assignment memorandum for guidance.)

  1. c) the city adopts a sliding scale fee based on income. Assume that city employees can be

grouped into three income classes:

Low income: average $15,000/year

Middle income: average $30,000/year

High income: average $50,000/year

and that city workers are evenly divided between these income classes. Assume that fees for

toddlers in a particular income class will be the same as infants in that class. Develop a sliding

scale fee that will cover the center’s deficits for 2001. Justify your choice of fee. (Hint:

Calculate effective tax rates*).

(* See the assignment memorandum for guidance.)


"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

Hi there! Click one of our representatives below and we will get back to you as soon as possible.

Chat with us on WhatsApp