Financial Modeling
1) Create a financial forecasting model for 2021 that contains the following features:
• Calculate the historical ratios and three-year averages for The Ale House.
• Refer to the in-class example and slides if you unsure how to calculate some of the ratios
Note: for the dividend ratio you need to include an IF statement to account for no dividends being paid. If the previous year’s dividend payments were zero, then the ratio should also be zero. If the previous year’s dividend payments were greater than zero, then the payment ratio should include the calculation for dividend growth over the past year.
• Estimate the projections for 2021 based on:
• The historical financial statements
• The 2021 economy and beer/alcohol industry
• Additional company specific information provided below
Note: there is no right answer for the projections, only an educated guess. You will be graded on the reasonableness of your estimates and the explanation of your assumptions.
DO NOT base your numbers off the base case projections.
Additional company information
• The Ale House plans on building a new $10 million brewery in 2021.
• Due to increased demand The Ale House plans to keep at least 2% more inventory in stock than they have in the past.
• The cost of hops (a key ingredient in beer production) is expected to increase by 5% next year.
• The Ale House plans to begin selling their beer in Canada and Mexico for the first time in 2021. They expect this will increase their sales by 3 million beers ($3 each) in addition to their U.S. sales.
• Due to predictions of an excessively long cold and snowy winter, people are drinking more beer than normal, The Ale House expects to sell more beer in the U.S. this year than it would in a typical year.
• Assume The Ale House does not plan to make any significant changes other than those listed above.
2) Insert comments for YOUR 2021 Projected Parameters in the in the Est. Inputs tab. These comments should briefly describe how you determined your projection estimates. Make your you identify whether the additional company information made your input increase or decrease.
