Financial Modeling

Home

Forum

Financial Modeling Topics

Financial Modeling
- Financial Modeling

Ask your modeling questions here! Feel free to copy/paste specific sections from filings as appropriate.

Questions/Discussions

Sort by Date ▼ / Top Rated

Convert an annual model into a quarterly tracking model

Full Question: In my new job, where I follow the retail industry as a high-yield analyst, I have been told that I might want to prepare quarterly forecasts in order to gauge how a company is doing relative to my annual forecast. How do you suggest amending the forecast we prepared in class to prepa... Read More

Show All 1 Replies Login to Reply

by Guest 1.

added 10 years ago

RE: Convert an annual model into a quarterly tracking model

If you are actively tracking an industry as a research analyst, it is absolutely critical to maintain a "tracking model" in which you update quarterly results. your model must be flexible enough to handle the periodic updates from the company. regardless of the cyclicality of the business (which in ... If you are actively tracking an industry as a research analyst, it is absolutely critical to maintain a "tracking model" in which you update quarterly results. your model must be flexible enough to handle the periodic updates from the company. regardless of the cyclicality of the business (which in the retail space, is obviously cyclical), you need a quarterly model.

To expand the projection model from an annual model to a quarterly model, it really isn't difficult:
- increase the number of periods on the right by selecting the last full year and Ctrl+R to however many additional periods you want
- change the labels from annual to Q1, Q2, Q3, Q4 and add a new row on top to indicate the Years
- decide if you want to put Full Year right after Q4 or summarized to the right of your model, really depends on your preference. obviously Full Year will be sum of the applicable four quarters
- instead of sequential Year-over-Year growth rates, use Quarter-over-Quarter growth rates
- change the Balance Sheet ratios from 365 days to the correct number of days in the quarter. use date arithmetic in Excel to automatically calculate the number of days in a row that is white to avoid printing and then reference the number of days to that row
- update historical inputs with quarterly instead of annual figures
- don't forget to update the labels to the correct quarter
- since you are specifically following high-yield, your ratios will be off the full year results. it may not be a bad idea to have an annualized ratio each quarter, although this is less meaningful due to cyclicality

Additional Note: Unfortunately, while quarterly results can easily be converted to annual, quarterly results cannot be imputed from your annual totals. In order to build a quarterly projection model you need to input historical quarterly results (we would suggest at least 3 years) and based on your knowledge of the company, historical evidence of seasonality, etc., you have to forecast future quarters based on Q/Q growth rates, margins, etc. Read More

by WST Expert 1.

added 10 years ago

Show More Show Less