Principal Hilda Cook has asked for a financial analysis of three options to implement a 12 percent budget cut imposed by the district. She sent a memo to her staff person, Lina Lamont. Before embarking on this assignment, please thoroughly read the case(Please see the case and memo I provided), and carefully review the instructions for all of the interlinking assignments.
Follow these steps to work through the model:
In the worksheet named “Modal A Status Quo” please complete Table A-2: Assumed Cost Functions and Table A-4: Proposed Budget Expenses by Program--Status Quo. Important: Do not complete Table A-4 by typing in numbers. It should include only formulas that either reference earlier tables, or entries in A-4. 2.
Next, copy the entire worksheet from “Model A Status Quo” into the worksheet named “Model B Eliminate Spanish.” Zero out the personnel and other variable expenses associated with elimination of the Spanish language program. Note that fixed costs will either remain associated with that program (e.g., depreciation of the Spanish lab equipment) or will be reallocated through the cost allocation formulas to other programs (e.g., some administrative and central office costs).
Then, copy the entire worksheet from “Model A Status Quo” into the worksheet named “Model C Teacher Layoffs.” Reduce the number of teachers assumed to be in the regular instructional program by altering the student-teacher ratio. If Model A has been constructed correctly, it should recalculate the costs associated with an increase in student/teacher ratios.
Next, conduct a targeted analysis of budget cuts by filling out the model in the worksheet called “Model D Targeted Reductions.” Note, to simplify the assignment, this is a truncated model, cells of which should reference Table A-4 in “Modal A Status Quo.”
Finally, summarize the findings of these four options (Status Quo plus three cutback scenarios) in Worksheet called Tble E Options Analysis Summary.