Strategic Financial Modelling Consultants Specializes in Strategic Joint Venture (JV) Projects

Background

Strategic Financial Modelling Consultants ‘SPMC’ specialises in strategic Joint Venture (JV) projects across UK cities, for a share of Profit or Loss in each JV. The firm partners with new entrepreneurs, who are interested in franchising products of major international brands over a six (6) year period only. Thereafter; SPMC must divest, without any payment due/received. 

A typical partnership deal requires SPMC to develop a strategies business plan for the franchisee, secure funding, contribute to strategic management decisions, undertake risk management, tax-planning and regulatory compliance. In April 2020, several potential franchisees approached SPMC for Joint venture deals across UK cities. 

A schedule of prospective JV Partners is presented in the accompanying Spreadsheet Template, used by the SPMC Financial Analyst, to evaluate each proposal. SPMC scores and ranks proposals according to a strict Benchmarks/Thresholds, which are the firm’s Key Performance Indicators (KPIs). 

Benchmarks/Thresholds?Each Project should meet every criterion below (Status Options: ‘Achieved’ or ‘Failed’)?(a) Net Present Value (NPV) of £500,000 from the investment (i.e. to be ‘Achieved’)?(b) Net Present Value (NPV) of 124.32% (as ROCE), from the investment (to be ‘Achieved’) (c) Net Present Value (NPV) of 22.00% (as Average Annual Return) (i.e. to be ‘Achieved’) (d) Internal Rate of Return (IRR) of 92.45% from the investment (i.e. to be ‘Achieved’).

Investment Portfolio ?Strategic Financial Modelling Consultants can raise Five Hundred Thousand Pounds (£500,000) from Sterling Bank PLC, to invest in promising JVs. The Initial Investment required and Cost of Capital for each JV differs, as detailed in the Schedule of Prospective JV Partners in the Spreadsheet. No additional funds can be secured by SPMC for such JVs. ?

 Market Analysis/Data: 
.    (a)  Projected Market Size: Specific details have been provided in the accompanying Spreadsheet Template. These are the most optimistic estimates. ?
.    (b)  Projected Market Growth rate: Details of estimated increase (by location) are provided in the accompanying Spreadsheet Template. ?
.    (c)  Projected Market Share (Brand Rivals): Each Franchisee will control the entire market space in each location but new franchises will be granted by rival brand competitors. For each new competitor entering the market in each location, a percent of market share will be lost per year. Details (by location) are provided in the Spreadsheet Template, including the related probability that new competitors will enter each market each year. ?
.    (d)  Projected Selling Price: Details of estimate increases (by location) are provided in the accompanying Spreadsheet Template. ?
.    (e)  Projected Costs: Details of estimate increases (by location) are provided in the accompanying Spreadsheet Template. ?
 
 Assumptions:?

Benchmarks/Thresholds

(a) All transactions and estimates are assumed to be real cash flows.

?(b) All cash flows occur evenly throughout each year and over six (6) years only.

?(c) All estimates were derived, after detailed market research, by SPMC’s Research Team.

(d) Each Project has a six (6) years lifespan, during which the fixed cost remains unchanged .

Your Tasks: 
You are a Financial Analyst at Strategic Financial Modelling Consultants, responsible for evaluating projects. Your analysis must be concluded for distribution to your other Team Members, within three (3) hours of receiving ‘Market Analysis/Data’ from the SPMC Market Intelligence Team. You must also deliver your Analytical Report to Sterling Bank PLC for appraisal and approval within 7 days thereafter. To evaluate each project’s financial viability and suitability for inclusion into SPMC’s Investment Portfolio over six (6) years; you should: 
1.    Create a financial model for each investment opportunity, using MS Excel [10 Marks] ?
2.    Using MS Excel, simulate each proposal (include these worksheets) [40 Marks] 
.    (a)  A ‘+Scenario Manager’ for each location (use details provided above) ?[15 Marks] ?
.    (b)  A ‘+Goal Seek’ for each location that seeks to achieve the benchmark/threshold NPV of £1,000,000 NPV only, by determining how many products need to be sold in each location to meet this target. (i.e. 2 marks per location) ?
(c) A ‘+ScenarioSummary’ for all locations 

(a) Determine the Correlation between the: ‘Investment Capital’, ‘Net Present Value (NPV) - Amount (£)’, and ‘Total Market Size for Product (Year 1)‘ for each of the following locations [Template contains a Sheet Titled ‘+Correlation’ for this purpose]Oxford and Brighton Franchises London and Bangor Franchises Reading and Glasgow?London and Reading 

Use MS Word to prepare an Analytical Report for review by Sterling Bank Your Analytical Report should include the followings: 
(a)  Discussion of uncertain economic/financial factors to be considered ?[05 Marks]
(b)  Discuss limitations of MS Excel that can better resolve ?( Please mention this: especially when you look at the no. of potential customers enter each year, the excel shows the number with decimal which is weakness of excel when compare with @Risk software. Another thing is when you do scenario manager, you have to add each city manually into scenario manager and which can lead to error and time consuming). [05 Marks] 

(c)  Explanation your observations from your analysis (by location) ?[10 Marks]
(d)  Explanation of your outcomes/results obtained from your    evaluation ?[10 Marks]
(e)  Justification for your location selection/non-selection ?[20 Marks]