Question

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

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 $2,300 a month. Ms. Nemet's salary is $3,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 2019 with 130 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 $8.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.50 per child per day. The cost of supplies is $2.00 per child per day. The City of Northville has purchased certain equipment (such as cots and desks) for the first 130 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 2019.

Beginning June 2019, the monthly growth is expected to be 5 percent. (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 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.

DAY CARE COMPUTER EXERCISE, Part I

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 a brief memo describing your findings (you can do both parts of this assignment in a single memo if 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 for each individual line items

total cost

total revenues

surplus deficit

total surplus

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

(3rd spreadsheet)

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

deficit shrink? surplus?

3) On another sheet (within the same excel workbook), show what would happen to the deficit if the enrollment increased by only 5 percent 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? What are the advantages and

disadvantages of these changes? On a final sheet (within the same excel workbook), produce a balanced budget and defend your choice 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 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 2019 budget were correct, there are a few adjustments which must be made to

personnel scheduling and other cost factors for 2020. Create a monthly budget for 2020 that allows for the following adjustments.

Budget Adjustments:

create seperate workbook

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. fringe benefit

2) In 2019, 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

requirements)how many students needed to cover workers pay.

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: $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 services provided by the center.

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

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

Answer 1

1)Monthly budget

Particulars	Jan	Feb	March	April	May	June	July	August	Sept	Oct	Nov	dec	TOTAL
Annual Inspection cost													500
Training program	4333	4767	5243	5768	6344	6662	6995	7344	7712	8097	8502	8927	80695
Salary to Ms. Prfekt	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	27600
Salray to MS. Nemet	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	40800
Social Security contribution	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	4891
Unemployment	456	456	456	456	456	456	456	456	456	456	456	456	5472
Pension Fund	342	342	342	342	342	342	342	342	342	342	342	342	4104
Health benefits	120	120	120	120	120	120	120	120	120	120	120	120	1440
Total Costs	11359	11792	12269	12793	13370	13687	14020	14370	14737	15123	15528	15953	165501
No. of children	130	143	157	173	190	200	210	220	231	243	255	268
No of workers= (No. of children/6)	22	24	26	29	32	33	35	37	39	40	43	45
Cost per child:
Day worker pay	29467	32413	35655	39220	43142	45299	47564	49942	52440	55062	57815	60705	548724
Food cost	9100	10010	11011	12112	13323	13989	14689	15423	16195	17004	17855	18747	169459
Cost of supplies	5200	5720	6292	6921	7613	7994	8394	8813	9254	9717	10203	10713	96834
Incomereceived	32500	35750	39325	43258	47583	49962	52461	55084	57838	60730	63766	66954	605210
Net Profit /Loss on child	-11267	-12393	-13633	-14996	-16496	-17320	-18186	-19096	-20050	-21053	-22106	-23211	-209806
Subsidy received from govt.	-	-	-	-	-	-	-	-	-	-	-	-	90000
Donation	1820	2002	2202	2422	2665	2798	2938	3085	3239	3401	3571	3749	33892
Total Revenue	-9447	-10391	-11430	-12574	-13831	-14522	-15249	-16011	-16812	-17652	-18535	-19461	-85914
Surplus /Deficit	-20806	-22184	-23699	-25367	-27201	-28210	-29269	-30381	-31549	-32775	-34062	-35414	-251416

2)If child care ratio changes to 8:1 the deficit shrinks drastically.

Particulars	Jan	Feb	March	April	May	June	July	August	Sept	Oct	Nov	dec	TOTAL
Annual Inspection cost													500
Training program	3250	3575	3933	4326	4758	4996	5246	5508	5784	6073	6377	6695	60521
Salary to Ms. Prfekt	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	27600
Salray to MS. Nemet	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	40800
Social Security contribution	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	4891
Unemployment	456	456	456	456	456	456	456	456	456	456	456	456	5472
Pension Fund	342	342	342	342	342	342	342	342	342	342	342	342	4104
Health benefits	120	120	120	120	120	120	120	120	120	120	120	120	1440
Total Costs	10276	10601	10958	11351	11784	12022	12272	12534	12809	13099	13402	13721	145328
No. of children	130	143	157	173	190	200	210	220	231	243	255	268
No of workers= (No. of children/6)	16	18	20	22	24	25	26	28	29	30	32	33
Cost per child:
Day worker pay	22100	24310	26741	29415	32357	33974	35673	37457	39330	41296	43361	45529	411543
Food cost	9100	10010	11011	12112	13323	13989	14689	15423	16195	17004	17855	18747	169459
Cost of supplies	5200	5720	6292	6921	7613	7994	8394	8813	9254	9717	10203	10713	96834
Incomereceived	32500	35750	39325	43258	47583	49962	52461	55084	57838	60730	63766	66954	605210
Net Profit /Loss on child	-3900	-4290	-4719	-5191	-5710	-5995	-6295	-6610	-6941	-7288	-7652	-8035	-72625
Subsidy received from govt.	-	-	-	-	-	-	-	-	-	-	-	-	90000
Donation	1820	2002	2202	2422	2665	2798	2938	3085	3239	3401	3571	3749	33892
Total Revenue	-2080	-2288	-2517	-2768	-3045	-3198	-3357	-3525	-3702	-3887	-4081	-4285	51267
Surplus /Deficit	-12356	-12889	-13475	-14120	-14829	-15219	-15629	-16059	-16511	-16985	-17483	-18006	-94061

3) If the %age of children grows every month is 5%, the defcit is further decreased but not very much . a slight decrease in deficit can be seen.

Particulars	Jan	Feb	March	April	May	June	July	August	Sept	Oct	Nov	dec	TOTAL
Annual Inspection cost													500
Training program	3250	3413	3583	3762	3950	4148	4355	4573	4802	5042	5294	5559	51731
Salary to Ms. Prfekt	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	27600
Salray to MS. Nemet	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	40800
Social Security contribution	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	4891
Unemployment	456	456	456	456	456	456	456	456	456	456	456	456	5472
Pension Fund	342	342	342	342	342	342	342	342	342	342	342	342	4104
Health benefits	120	120	120	120	120	120	120	120	120	120	120	120	1440
Total Costs	10276	10438	10609	10788	10976	11173	11381	11599	11827	12067	12319	12584	136537
No. of children	130	137	143	150	158	166	174	183	192	202	212	222
No of workers= (No. of children/6)	16	17	18	19	20	21	22	23	24	25	26	28
Cost per child:
Day worker pay	22100	23205	24365	25584	26863	28206	29616	31097	32652	34284	35999	37798	351768
Food cost	9100	9555	10033	10534	11061	11614	12195	12805	13445	14117	14823	15564	144846
Cost of supplies	5200	5460	5733	6020	6321	6637	6968	7317	7683	8067	8470	8894	82769
Incomereceived	32500	34125	35831	37623	39504	41479	43553	45731	48017	50418	52939	55586	517307
Net Profit /Loss on child	-3900	-4095	-4300	-4515	-4740	-4977	-5226	-5488	-5762	-6050	-6353	-6670	-62077
Subsidy received from govt.	-	-	-	-	-	-	-	-	-	-	-	-	90000
Donation	1820	1911	2007	2107	2212	2323	2439	2561	2689	2823	2965	3113	28969
Total Revenue	-2080	-2184	-2293	-2408	-2528	-2655	-2787	-2927	-3073	-3227	-3388	-3558	56892
Surplus /Deficit	-12356	-12622	-12902	-13196	-13504	-13828	-14168	-14525	-14900	-15294	-15708	-16142	-79645

4) If we decrease the pay of daily worker from 8.5 per hour to 6.5 per hour then we can justify the budget in surplus mode.

Particulars	Jan	Feb	March	April	May	June	July	August	Sept	Oct	Nov	dec	TOTAL
Annual Inspection cost													500
Training program	3250	3413	3583	3762	3950	4148	4355	4573	4802	5042	5294	5559	51731
Salary to Ms. Prfekt	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	2300	27600
Salray to MS. Nemet	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	3400	40800
Social Security contribution	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	407.55	4891
Unemployment	456	456	456	456	456	456	456	456	456	456	456	456	5472
Pension Fund	342	342	342	342	342	342	342	342	342	342	342	342	4104
Health benefits	120	120	120	120	120	120	120	120	120	120	120	120	1440
Total Costs	10276	10438	10609	10788	10976	11173	11381	11599	11827	12067	12319	12584	136537
No. of children	130	137	143	150	158	166	174	183	192	202	212	222
No of workers= (No. of children/6)	16	17	18	19	20	21	22	23	24	25	26	28
Cost per child:
Day worker pay	16900	17745	18632	19564	20542	21569	22648	23780	24969	26217	27528	28905	268999
Food cost	9100	9555	10033	10534	11061	11614	12195	12805	13445	14117	14823	15564	144846
Cost of supplies	5200	5460	5733	6020	6321	6637	6968	7317	7683	8067	8470	8894	82769
Incomereceived	32500	34125	35831	37623	39504	41479	43553	45731	48017	50418	52939	55586	517307
Net Profit /Loss on child	1300	1365	1433	1505	1580	1659	1742	1829	1921	2017	2118	2223	20692
Subsidy received from govt.	-	-	-	-	-	-	-	-	-	-	-	-	90000
Donation	1820	1911	2007	2107	2212	2323	2439	2561	2689	2823	2965	3113	28969
Total Revenue	3120	3276	3440	3612	3792	3982	4181	4390	4610	4840	5082	5336	139661
Surplus /Deficit	-7156	-7162	-7169	-7176	-7184	-7191	-7200	-7208	-7218	-7227	-7237	-7248	3124