This web page demonstrates a couple of other corporate finance concepts. The first example demonstrates how to perform financial analysis of building a new store and the process of efficiently extracting data from an enterprise financial database. The second example illustrates how to model a cash flow waterfall in the context of financing a construction company. A lot of the new store 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. The progress payment model is a lot about using MIN, MAX and OFFSET in evaluating loan draws and loan repayments.
New Store Analysis Demonstrating Creation of Model from Database and Analysis of ROIC and Location
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.
Progress Payment Loan Analysis and Short-term Financing
The video below illustrates how to construct a loan analysis for a progress payment facility. I will put some explanation here, but for now I have a video. The file is attached to the button below and the video that explains how to use the MIN and MAX functions in modelling a cash flow waterfall.