KPMG Enhance Programme Financial Modelling Module ACC2010 2020/21 Resit

Background

You work in a corporate finance department.

Your client, Mr Reilly, is setting up a company, Dolls Limited, to manufacture dolls that are based on different professions. Mr Reilly has identified a factory which would be suitable for the company, however he has not yet decided whether to lease or buy the factory. He has already been in discussions with a bank regarding a loan to part fund the purchase of manufacturing equipment and the fit out of the factory. The bank has now provided indicative terms for a larger loan to fund the factory purchase also.

Mr Reilly would like a financial model developed that can compare the financial impact of leasing or buying the factory, to assist his decision making. He would also like to use this model to support his fundraising application to the bank.

Mr Reilly has asked for your advice on whether he should lease or buy the factory, and whether you think the bank would be willing to lend him the extra money to buy the factory.

Requirements

1. Develop a financial model to produce financial projections for the proposed company to support the decision making and fundraising processes (60% of available marks).

The model must include:

• Functionality to input assumptions for three scenarios:

• Scenario 1: Lease Factory
• Scenario 2: Buy Factory Base Case
• Scenario 3: Buy Factory Bank Case

Scenarios 1 and 2 reflect Mr Reillys expectations for the future performance of the company, whereas Scenario 3 reflects the assumptions which the bank has advised it will use when deciding whether to lend.

• Calculations on a quarterly basis for 15 years;
• Integrated financial statement outputs on a quarterly and annual basis;
• A summary output sheet showing (for the scenario in use):

• Key assumptions;
• Key outputs including:
  • Minimum and average Debt Service Cover Ratio (DSCR);
  • Project IRR and Equity IRR; and
  • Summary financial statements on an annual basis;
• Charts to illustrate trends in key figures/metrics.

2. Prepare a report of no more than 2,000 words.
   • Whether Mr Reilly should buy or lease the factory. Your analysis should include a comparison of the results of Scenarios 1 and 2, along with consideration of any other relevant financial factors not reflected in the model and any non-financial factors which you think are relevant to the decision.
   • Whether the bank is likely to lend towards the purchase of the factory. Your analysis should consider the results of Scenario 3 and should include a short explanation of why the forecasting assumptions used by the bank when deciding whether to lend differ from those used by Mr Reilly for his internal decision making.

     Model Assumptions

     The client has provided the following assumptions for use in the model.

     Timing

   • Projections start on 1^st July 2021.
   • Operations phase starts on 1^st October 2021 (no revenue or operating costs before this date).
   • Operations phase runs to the end of the projections.

   • Capital Expenditure

   • Capital expenditure can be allocated to 3 categories of non-current assets:
     • Buildings (useful life 50 years);
     • Plant & Machinery (useful life 7 years);
     • Fixtures & Fittings (useful life 10 years).
   • Buildings: In Scenarios 2 and 3, the factory is purchased for 2.5m on 1^st July 2021.
   • Plant & Machinery: The equipment for the factory is purchased for 2m on 1^st July 2021.
   • Fixtures & Fittings: The fit out of the factory costs 0.5m and takes three months to complete, however it is paid upfront on 1^st July 2021.
   • Assets are not depreciated prior to the operations phase. During the operations phase, assets are depreciated on a straight-line basis over their useful life.

   • Financing

   • In Scenario 1, capital expenditure is assumed to be financed:
     • 75% by debt; and
     • 25% by equity.
   • In Scenarios 2 and 3, capital expenditure is assumed to be financed:
     • 65% by debt; and
     • 35% by equity.
   • Debt terms:
     • Prior to the operations phase, interest is paid monthly (funded by additional equity investment);
     • During operations, debt is repaid semi-annually on an annuity basis (capital and interest);
     • DSCR calculated in each debt repayment period on a 12-month look-back basis;
     • Distribution lock up DSCR is 1.10 (see footnote [1]).
     • In Scenario 1:
       • 4.5% annual interest rate (simple interest rate);
       • Debt repaid over 6 years.
     • In Scenarios 2 and 3:
       • 5% annual interest rate (simple interest rate);
       • Debt repaid over 12 years.
   • Equity terms:
     • Equity investment is treated as share equity;
     • Dividends will be declared annually in the final quarter of the companys financial year (year ending 30^th June), based on available profits and cash;
     • Dividends will be paid the quarter after being declared. Revenue
   • There will be 3 revenue streams:
     • Product 1 (doctor doll)
     • Product 2 (vet doll)
     • Product 3 (astronaut doll)
   • The sales prices per unit (at 1^st October 2020 price base date) are:
     • Product 1 (doctor doll): 8
     • Product 2 (vet doll): 10
     • Product 3 (astronaut doll): 12
   • Sales prices are forecast to increase on an annual basis (on 1^st July each year) by 2% per annum.
   • Annual sales volumes for Scenarios 1 and 2 are:
     • Product 1 (doctor doll): 750,000 units
     • Product 2 (vet doll): 650,000 units
     • Product 3 (astronaut doll): 650,000 units
   • Annual sales volumes are 5% lower in Scenario 3.
   • All three products are subject to the following seasonality each financial year:
     • 1^st July to 30^th September: 30%
     • 1^st October to 31^st December: 34%
     • 1^st January to 31^st March: 23%
     • 1^st April to 30^th June: 13% Cost of Sales
   • Cost of sales is calculated based on the following gross profit margins:
     • In Scenarios 1 and 2, gross profit margin of 15%;
     • In Scenario 3, gross profit margin of 14%.

   • Operating Costs

   • There will be 3 operating cost categories:
     • Distribution costs;
     • Factory lease (see footnote [2]); and
     • Administrative expenses.
   • Operating costs are assumed not to be subject to seasonality.
   • Forecast annual operating costs (at price base date 1^st October 2020) are:
     • Distribution costs: 1,750,000
     • Factory lease: 225,000 (Scenario 1 only)
     • Administrative expenses: 500,000
   • Operating cost prices are forecast to increase on a quarterly basis, based on an annual indexation rate of 2.5%.

   • Working Capital

   • Trade receivables are assumed to be received after one month;
   • Trade payables are assumed to be paid after one month.

   • Corporation Tax

   • Corporation tax rate of 19%;
   • Tax paid 9 months after the year end.

   • Other

   • Deposit interest rate of 0% per annum (simple interest rate);
   • Overdraft interest rate of 5% per annum (simple interest rate).

   • [1] The model does not need to lock up distributions to equity if the Distribution lock up DSCR is breached, but should highlight if this limit is breached.

     [2] For modelling purposes, the factory lease is assumed to be expensed to the Income Statement in the period incurred.