Financial Modelling Assessment Case study Outdoor Leisure

The takeover company Outdoor Leisure (OL) is an Australian company that owns a diverse portfolio of adventure and leisure assets in Queensland (Qld) and New South Wales (NSW). OL is owned by the Blyth Family.

The company’s chief executive officer (CEO) is Annika Blyth, and her husband Roland Blyth is the chief financial officer (CFO).

OL owns the following adventure and leisure facilities:

1. Wahoo World: located on the Sunshine Coast in Qld, this theme park caters to families.
2. White Mountain Resort: located in the NSW Snowy Mountains, this resort offers snow sport activities between the start of June and the end of September each year, and bushwalking and hiking during the other months.
3. Snow Cap Resort: also located in the NSW Snowy Mountains, this winter-only resort is open from the start of June to the end of September each year.

OL is concerned about the future operations of the White Mountain Resort and Snow Cap Resort. With climate change affecting winter resort operations and the viable period for snow sport activities shortening, Annika and Roland are looking to further diversify OL’s assets and increase the resorts’ use year-round. Annika has recently learned that the existing infrastructure at both resorts would suit mountain biking in the warmer months, opening opportunity for OL to better use its NSW assets.

The acquisition of Bike Lane Collective Bike Lane Collective (BLC) is a road and mountain bike adventure company that was founded by Mike and Sarah Askew in 2010. It operates in the NSW Snowy Mountains area year-round and specialises in single-day and multi-day mountain bike adventures. It also has a bike-hire facility for participants who do not own their own equipment. Mike and Sarah are looking to sell their business, including all properties and assets, and retire to a warmer climate.

BLC’s business model

BLC operates out of Jindabyne NSW, with a shopfront and back-office operations in the town centre.

BLC offers the following services:

• mountain bike hire
• guided/structured single-day and multi-day mountain bike adventures
• road bike hire for self-guided rides.

BLC owns the shopfront and back-office premises it operates from, as well as a warehouse in the industrial area four kilometres away, which houses additional equipment and a mechanics workshop. Biking is seasonal in the area due to the colder winter months and snow fall, which can make the primary mountain biking activities difficult. Customers can hire their equipment and book the guided mountain bike adventures online or in store. The shopfront holds limited inventory for sale – mainly helmets, gloves and sports drinks. The goal Annika is focused on improving the use of OL’s White Mountain and Snow Cap resort assets in NSW and making both resorts year-round destinations for outdoor enthusiasts. She is also keen to grow OL through diversification and acquisition of businesses that complement the current portfolio of assets and operations. Annika is an avid mountain biker and has long wanted to include mountain biking at the NSW resorts, where the existing lift infrastructure can be used to transport riders and their bikes to the top of the mountain. Annika became aware of Mike and Sarah’s intention to sell BLC at a recent Chamber of Commerce networking event. Acquisition process OL and BLC signed a confidentiality agreement in December 2024 to start the acquisition process. Roland has engaged a sports consulting firm, Winning Solutions, to independently assess the financial viability of the BLC purchase and to produce a model of the five-year view of financial performance.

To help OL and Winning Solutions with their analysis, BLC has provided the following data:

• An income statement for the previous three financial years, 2022–2024, extracted from BLC’s management accounts (in the ‘Data Inputs’ tab of the financial model).
• An extract of sales, cost of sales and working capital data for BLC’s products (in the ‘Data Inputs’ tab of the financial model).
• Notes from Mike and Sarah describing BLC’s business operations and products (in Appendix 1 of this document).

Winning Solutions have used Mike and Sarah’s data to produce a business valuation, alongside a projected net profit position. Your role You are a management accountant working for OL’s finance team and you are considered an expert financial modeller. Your role is to build financial models so OL can produce accurate analysis of past results and model the financial plan and budget going forward. Note: The financial year end is 30 June. All dollar values are in Australian dollars. Ignore the impact of GST and indirect taxes.

Task 1: Review the financial model (24 marks)

Roland has given you a copy of the financial model built by Winning Solutions and has asked you to review it. In an accompanying email, he notes the following:

To:
Management Accountant From:
Roland Blyth, CFO Subject:

Bike Lane Collective business valuation – Financial model review

Hi,
As discussed, Annika and I have been canvassing an opportunity to purchase the business Bike Lane Collective, which is located near our NSW resort operations. BLC is a well-established bike hire business and mountain bike tour operator in Jindabyne. We feel this business will complement our existing operations at White Mountain Resort and Snow Cap Resort and will allow us to better use the infrastructure at both resorts, year-round. BLC is a profitable business and has the potential to expand within the current market given the rising popularity of biking, but this will require OL to invest a significant sum into upgrading some of our facilities. We intend to repurpose existing sheds at both White Mountain Resort and Snow Cap Resort for bike hire in the spring, summer and autumn months, and to install bike mechanic workshops at each site. This will require a capital investment of around $800,000. We engaged a specialist sports consultancy firm, Winning Solutions, a few weeks back to assist with the due diligence process. As part of their analysis, they have created the attached financial model using background information and financial data from BLC. Based on this model, the net present value (NPV) is $7.2 million (rounded) and the EBITDA valuation range is from $4.6 million to $5.8 million (rounded). While inflation is expected to affect all cost categories, OL anticipates the most significant increases will be in drinks and snacks, with costs projected to rise by 6% per annum.

Could you please review the model and let me know your thoughts over the next few days?

The valuation will form the basis of our offer, so I need to know that model assumptions are realistic and error-free. I’ve completed a partial review of the model and identified the following four issues:

• Assumptions worksheet – Cells $I$39:$M$39: Capital expenditure – Other. The assumption is that spend will be $25,000 per year. Maintaining the same spend each year appears unrealistic for a business that OL intends to grow through this acquisition, particularly given its plans to invest in capital expenditure.
• Assumptions worksheet – Cells $I$28:$M$28: COS merchandise – Drinks/snacks. The assumption is that there is no cost of sales (COS) price growth in any future year; COS prices remain constant from Year 1 to Year 5. This is unrealistic given that there has been price growth in previous years and OL’s future expectations.
• Calculations worksheet – Cells $D$102 and $D$103. These cells contain hard-coded numbers, which result in inconsistent formula across the two rows.
• Appendix worksheet – Cell $F$10. This cell contains a hard-coded number ‘0’, which prevents it from completing a cross-check between worksheets to ensure consistency in calculations.

Some notes on the preparation of the model:

• OL plans to invest $1.64 million in refreshing the current fleet of mountain and road bikes over the next five years. This will also increase the number of bikes available for hire, as we expand the capacity of our guided mountain bike tours.
• Winning Solutions has used a WACC of 15%. Thanks and regards, Roland Required Review the financial model (‘Financial Model – Task 1’), which was prepared by Winning Solutions to value BLC’s business, along with the notes from Mike and Sarah Askew in Appendix 1.

(a) For each of the four (4) issues identified by CFO Roland Blyth in his email:

i. Recommend an action to resolve the issue. (4 marks)

ii. Identify the potential impact of the issue on the EBITDA valuation. (4 marks)

(b) Considering the model’s structure, logic and integrity:

i. Identify and explain four (4) additional issues, clearly referencing the specific tab and cell(s) that relate to each issue. Assume the data in the data inputs tab are correct. Each issue must be unique in nature. If the same issue (e.g. hardcoding) appears in multiple cells or across different tabs, report it only once. Ignore any formatting issues. (8 marks)

ii. Recommend an action to resolve each issue from (b)(i). (4 marks)

iii. Identify the potential impact of each issue from (b)(i) on the EBITDA valuation. (4 marks)

24 marks

Present your answers to tasks (a) and (b) in a table format.

You are not required to change the financial model to correct any issues you identify, quantify the potential impact on the EBITDA valuation, or submit a financial model for this task.

Task 2: Build a three-way forecast model (56 marks)

After reading your reply to his email, Roland has asked you to build a three-way forecast model for the years 2025– 2029 (Year 1 to Year 5). OL will then use your three-way forecast model to make a more accurate calculation of BLC’s net present value (NPV). Meanwhile, as part of the due diligence investigation, Mike and Sarah Askew have provided the following information from BLC (actuals for 2022–2024; budget for 2025):

• Extract of management accounts with annual income statements and balance sheets
• Details of sales for mountain bike hire, road bike hire, guided tours, and merchandise
• Details of cost of sales
• Overview of full-time equivalent (FTE) staff and salaries This information is provided in the Excel file Financial Model Template – Task 2, which was supplied with this assessment to help you prepare the 2025–2029 forecast.

You should use the approved 2025 budget as Year 1 of your forecast. All information for 2025 budget and prior years (2022–2024) is available in the Excel file. Required All assumptions are provided in the Excel file under a tab called ‘Assumptions’. In this task, you don’t need to change any of the assumptions.

For the three-way forecast model for Year 2 to Year 5:

• Build the calculation components as outlined in task (a).
• Build the output components as outlined in task (b).
• Complete an NPV sensitivity analysis as outlined in task (c).

You need to complete these build requirements using the Excel file provided for this task by filling in the grey cells and submit your completed Excel file for Task 2.

Calculation components

(a) Build the following seven (7) components in the Excel file provided:

i. An ‘F Sales & COS’ tab with a detailed sales forecast that covers: (i) sales volumes,
(ii) sales price per unit,
(iii) cost of sales per unit,
(iv) sales revenue,
(v) cost of sales and
(vi) gross profit.

The forecast must provide insights at the product level (i.e. mountain bike hire, road bike hire, guided tours, annual passes and merchandise). (7 marks)

ii. An ‘F Salary expenses’ tab with a forecast of:
(i) full-time equivalent (FTE) staff and
(ii) salary expenses, including on-costs. Separate calculations must be included for current staff and incremental/ decremental FTEs.

Both must be broken down to the level of the organisation unit (i.e. overhead staff, sales staff, operational staff and technical staff). (6 marks)

iii. An ‘F PPE’ tab with projected:
(i) capital expenditure PPE (property, plant, and equipment),
(ii) PPE opening balances (o/bal) and closing balances (c/bal) and
(iii) annual depreciation of PPE for both categories of PPE. (3 marks)

iv. An ‘F Expenses’ tab with projected expenses, including:
(i) marketing,
(ii) office expenses and
(iii) other expenses. (3 marks)

v. An ‘F Loan facility’ tab with projected:
(i) opening balance and closing balance loan balances for current and non-current liabilities,
(ii) correct application of assumptions and
(iii) calculation of interest expense. (3 marks)

vi. An ‘F Working cap’ tab with a forecast of:
(i) inventory,
(ii) trade debtors and
(iii) trade creditors. (6 marks)

vii. A ‘Control accts’ tab with control accounts, including:
(i) working capital (trade debtors, trade creditors, inventory),
(ii) PPE,
(iii) annual pass revenue – contract liability and contract fulfilment asset,
(iv) salary payable and
(v) tax payable. Each control account should reconcile with the corresponding balance sheet line, and evidence should be presented that the control accounts reconcile (e.g. by conditional red/green formatting). (8 marks)

Output components

(b) Prepare a three-way forecast that includes:

i. An income statement in the ‘F IS’ tab (4 marks)

ii. A balance sheet in the ‘F BS’ tab (4 marks)

iii. A cash flow (CF) statement in the ‘F CF’ tab, broken down into operating CF, investing CF and financing CF (8 marks)

NPV sensitivity analysis Roland would like to know the impact of changing the discount rate and growth rate on BLC’s business value. To assist you with the NPV calculation, rows 11 to 16 have been pre-populated with the correct formulas, which will automatically calculate once you complete the free cash flows. To build the data table, define data points for the discount rate in a range between 13.5% and 16.5% with midpoint 15% and step size ±0.5%. The growth rate is defined in a range between 1.25% and 2.75% with midpoint 2% and step size ±0.25%.

(c) Using the format in the ‘NPV sensitivity analysis’ tab, build the ‘NPV sensitivity analysis’ by completing the NPV calculation of BLC’s business value and the sensitivity analysis data table using the parameters provided. (4 marks)

56 marks

Task 3: Demonstrate the financial model (20 marks)

OL’s management team has asked you to present the workings of your financial model in a short video presentation.

Required

Create a video of no more than three (3) minutes, presenting your financial model from Task 2 to OL’s management team.

In your presentation:

(a) Explain briefly the structure of the financial model, including the inputs, calculations and outputs.
(b) Advise OL on an appropriate purchase price range to consider offering for BLC, based on the NPV sensitivity analysis. Task 3 is a demonstration of the model only. You do not need to prepare a written presentation or submit anything other than a video for Task 3.

Appendix 1: Notes on BLC’s operations from Mike and Sarah Askew Products BLC offers three main products to the biking community:

Product Notes Mountain bike hire Available all year, bikes may be booked online or in store and collected from the shopfront in Jindabyne. The mountain bikes are cleaned after every booking and serviced at least once a fortnight. The hire facility is popular with locals and tourists. Road bike hire Available all year, bikes may be booked online or in store and collected from the shopfront in Jindabyne. Road bike hire is popular during cycling festivals that are held in summer and autumn each year, with many riders hiring a bike rather than transporting their own equipment. Guided mountain bike tours Guided/structured single-day and multi-day mountain bike adventures are offered for adults and children. Tours operate from the start of October to the end of May each year. They take place weekly on Fridays, Saturdays and Sundays and every day during school holidays. Guided tours may be arranged at other times by school and community groups but require a minimum of 15 riders to book. The tours are popular with families and provide appropriate trails for beginners, intermediate and advanced riders. Additional notes Area Notes Overheads This organisational unit includes the co-CEOs Mike and Sarah Askew, CFO James Scott and Operations Manager Dale Jenson. Sales The sales team consists of three sales managers who manage the shopfront in Jindabyne. Several customer experience specialists perform various front-of-house and back-of-house functions, including handling online and in-store orders, and ensuring customers are correctly fitted for their equipment. All customer experience specialists are experienced mountain bike or road bike riders and the team tries to have one specialist for each product in the shop on each shift. Operations The operations team oversees and delivers the guided tours. All biking guides are experienced mountain bikers with first aid and navigation qualifications. Technical The technical team looks after the servicing and maintenance of the equipment. It ensures that any unsafe equipment is removed from the stock immediately. The team consists of qualified mechanics and two apprentices.