Forecasts (Excel)

1.Your group is required to do the following forecasts (2019) for the Income Statement:

a)Moving 3 year average

b)Moving 5 year average

c)Trend based on absolute change over last 5 years

d)Trend based on relative change over last 5 years

e)Trend based on common size over last 5 years

f)Any combination of the above depending on the line item

2.You can have 1 page per forecast, have them all on 1 page, or any combination. It is your choice, but it must make logical sense. Each column must be labeled so the reader knows what forecast they are reading. In addition, if you use multiple pages you must make sure the Income Statement names (first column) appears on all pages otherwise how would a reader know what line items each number goes to.

3.All forecasts are to be formulas linked to the 5 years of data or subtotal/total formulas. There are to be no hardcoded numbers.

Support for forecasts (Write-Up)

1.The moving 3 years and moving 5 years forecasts do not need support as there is only 1 way to do each. Your Excel file will be reviewed to make sure you did these calculations right.

2.The next two forecasts, trend based on absolute change and trend based on relative change, you must support how you came up with the trend. You must calculate the absolute and relative change over all 5 years before determining the trend. In class, the trend was consistent from year to year, but in your data it will not be. You must make a reasonable determination on what to use based on what you have learned in class. Do not give any numbers here since the forecast already has the numbers. You should look at each individual account and each may have a different logic. Subtotals should stay subtotals and you should not use a trend on a subtotal. As long as it is logical, you evaluated every line item, and you support your choice, you will get credit. Keep in mind these are two different forecasts. One you only evaluate the absolute trend and forecast on that and the second one you only evaluate the relative change and forecast on that. Do not combine these two together. Your write-up must be the logic and support for what you used for each one.

Follow Chapter 9, Problem 11 if you don’t know where to start.

3.For trend based on common size follow the same method as the last two forecasts. You must calculate the common size for all 5 years before determining the trend. This forecast will require you to use a different method for total revenue first and then common size after. Total revenue needs a different forecast because that is the 100% on your common size and you cannot forecast a number with just 100%. You must have a starting number and then all numbers calculate off this. You can use whichever previous forecast (a-d) you want for total revenue as long as you explain and support why you choose that one. You must make a reasonable determination on what to use based on what you have learned in class. Do not give any numbers here since the forecast already has the numbers. As long as it is logical, you evaluated every line item, and you support your choice, you will get credit.

Follow Chapter 10, Problem 1 if you don’t know where to start.

4.The last forecast method is one of your own choosing but it cannot just be one of the other forecast you have already done. This forecast should be a combination of any of the forecast methods previously done for this project (do not use other methods besides a-e). This is the most common type of forecast as each account generally changes differently. For instance some accounts may be growing by a certain percentage year over year, while others may be changing by a certain dollar amount each year, others could be fluctuating randomly, and others still may be based on the revenue that they generate. For each account, you must determine and support why you choose the method you did. Again give no numbers but address how the account is changing over time, in general terms, and use that to determine how you will forecast it for this year.

5.The last part of the write-up is your group choosing which forecast method you would suggest the company use and why. You can only choose one of the six methods you calculated. Keep in mind in industry, we do all the methods first, evaluate the numbers, and based on intuition and our knowledge of the business select the most appropriate method. You should never go into a project with a predetermined outcome because then you are just doing busy work and not opening your eyes to other potentially good options. To adequately support your decision this must be a minimum of ¾ of a page. You cannot just have 1-2 sentences on why you choose it. You need to convince your manager that this is the correct method to use.