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 expens

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 center workers 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 training center 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 new homeless 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 center grant 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 just asked 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.