This page demonstrates how to perform financial analysis of building a new store and the process of efficiently extracting data from an enterprise financial database. A lot of the model is about efficiently using the INDEX function and extracting comparative store data for different stores.The example model shows how you can create a financial forecast from the comparative data and include potential cannibalization. The various techniques are illustrated in the file that you can download below.
New Store Analysis: How to Evaluate Data from Corporate Databases in Creating Models of New Stores
The model below demonstrates how to extract data from a corporate database that records data for different facilities and then use the information to evaluate statistics relative to the proposed new store. This is similar to evaluating history relative to the forecast in a standard corporate model. Some of the tricky issues in the model are selecting different samples of existing stores for comparison. The model also includes a method where data can be graphed and then a macro can be used to drill down to the assumption. In this model the type of model structuring that is flexible, structured and transparent is an important part of the process. The inputs page is structured in the same order as the financial model and all of the inputs come from this sheet. The financial model describes each item in the financial statement in a transparent and simple manner whereby you can see how each of the revenue, cost, capital expenditure and balance sheet items are derived. These items are presented before the financial statements are presented. Finally a summary is presented where key outputs are shown next to the inputs.