Sheet Forecast: This is the “heart” of our model. Our valuation consists of three stages – the discrete period which is the first 5 years of forecast in which all items are forecasted precisely based on your assumptions, the terminal period which uses a low growth rate that can be sustained into perpetuity and derives the value for future years, and the fade period which connects the discrete period (usually high growth period) with the terminal period (usually low growth period) by fading down/up profitability and other related items. The forecast sheet includes all financial statement items necessary to forecast to upcoming 5 years in detail. The top half of the sheet includes all items with their actual historical figures and their forecasted figures. The lower half of the sheet includes your forecast. The forecast area starts after the blue horizontal line. Starting from this point you can apply all your forecasted numbers into the highlighted area in gray only. The rows highlighted in dark green are ratios that should help you simplify the forecast. If your forecasted numbers diverge strongly from historical inputs, those ratios will increase or decrease accordingly. “Jumps” from one year to another might be an indicator for a mistake. By now you probably realized that the data we are looking at has an annual format. The quarterly numbers can be found if you scroll to the right. Quarterly numbers can be helpful in many ways. If the firm did not release the full financial statement of the year, but let’s say 2 quarterly reports, you will find them there. These numbers will help you making a more accurate prediction for the partial year. For further instructions and guidelines on how to forecast, check the “Forecast support” area on the home screen of this wiki.Sheet DiscountVal: In the top area of this sheet you make all assumptions not related to single items. This includes this like discount rate and assumptions for the fade and terminal period. Next to your assumptions you can find several graphs visualizing your forecast to spot unrealistic input fast. Below the top area you see the breakdown of valuation for each of the valuation methods, namely free cash flow and shareholder value for the discounted cash flow methods, the dividend discount model, and the residual income method. For each method you will see the assumptions used to come up with the specific value.
Sheet MultipleVal: As the name indicates, this sheet will give you several multiples regarding the firm to value. However, that’s not all. After choosing the country and sector to compare with, you will be provided with the sector multiples for your country, Asia ex-Japan and the world! This gives you a fast option to evaluate whether your company is cheap or expensive to peers all over the world.
Sheet Out1: This sheet displays the output for your finished forecast. Here you will find not only all items but also useful ratios and ROIC analysis. No input required here.
Sheet MainAssump: This is a summary of your main assumptions primarily made in the DiscountVal sheet, the MultipleVal sheet, and a sensitivity analysis in which you can manually adjust the specifics of your analysis within the gray area. After you finished your forecast, you have to update the sensitivity analysis manually. To update the sensitivity analysis go to the index card “ASIR FIN MODEL” in the register of your excel functions on top of your screen and click the button “Sensitivity”. Congratulations, you just finished your first forecast!
Sheets New1, New2, New3: These sheets are empty and for the sole purpose of keeping notes and doing separate calculations. You might want to add some notes on why you forecasted certain items in a certain way so you can pick up where you left the next time you open the model or the firm updates their financial data.
