Assignment: WESTWOOD HOMELESS TRAINING CENTER
Westwood, a medium-sized city in Washington, has a significant homeless population.Mayor Ozurumba is in favor of doing something, in principle, but he is not in favor of incurring a major new expense, given the many legitimate claims on the city's already strained budget. He has told Mr. Gates, who is developing the homeless training center, "We'll give you space and utilities for a year at no cost. It is up to you to come up with a suitable homeless training center that conforms to state and federal law." The only requirements specific to homeless training centers are that (a) they are licensed and inspected once a year, (b) all new homeless training centerworkers complete a three day state-certified training program and (c) the homeless person/worker ratio can be no greater than 10 to 1. The annual inspection fee is $750. The total cost of the three-day training program is estimated to be $250 per employee.
Mr. Gates has negotiated an arrangement with a local non-profit agency that is already providing training to the homeless in the Westwood metropolitan area.Fisherman Training Centers, Inc. has three locations; the contract with the City of Westwood would be a fourth training center. While discussing the proposed arrangements with Mr. Gates, Ms. Jobs said, "We will not need any additional administrative staff, since Ms. Edison, my administrative secretary, and I could certainly handle the additional administrative work."
Ms. Edison earns $2,000 a month. Ms. Job's salary is $3,000 a month. Fisherman Training Centers, Inc.also must pay $60 per month for health benefits for each employee. These fringe benefits apply to all employees of Fisherman Training Centers, Inc.
The additional details of the contract are as follows:
• The homeless training center will be open 20 days every month. The center operates on an 8-hour day (9:00 a.m. to 5:00 p.m.).
• Based on projected demand, it is expected that the homeless trainingcenter will open in January 2018 with 160 homeless people. Ms. Jobs has been successful in negotiating a ratio of 6 homeless individual to 1 staff worker for the first year of operation.
• Staff workers earn $11.00 per hour. They work from 9 to 5 and get paid for eight hours.
• Homeless people get fed daily. Through an agreement with Whole Foods, food cost is $4.00 per person per day. The cost of supplies is $2.50 per person per day. The City of Westwood has purchased certain equipment (such as paper and pens) for the first 120 homeless people. However, these costs are estimated to increase by $50 per person as the population of the homeless training center increase. For the first four months, it is expected that the homeless population will grow by 15 percent, beginning in February 2018.
• Beginning June 2018, the monthly growth is expected to be 5 percent.
• The State of Washington will pay $250 per month per homeless person. In the first year, Westwood is "donating" space and utilities.
• Mr. Gates says that this city contribution is worth $2,000 a month. Since many of the homeless in the city are veterans, the Veteran's Administration will contribute to the cost of the city's newhomeless training center by providing $2.00 per person per day for homeless veterans. It is estimated that 70 percent of the homeless are veterans.
• The state has a subsidized homeless training centergrant for the first year of operation. This grant is $120,000 a year.
HOMELESS TRAINING CENTER COMPUTER EXERCISE, PART 1
You are a budget analyst in the Budget Office of the City of Westwood. Mayor Ozurumba justasked you to "run some numbers" so that he can take a look at the arrangement that was just negotiated between Mr. Gates, Ms. Jobs, and Ms. Edison. You should prepare the budget in a spreadsheet. Since Mayor Ozurumba may ask you some questions about the homeless training center 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 one-page memo describing your findings:
1) Prepare the baseline 2018 monthly budget for the homeless training center. (You can assume a calendar year.) Determine the total surplus and deficit for each month.
2) Suppose the homeless person/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 10 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.
Homeless Training Center Exercise Guide
Tips:
1. Get started early: this will take more time than you think.
2. Submit your budget in electronic form so I can examine it. If time permits, we will examine a one or two daycare budgets in class, so be prepared to discuss yours.
3. Collaboration: you may discuss techniques with each other teams (e.g. how to link pages), but the choices and calculations you make should be your own.
4. Respond to the questions with actual examples and estimates from your own budget. For example, answering question two by saying "reducing the child: staff ratio will reduce costs" is not a complete answer. Pick some specific changes and estimate the impact.
5. Understand the difference between assumptions and parameters. Parameters are key variables affecting budgets that are expected to change at least once in the next several years, such as wages, inflation rates, benefit levels, user fees, unit costs, etc. Read through the case narrative and highlight those factors that are parameters. 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 homeless training center budget that there is no turnover in homeless training centerworkers, and that all homeless training centerworkers receive the same hourly wage. While these assumptions are unrealistic in the long run, they may be reasonable simplifications for the first year's 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.
6. Note that the parameters associated with fringe benefits include the percent of wages required to set aside for social security, unemployment insurance, pensions, and monthly costs for health insurance.
7. It's a good idea to have your list of parameters on one excel worksheet, organized into logical categories, and your actual budget on the other. The actual budget should identify all of the major expenditures and costs (lay these out horizontally) for each month (lay out months vertically). The parameters should be a simple list of numbers e.g. salary, teacher-student ratio. This will help make your budget transparent. You can make your budget flexible by linking the parameters to the calculations on the budget page. As you change the parameters, the budget should automatically change. This will save you a lot, since one flexible budget is a lot easier than creating a different budget to answer each question.
8. Think about the desirable qualities for a budget and try to implement those. The budget should be easy to understand, clearly laid out and employing logical formulas to make calculations. As mentioned above, it is also desirable for such a budget to be flexible, able to adjust to changing parameters. You should only need one excel spread sheet (with a parameters worksheet and the actual budget worksheet) plus your memo.
Formatting:
9. Absolute cell references: Excel allows you to use formulas to link to a cell, but the formula will normally adjust as you move it around. For instance the formula for B2 might be =A1+A2. If you move the formula to B3 it becomes =A2+A3. This works well for some instances, but in other occasions you will want to use absolute cell references. Absolute cell references, denoted by putting a $ in front of either the column or row address, fixes the cell address in the formula so that it doesn't change when it is copied to a new cell. This will be useful when referring back to specific parameters. So, for example, if you wanted to consistently add A1 to another cell, the formula would become $A$1+A2 (or whatever the variable cell).
10. Note that excel allows you to round or roundup the solution of a calculation by simply writing "round" or "roundup" before the brackets of the calculation.