Please login here to save this document to a list.
If you don't have an account, you can register for free here.
BENEFITS OF DOCUMENT
This DCF model presents the impact of store expansion strategies of a business over a 5-year period.
The first strategy consists of a gradual expansion by using debt and equity financing, as well as cash generated from operations.
The second strategy consist of a faster expansion of the business by getting financed from a Fund and repaying this liability with a percentage of the revenues of the expanded business, additionally to optimize cash flows the business sells and lease back its current assets and repurchase them at the end of the deal with the Fund.
For each of these entities: the business with organic growth, the business with revenue-based financing, and the Fund, the model generates the three financial statements, calculates the relevant metrics, and performs a valuation for all entities. All this is supplemented with graphs, KPIs and a summary.
So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.
On the manual tab you can feed the general information for the model such as: project title, responsible, timeline of the model and date and currency conventions.
Additionally, there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call ups (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.
There is also color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents, and checks).
There are 2 main assumptions tabs for each strategy. The assumptions for the organic growth of the business are located in the tab "Organic_Inputs", while the assumptions for the revenue-based financing of the business are located in the tab. "".
Let's start by populating the tab related to the organic growth of the business. The first section consists of the business operations and more precisely to the revenues sub section. There the user needs to fill the items sold per location, and the price per item sold, so that the revenues are calculated.
Then the user must set the cost of goods sold as a percentage margin on revenues.
Afterwards, we move to the Operating Expenses, which is split into 2 parts:
• Labor Costs: the user must set the labor costs (headcount & salary) for the main store (the already existing store before the expansion), after that for each new location the user must set the percentage labor cost versus the initial location. So, if the initial store labor cost is 100, and the user sets for location 1 to be 90%, the labor costs for location 1 would be 90. Also note that the CEO and CFO costs are split between venues equally.
• Sales, General, & Administrative Costs (SG&A): the user must set SG&A for the main store (the already existing store before the expansion), after that for each new location the user must set the percentage SG&A cost versus the initial location.
• So, if the initial store SG&A cost is 100, and the user sets for location 1 to be 90%, the SG&A costs for location 1 would be 90.
The next sections ("Existing Assets" & "New Assets") allows the user to set the total existing assets and new assets (acquisition costs less depreciation), and the depreciation years. Additionally, the user can set the maintenance capex as a percentage of sales.
Moving to the "Financing" section, the user needs to set the following debt / equity schedules:
• Existing Debt Financing: the user sets the opening balance, the annual interest rate, and the repayments per year:
• New Assets – Central Stores Financing: the initial investment is called up from the Assets sections, and the user needs to set the annual interest rate, the maturity in years, and the debt gearing:
• Overdraft Financing: the user just sets the annual interest rate, and the beginning cash balance.
• Share Capital and Retained Earnings: the only input needed here from the user is any additional equity injection, the equity from the new assets' investment is brought up automatically.
• Finally in the Discount Rate section the user needs to set the cost of equity and any premiums to calculate the Weighted Average Cost of Capital.
Moving to the working capital section, the user needs to set the receivables, inventory, and payables' days, as well as the opening balance of each item.
In the tax section, the user just needs to set the tax rate for corporate income. Additionally, the user can also set any tax payable from previous periods.
Now moving to "" tab, few assumptions need to be changed. The user first needs to set the faster expansion of the business by adjusting the year when the new stores are expected to be opened.
Another input that needs to be changed is the date when the existing assets are sold, and leaseback occurs. The user also sets the year of asset buyback from the business.
Finally, regarding the revenue-based financing, the user needs to set in the relevant section the percentage of revenues to be shared with the fund, and the maximum revenues that will be shared with the fund across all years (Revenue Based Repayment Capped).
All other assumptions have been directly retrieved as called up links from the organic growth assumptions' tab, so that the 2 cases can be compared with each other. Of course, since in this tab we are focusing on the revenue-based financing, the debt and equity financing have been deactivated.
Now based on the assumptions we have detailed earlier, the financial statements are produced for the Organic Growth, the Revenue-Based Financing, and the Fund, as presented below.
In the Fund Financial Statements, the user must set some minor assumptions such as cost of equity, cost of sales as a percentage of sales, various operating expenses charges, and a corporate tax income rate.
Moving to the valuations tabs ("Val – Organic", "Val – ", "Fund FS & Metrics"), a valuation is performed along with investment metrics such as enterprise value, equity value, net present value, internal rate of return, cash on cash multiple, and payback period.
Moving on to the next tab, a set of graphs that compares the main metrics from the organic growth and the revenue-based growth.
Finally, in the last tab, various checks are performed and, or are aggregated here from the various worksheets. This tab ensures that everything in this model is working as it should.
All the above are summarized in the "Executive Summary" which displays the most important parts of the model.
Got a question about the product? Email us at email@example.com or ask the author directly by using the "Ask the Author a Question" form. If you cannot view the preview above this document description, go here to view the large preview instead.
Source: Best Practices in Integrated Financial Model, Retail Strategy Excel: Store Expansion: 5 Year DCF Fin. Model with Impact Analysis Excel (XLSX) Spreadsheet, Big4WallStreet
ABOUT THE AUTHOR: BIG4WALLSTREET
We help small and midsize businesses' leaders with their strategic problems by creating or improving financial models, implementing tools to drive efficiency, performance, and better decision making.
With over a decade of experience in Financial & Business Modeling / Planning, Performance & Analysis, we have developed strong business understanding across diverse industries (Energy, Shipping, Manufacturing, Retail, Oil & Gas, Infrastructure, Food & Beverages, Agriculture, Healthcare, Telecoms, Real Estate, Construction & Hospitality), as well as strong capabilities across a range of financial modeling solutions.
We are one of the few financial modelers to be certified from both the Corporate Finance Institute (Financial Modeling & Valuation Analyst, Business Intelligence and Data Analyst), and the Financial Modeling Institute (Advanced Financial Modeler, Certified Financial Modeler). We build integrity and trust by providing high-quality tailor-made solutions to our clients.
Furthermore all these models can be tailor made upon your request in order to meet any special requirements for your projects should you not find something that suits you.
Big4WallStreet has published 48 additional documents on Flevy.
|Download our free compilation of 50+ Strategy & Transformation slides and templates. Frameworks include McKinsey 7-S Strategy Model, Balanced Scorecard, Disruptive Innovation, BCG Experience Curve, and many more. To receive this free download, enter your email address below and click the "Email Me" button.|
"I have used Flevy services for a number of years and have never, ever been disappointed. As a matter of fact, David and his team continue, time after time, to impress me with their willingness to assist and in the real sense of the word. I have concluded in fact that it is not at all just a repository of documents/resources but, in the way that David and his team manage the firm, it is like dealing with consultants always ready to assist, advise and direct you to what you really need, and they always get it right.
I am an international hospitality accomplished senior executive who has worked and lived during the past 35 years in 23 countries in 5 continents and I can humbly say that I know what customer service is, trust me.
Aside from the great and professional service that Flevy's team provide, their wide variety of material is of utmost great quality, professionally put together and most current.
Well done Flevy, keep up the great work and I look forward to continue working with you in the future and to recommend you to a variety of colleagues around the world."
– Roberto Pelliccia, Senior Executive in International Hospitality
"FlevyPro has been a brilliant resource for me, as an independent growth consultant, to access a vast knowledge bank of presentations to support my work with clients. In terms of RoI, the value I received from the very first presentation I downloaded paid for my subscription many times over! The quality of the decks available allows me to punch way above my weight – it's like having the resources of a Big 4 consultancy at your fingertips at a microscopic fraction of the overhead."
– Roderick Cameron, Founding Partner at SGFE Ltd
"As a young consulting firm, requests for input from clients vary and it's sometimes impossible to provide expert solutions across a broad spectrum of requirements. That was before I discovered Flevy.com.
Through subscription to this invaluable site of a plethora of topics that are key and crucial to consulting, I have been able to exceed expectations and deliver quality advice and solutions to my clients. The quality and expertise of the authors are exemplary and gives me great confidence to use as part of my service offerings.
I highly recommend this company for any consultant wanting to apply international best practice standards in their service offerings. "
– Nishi Singh, Strategist and MD at NSP Consultants
"As a niche strategic consulting firm, Flevy and FlevyPro frameworks and documents are an on-going reference to help us structure our findings and recommendations to our clients as well as improve their clarity, strength, and visual power. For us, it is an invaluable resource to increase our impact and value."
– David Coloma, Consulting Area Manager at Cynertia Consulting
"If you are looking for great resources to save time with your business presentations, Flevy is truly a value-added resource. Flevy has done all the work for you and we will continue to utilize Flevy as a source to extract up-to-date information and data for our virtual and onsite presentations!"
– Debbi Saffo, President at The NiKhar Group
"As a consulting firm, we had been creating subject matter training materials for our people and found the excellent materials on Flevy, which saved us 100's of hours of re-creating what already exists on the Flevy materials we purchased."
– Michael Evans, Managing Director at Newport LLC
"I have used FlevyPro for several business applications. It is a great complement to working with expensive consultants. The quality and effectiveness of the tools are of the highest standards."
– Moritz Bernhoerster, Global Sourcing Director at Fortune 500
"Flevy is now a part of my business routine. I visit Flevy at least 3 times each month.
Flevy has become my preferred learning source, because what it provides is practical, current, and useful in this era where the business world is being rewritten.
In today's environment where there are so many challenges and there is the need to make the right decisions in a short time, with so much scattered information, we are fortunate to have Flevy. Flevy investigates, selects, and puts at our disposal the best of the best to help us be successful in our work."
– Omar Hernán Montes Parra, CEO at Quantum SFE
Do You Want to Get Lean?We've assembled 40 Lean Six Sigma guides: Lean Management, Six Sigma, Strategy Planning, Process, and Change Management.
Waste Too Much Time on PowerPoint?Get our FREE PowerPoint Plugin (Flevy Tools) for creating common business diagrams, from Gantt Charts to Harvey Balls.
Do You Run a Consulting Firm?Learn how to level the playing field with global consulting firms, like McKinsey, BCG, Bain, E&Y, and Accenture.
Need Help with PowerPoint or Excel?Have our highly trained staff create the exact documents you need. We offer PowerPoint and Excel document creation services.
FlevyPro (Subscription Service)
Flevy Executive Learning (FEL)
LinkedIn Influencer Marketing
Contact / FAQ / Terms / Privacy
flevyblog (Business Magazine)
Strategy, Marketing, & Sales
Organization, Change, & HR
Health & Safety
Governance, Risk, & Compliance
Customer-centric Design (CCD)
Human Resource Management
Blue Ocean Strategy
COVID-19 Trend Data
Consulting Training Guides
Organizational Culture (OC)
Organizational Design (OD)
Organizational Leadership (OL)
Financial Advising Services (FAS)
Flevy Tools (PowerPoint Plugin)
Lean Six Sigma Training Guides
Post-merger Integration (PMI)
Supply Chain Management (SCM)
Small Business Owner
© 2012-2023 Copyright. Flevy LLC. All Rights Reserved.