Analysts forecast : following the analyst estimates provided below (in $m) Compounded growth :

/ Uncategorized / By /

Assignment Task

Question 1. Revenue Forecast

As a first step, present three revenue forecasts in the Assumptions sheet:

  • Analysts forecast : following the analyst estimates provided below (in $m)
  • Compounded growth : using a geometric mean of the 2018-2023
  • Exponential smoothing : using an appropriate exponential smoothing method based on the 2018-2023 period.

After generating the three Revenue forecasts, analyse their profiles and label each forecast as the Base, Optimistic and Pessimistic scenario.

Question 2. Historical Financial Statements 

To start laying out the Statements sheet, please use direct links to reference all historical values in the Income Statement, Balance Sheet, and Cash Flow from the Inputs worksheet.

Please note, the Check in row 104 of the Inputs sheet is off in FY19 and FY20. This is because of issues with the historical data. The issue does not affect FYs 21, 22 and 23.

Question 3. Historical Financial Analysis

Please use ratios in the Assumptions sheet to perform a financial analysis of items in the Income Statement and Balance Sheet for the period June 2018 – June 2023 (Actuals). Use the Building a Financial Model.pdf as a guide of the drivers you could use for each item.

Question 4. Forecast Assumptions

For each of the relevant financial ratios and other inputs, create 3 sets of assumptions (Base, Optimistic, and Pessimistic) for your forecast.

  • Base your choice of values in your historical financial analysis, your expectations about TPW’s future expected performance in each scenario, as well as any additional information you can access in the company’s documents (TPW Financial Report 2023.pdf and TPW Investor Presentation 2023.pdf).
  • Here is an important note from the financial report:
    • “Cost of goods sold also includes the shipping costs (Distribution) incurred on delivery of products to customers of $55,726,000 (2022: $56,850,000). This was a change in presentation during the year ended 30 June The comparative prior year balances were also updated in line with this change in presentation.”
  • The following items can have the same assumption across the three scenarios:
    • Income Statement:
      • Other Operating Income,
      • Share of loss of an
    • Balance Sheet:
      • Deferred Tax Assets,
      • Other Assets,
      • Provision for Risks & Charges,
      • Common Stock Par/Carry Value,
      • Cumulative Translation Adjustment/Unrealized For. Exch. Gain. These balance sheet items can maintain the same values they show in

Question 5. Complete The Three-Way Forecast

In the Statements worksheet, use the Revenue forecast and the Assumptions to complete the three- way forecast for the 2024 – 2027 period.

  • Make sure:
    • the financial statements are linked to each
    • The Balance Sheet
      • Given the solid Cash position of TPW, you can use Cash and ST Investments as the source of any financing the company may need in upcoming years.
    • Calculations for the Cash Flow Actuals are provided in the Inputs You can use the same calculations in the Statements sheet for the 2024-2027 period.

Question 6. Outputs

In the Dashboard worksheet, please:

  • Create a chart to show historical Revenue and the 3 Revenue forecasts at
  • Create a chart to show a breakdown of costs and expenses as a proportion of Revenue both for the 2018-2023 period (actuals), and for the 2024-2027 period (forecast). The forecast period should show one scenario at a time by using a scenario selector.

Question 7. Best Practices

Aim to structure and format your Excel solution in the most efficient way possible and use financial modelling best practices. Please refer to the Week 2 material.