In this assignment, you will be placed in the role of Director of Finance for Big Fizz, an up-and-coming beverage company entering its third year of operation. The company has supplied you with its historic sales and expense data for its first two years of operation. In your first task as Director of Finance, you’re being asked to create a financial projection that extrapolates their future revenue, expenses, cash flow, and overall cash balance based on historic trends.
Part I: Revenue, COGS, and OpEx Forecasting
Using the information provided in the starter file, create forecasts for Revenue, COGS, and the Operating Expenses of the company from 1/2022 through 12/2025.
For each forecast, capture both the baseline forecast as well as the lower bound and upper bound values associated with a 95% confidence interval.
Each forecast (Revenue, COGS, and OpEx) should have its own tab with both the values and the graphs contained therein. Ensure that your forecast properly captures the trend associated with the data set.
Part II: Burndown Projections
Next, create three copies of the historic Burndown tab. Name them: “Burndown (Base Forecast),” “Burndown (Pessimistic Forecast),” and “Burndown (Optimistic Forecast).”
For each tab, pull the relevant information associated with each forecast to create a Base, Pessimistic, and Optimistic view of the company’s monthly cash flow and remaining balance month over month.
Your pessimistic forecast should present the absolute worst-case scenario. In contrast, your optimistic forecast should present the absolute best-case scenario. (Note: You should pay special attention to which piece of information should be drawn in each scenario.)
For each tab, you should have complete burndown values between the dates 1/1/2020 and 12/1/2025, as well as associated graphs for Monthly Cash Flow and Running Cash Balance.
For your graphs, color the bars associated with the projection differently from the historic bars.
Part III: Report and Recommendations
Once you’ve completed each of the preceding tasks, generate a short, 1-2 page report that summarizes your observations and recommendations in looking at the results.
At a minimum, your report should include the graphs you generated from Part I and Part II and answer all of the following questions:
Generally, what patterns can be gleaned around the normal revenue and expense picture of the business? When does the business experience its peak sales? When does it experience its peak expenses?
In the base scenario, will the business remain solvent (positive cash balance) through 2025? Will it remain solvent in the optimistic case? Will it remain solvent in the pessimistic case?
How much of a spread exists between the optimistic and pessimistic projection? If the spread is large, why might this be the case? What might be causing the projection to have such divergence?
If the business was looking for early clues that it was “on track” to achieve the optimistic case in June of 2022, what metrics should it look to? What warning signs might suggest it was at risk of delivering the pessimistic case?
I will include a sample file (excel spreadsheet with multiple tabs) of what we used for forecasting formulas if the writer would like to reference how we did it in class and what the assignment objective is looking for more or less “Forecasting Formulas” begins the name of the file. APA citation style if using sources only if necessary.
0 comments