Apr 19, 2024

Data

• In the Assignment page on Canvas, you will find the workbook TPW Forecast.xlsx. The workbook contains the Income Statement, Balance Sheet, and Cash Flow in millions of \$ from 2018 to 2023.
• Perform your calculations based on the data in these worksheets.
• Search for additional data as required.

The future of the group looks very promising:

• Revenue is growing and becoming more diversified.
• Retention of active customers and the proportion of repeating customers show this online retailer is not only a Covid phenomenon.
• A strong cash position \$105m and \$0 Debt give TPW flexibility to focus on its growth. In this context, please use the TPW Forecast.xlsx workbook to create a three-way forecast in three scenarios (Base, Optimistic, Pessimistic) that includes:

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.

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.

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.

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.

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:

The 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: o 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.

5. Outputs

Base Charts Component

• 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,

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?

6. Best Practices

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

Recent Post