Financial Forecasting and Analysis for TPW: Excel Solution and Dashboard

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 period.
• 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 [2 marks]

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 2023. 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 associate.
  • 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 2023.

QUESTION 5. COMPLETE THE THREE-WAY FORECAST

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

• Make sure:
  • the financial statements are linked to each other.
  • The Balance Sheet balances.
    • 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 sheet. You can use
    the same calculations in the Statements sheet for the 2024-2027 period.

QUESTION 6. OUTPUTS

Base Charts Component

In the Dashboard worksheet, please:

• Create a chart to show historical Revenue and the 3 Revenue forecasts at once.\
• 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.

Base Written Component

In the Dashboard worksheet, please:

• Refer to the first chart above:
  • Use a text box to justify your choice of scenario (Base, Optimistic, and Pessimistic)
    for each forecast.
  • Is there an alternative forecast that should have been included? What are the main
    Revenue growth drivers for TPW?
• Refer to the second chart above:
  • Use a text box to:
    • highlight the 2 most relevant findings in your historical analysis and briefly
      explain what could have caused them. Is TPW profitable?
    • justify your choice of assumptions for: COGS, Distribution, and Advertising &
      Marketing

WOW factor

• add charts / tables / functionalities you think will help the user better understand the
  company and your forecast.
  Note: keep the Dashboard to one page.
  Tip: browse for dashboard templates and videos. Get creative!

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.

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back!
Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects. Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank