Financial Model (Toys & Seasonal) Excel Template

Retail Store Model and Valuation presents the business case of a an up and running retail company mostly oriented in the toys and seasonal gifts. The user needs to input the historical income statement and balance sheet in order for the model to generate the three forecast financial statements as well as multiple valuation methods which will be discusssed later on.

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 are able to feed the general information for the model such as: model name, 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. Historic Input Data (from the financial statements) are in light green fill with black characters, 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 a 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).

Moving on to the Inputs: macroeconomic assumptions, detailed inputs for revenues (stores, revenue per store ), costs (costs of goods sold as a percentage margin, sales & general administrative expenses), assets ( non current assets, other non current assets, current assets, cash and other current assets), equity , liabilities both short term (trade payables and other payables) and long term (loans and other liabilities), as well as valuation assumptions.

Calculations: this is were all calculations are performed. Based on the historical sales per country, number of stores and expected evolution of stores and by also taking into account the growth based on each country macros the revenues are calculated. Then operating costs are calculated based on the historical margins and sales and general administrative expenses are calculated based on an average wage growth rate. Based on the capital expenditures and existing fixed assets as indicated by the company growth, the depreciation is calculated based on historical depreciation rates. The company is maintaining a stable loan facility on which it pays interest and any additional needs are covered through an overdraft facility. Working capital such as receivables, inventory and payables are calculated based on the historical days as occuring through the financial statements. Similarly dividends are calculated based on historical payount ratios.

Moving on to the Statements Tab: everything is aggregated here into the relevant statements: profit and loss, balance sheet and cash flow.

Outputs: in this tab the valuation of the company is performed using various valuation methods such as:

Dividend Discount Model
Free Cash Flow to the Firm
Free Cash Flow to Equity
Economic Profit Model
Economic Value Added Model
and
Comparable Companies Multiples

In the summary tab you will be able to compare the Valuation per share from each method and benchmark it to the market price at the time and the analyst consensus.

There are a number of support tabs as well regarding the weighted average costs of capital, the regression parameters, the macroeconomic data used per country as well as the market data used for the comparable companies multiples.

Finally there is a dedicated worksheet (Checks tab) that makes sure that everything is working as it should!