(25624) Financial Metrics and Decision Making Assignment

Assignment Task

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

Temple & Webster Group Ltd Three-way Forecast Model

Temple & Webster Group Ltd. (TPW) was founded in July 2011 and is headquartered in St. Peters, Australia. The company operates as an online retailer of furniture and homewares. It provides furniture, homewares, home décor, arts, gifts, and lifestyle products from Australian and international designers. The firm operates through two brands: Temple & Webster, and Milan Direct.

As of FY23, B2C Furniture & Homewares (the core business of the company) accounts for 85% of the total revenue. Due to its recent success, the company has expanded into the B2B and Home Improvement segment (growth plays)1 .

The future of the group looks very promising:

• Revenue is growing and becoming more diversified.
• Retention of active customers and 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.

Questions

1. REVENUE FORECAST

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

• 2024 - 496.97
• 2025 - 614.80
• 2026 - 746.03
• 2027 - 1,024.00

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.

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.

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: o “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

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

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

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.

This Accounting has been solved by our PHD Experts at My Uni Paper.