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
• Estimate the projections for 2021 based on:
• The historical financial statements
• The 2021 economy and beer/alcohol industry
• Additional company specific information provided below
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.
