Assessment task 2
Financial Modelling Case Study (Individual)
25705 Financial Modelling and Analysis
Spring session 2024
General information and instructions
• This business case accounts for 40% of your final grade.
• You must complete this assignment on an individual basis.
• Deadline: Monday 7th October 2024, 11.59pm AEST (Sydney time)
• You need to submit via Canvas:
o An Excel workbook
.xlsx format only
• The Income Statement, Balance Sheet and Cash Flow forecast should be displayed in the Statements worksheet;
• the key outputs must be displayed in the Dashboard worksheet.
Aim to structure and format your Excel solution in the most efficient way possible and use financial modelling best practices.
• All assignment-related queries must be posted on the Canvas discussion board. E-mail queries related to the assignment will not be entertained.
From prior experience, technical difficulties often can be traced back to students trying to upload their work a few minutes prior to the deadline. Tip: do not wait until 11.55pm on the due date to submit your work via Canvas. Late submissions will receive zero credit unless a special consideration with valid reasons and documentation is filed.
Grading
• Both components (the written component in the Dashboard and the Excel workbook work) will be graded.
• The written component will 代 写25705 Financial Modelling and Analysis be marked based on the accuracy of the arguments.
• The Excel work will be marked based on the efficiency and clarity with which solutions are produced.
• Late submissions will receive zero credit unless a special consideration with valid reasons and documentation is filed.
Data
• In the Assignment page on Canvas, you will find the workbook TPW Forecast.xlsx. The
workbook contains Income Statement, Balance Sheet, and Cash Flow in millions of $ from 2018 to 2024.
• 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 (https://www.templeandwebster.com.au/) 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).
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 $116m 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:
1. DEPTH OF EVALUATION OF MODEL CHOICES AND OUTCOMES [12 marks]
a. REVENUE FORECAST
As a first step, present four revenue forecasts in the Assumptions sheet:
• Analysts forecast: following the analyst estimates provided below (in $m)
2025 2026 2027 2028
620.40 760.60 917.00 1,109.03
• Compounded growth: using a geometric mean of the 2018-2024 period.
• Exponential smoothing: using an appropriate exponential smoothing method based on the 2018-2023 period.
• Your Revenue Forecast: build a revenue forecast that reflects your views on how TPW is expected to perform. in the next four years.
After generating the Revenue forecasts, analyse their profiles, select three of them and label each selected forecast as the Base, Optimistic and Pessimistic scenario.
b. 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 2024 (Actuals). Use the Building a Financial Model.pdf as a guide of the drivers you could use for each item.
c. 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 (Financial Reports, Investor Presentations, and others).
• The financial reports show a change in how Cost of goods sold and Distribution costs
were reported until 2022 and thereafter. Please make sure you consider these changes when you do your financial analysis.
• The following items can have the same assumption across the three scenarios:
o Income Statement:
Other Operating Income,
Share of loss of an associate.
o 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 2024.
2. APPROPRIATE USE OF FINANCIAL MODELLING PROCESSES AND BEST PRACTICES IN EXCEL [12
marks]
a. 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 109 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, 23, and 24.
b. 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 – 2028 period, making sure:
o the financial statements are linked to each other.
o 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.
o 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
c. FOLLOW 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.
3. CLARITY AND EFFECTIVENESS OF THE DASHBOARD IN DISPLAYING RESULTS [8 marks]
a. BASE CHARTS
In the Dashboard worksheet, please:
• Create achart 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-2024 period (actuals), and for the 2025-2028 period (forecast). The forecast period should show one scenario at a time by using a scenarioselector.
b. 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!
4. FEASIBILITY AND JUSTIFICATION OF RECOMMENDATIONS BASED ON THE MODEL [8 marks]
In the Dashboard worksheet, please use a textbox to (in 500 word or less):
• discuss what are the main Revenue growth drivers forTPW?
• highlight the 3 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 expenses
RESOURCES
Here are some sources. Please use as many additional trustworthy sources as you judge convenient:
• Access the company’s annual reports and investor presentations in Facset
• IBIS World TPW profile
• Sector reports
o Online Household Furniture Sales in Australia
o Online Home Furnishing Sales in Australia
标签：2024,use,Financial,Modelling,forecast,TPW,Analysis,financial,your From： https://www.cnblogs.com/WX-codinghelp/p/18427649