Exploring a Forecasting Tool

Goals and Hints for Assignment

Many a time in your career you will encounter financial tools or spreadsheets that you did not create.  You will need the ability to use tools created by others and understand how they work.  The latter is particularly important, as you should understand and be comfortable with the assumptions driving the outputs.

To complete this assignment you will need to understand Excel functions (how to look them up and how to get help), the IF() family of functions, and formula auditing.  

You also should play with and explore the tool before attempting the questions below.

Assignment 

You have been asked to present a 5-year projection of LAUSD expenses to city leaders in two days.  Your somewhat unreliable finance intern, JF, found a spreadsheet “Tool_Budget_Projection.xlsx” on the “Smarter School Spending” website. This tool allows district management to enter basic budgetary information and then projects revenues and expenditures for five years.  

JF loves this tool as it saved him from having to do his own projections.  JF has already entered LAUSD’s General Fund data into the tool and has sent it to you.  Unfortunately, that is all JF will be doing to help you prepare.  This afternoon he was invited to the Smoldering Chicken music festival (online, socially distanced?), and he immediately announced he would be “unavailable” for the next three days.  

It is now your job to understand and evaluate the tool and the results it provides.  Please prepare a Word document with answers to the following questions.  Also, turn in your custom projection in a separate Excel Workbook.

1. There are three projection methods used in the workbook – “Light Touch”, projections based on average growth, and projections based on enrollment trends.  Describe each of these methods and how they differ from one another. What are the critical assumptions in each?  Feel free to use equations or formulas in your exposition.

2. How does LAUSD fare under each of these three projection scenarios?  How do these projections compare to LAUSD’s own projections (found on page 78 of the LAUSD budget)?

3. Before he left, JF muttered something about “How nothing mattered as the district is financially doomed.”  Knowing JF’s work ethic, you figure this statement must be based on the default analysis – the average growth projections. What is driving the growth in expenses?  How worried should the district be about such a doomsday scenario?

4. Column AC in the Expenses sheet allows you to decide how to project each line item – including using your own methodology.  Please make the selections and changes you think are appropriate.  Explain what you did and your reasoning for major choices.  (E.g., “I projected school-based salaries using the enrollment method as number of these personnel are likely to vary directly with enrollment).  How does your final projection compare to those made in the LAUSD budget?  From your projection, what do you believe the district’s financial position would be in 2021?

5. What improvements, if any, would you make to this tool – both in terms of usability and the underlying methods?

6. How useful was this tool for projecting LAUSD finances?  How useful would it be for other districts (I.e., are there special circumstances with LAUSD that affect the usability of the tool)?  If given the option, would you use this tool or a similar tool – or create your own projections?

Questions 

1. What are the advantages and disadvantages of the traditional salary schedule? 

2. What are the advantages and disadvantages of a system like IMPACT relative to the traditional salary schedule approach?  

3. What approach to teacher salary structures would you take?  What are its weaknesses – and how might you guard against them?