Answer step by step with all excel formulas, please.

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 35 percent of the city employees are now female. In a recent survey of city employees, over 40 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, "Well 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 10 to 1. The annual inspection fee is $800. The total cost of the three-day training program is estimated to be $500 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 $3,000 a month. Ms. Nemet's salary is $5,000 a month. Tiny Tots, Inc. also must pay 7.65 percent of their salaries in the form of Social Security (6.2%) and Medicare contributions (1.45%), 8 percent for unemployment and disability benefits; 6 percent of salaries goes to a pension fund, and $260 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 2024 with 120 children. Ms. Nemet has been successful in negotiating a ratio of 8 children to 1 daycare worker for the first year of operation.

Day care workers earn $20 per hour. They work from 9 to 5 and get paid for eight hours. Children get a snack and lunch. The food cost is $4.00 per child per day. The cost of supplies is $2.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 $60 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 monthly, beginning in February 2024. Beginning June 2024, the monthly growth is expected to be 5 percent.

Parents pay $550 per month per child. In the first year, Northville is "donating" space and utilities. Ms. Lux says that this city contribution is worth $6,000 a month. Mr. Legato says that the union will contribute to the cost of the city's new day care center by providing $2.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.

EXERCISE

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 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 memo (2 single-spaced pages) describing your findings:

1) Prepare the baseline 2024 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 (assuming the child/staff ratio is 8:1)?

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.

INSTRUCTION

a) You will need to define certain assumptions (or expectations) about how elements of the budget may change or stay constant (e.g., administrative salaries and non-wage compensation). Assumptions are defined as simplifications that are made in the spreadsheet design process to make it manageable. For example, you might assume in designing this day care budget that: one-quarter of Director and Secretary salary allocated to center; Inspection fee and state subsidy are equally divided across months; there is no turnover in day care workers, and that all day care workers receive the same hourly wage; staff receive no vacation or sick leave with pay. While these assumptions are unrealistic in the long run, they may be reasonable simplifications for the first years budget. As you encounter questions about key parts of this exercise, make assumptions and formally write these assumptions down. The goal is to design a budget that is both flexible and transparent to future budget analysts.

b) You will also need to define certain parameters (or key variables) that affect this years budget but might change next year (e.g., wages, inflation rates, benefit levels, user fees, unit costs, etc.). Put these on the worksheet along with your assumptions. It is better to have your list of parameters on one excel worksheet, organized into logical categories, and your actual budget on the other, and to link the two pages. This will save you a lot work by allowing you to easily and quickly change the budget as the parameters change. One flexible budget is a lot easier than creating a different budget to answer each question.

c) You must identify all of the objects of expenditure from the text. Some costs will be fixed or variable, and others will be somewhere in between.

d) You must identify all sources of revenue (e.g., grant, fees, etc.).

e) When building your budget, try to group similar expenditures together (i.e., salaries, benefits, etc.).

Turn in your spreadsheet and a pdf file of your memo with the 4 budget tables in the appendix.