excel future forecast projections

Help them... or die. The brewery has two units of sale: a retail sales unit - 16 oz pints - and a wholesale sales unit - 1,984 oz. kegs. The pints will be sold for $6.50 and the kegs for $160. The variable cost per ounce of beer brewed is $0.046875/ounce. In addition, they estimate the brewery will incur the following fixed costs per month: Salaries & Wages Mortgage Insurance Utilities Marketing Expenses Misc. Accounts Payable $6000 $4500 $1500 $1800 $1400 $1500 Pandemic Brewing's brewing capacity is based on a 30-barrel brewing system. When operating at maximum capacity, assuming no beer is lost in the process, the founders believe they can brew approximately three batches per month. There are 3,968 fluid ounces in a barrel of beer. When completing this exercise, remember this is a projection - none of this has actually happened yet. The goal is to derive the most accurate forecasts possible to assist the founding team in their capital expenditures moving forward. 1. Based on their monthly fixed expenses and the variable cost per unit, compute the number of pints they would need to sell in a month to breakeven (assuming they only sell pints). Hint: Breakeven is calculated by dividing total fixed costs by the unit contribution (price - variable cost). (1 point) 2. Based on their monthly fixed expenses and the variable cost per unit, compute the number of kegs they would need to sell in a month to breakeven (assuming they only sell kegs). (1 point) 3. According to their digital marketing strategy and comparable conversion ratios they have researched on digital marketing analytics for their industry, they expect that they will see a conversion ratio of 15% (sales/impression). Research from IBISWorld suggests the average customer purchases two and a half drinks (pints) when visiting a microbrewery. Assuming they make a baseline of 10,000 impressions/month, compute their estimate revenue, gross profit, and earnings before interest, taxes, depreciation, & amortization (EBITDA) if they only sell pints during the first month. How much extra beer would that leave them with? (Start with the assumption that they brewed the maximum capacity prior to opening their doors). (3 points) 4. In month two, they continue their marketing activities that they anticipate will yield an additional 2500 new impressions. Assuming they maintain their first month sales forecast as a baseline (they sell the same number of units as month one, plus the new customers) and convert those new customers at the same rate of 10%, forecast revenue, gross profit, and EBITDA for month two. (3 points) 5. Looking at these projections, the founding team realizes they will have a surplus of inventory sitting in fermentation tanks at the end of month two that they need to sell before it goes bad. As stated previously, they have already brewed the max capacity (three batches with a 30 barrel system). They've decided they are going to keg all of the excess beer that they aren't going to sell in the first two months, and not brew any new beer until after month 2. As part of their marketing strategy, they intend on giving away 50% of the kegs for promotional purposes in month 1 and selling the remaining 50% for $ 150/keg in month two. Compute the new EBITDA in month two. (3 points) 6. Now let's make it fun. Let's start by assuming the promotional campaign of giving away all those kegs in Month two leads to a massive increase in demand in Month 3. Because they have you advising them, you've anticipated this spike in demand and encouraged them to begin brewing at capacity in Month 3. Based on your forecasts, you're assuming the following: Pandemic Brewing will be able to sell 75% of their total maximum production capacity in Month 3, and 100% of capacity in Months 4 through 12 (don't worry about carrying over the 25% extra beer from month 3 - we'll chalk that up to a lot of spilled beer. Simply use these capacity assumptions for forecasting revenue). 80% of their sales volume will be retail (pints) units, and 20% will be by distributing kegs. Due to increased demand, they hire two new employees in Month 4, increasing Salaries & Wages by $7000 from Month 4 on. . Compute EBITDA for the first year. (6 points) 7. Assume the founders are able to increase their total revenue from Year 1 by 25% in Year 2 and 15% in Year 3 (relative to Year 2). We will also assume that the gross margin remains the same, and total operating expenses increase by 10% in year 2 and 5% in year 3 (relative to year 2). Compute the net present value of a $500,000 investment assuming a 50% required rate of return. Create your own formula - using Excel's canned NPV function will not count. (3 points) Help them... or die. The brewery has two units of sale: a retail sales unit - 16 oz pints - and a wholesale sales unit - 1,984 oz. kegs. The pints will be sold for $6.50 and the kegs for $160. The variable cost per ounce of beer brewed is $0.046875/ounce. In addition, they estimate the brewery will incur the following fixed costs per month: Salaries & Wages Mortgage Insurance Utilities Marketing Expenses Misc. Accounts Payable $6000 $4500 $1500 $1800 $1400 $1500 Pandemic Brewing's brewing capacity is based on a 30-barrel brewing system. When operating at maximum capacity, assuming no beer is lost in the process, the founders believe they can brew approximately three batches per month. There are 3,968 fluid ounces in a barrel of beer. When completing this exercise, remember this is a projection - none of this has actually happened yet. The goal is to derive the most accurate forecasts possible to assist the founding team in their capital expenditures moving forward. 1. Based on their monthly fixed expenses and the variable cost per unit, compute the number of pints they would need to sell in a month to breakeven (assuming they only sell pints). Hint: Breakeven is calculated by dividing total fixed costs by the unit contribution (price - variable cost). (1 point) 2. Based on their monthly fixed expenses and the variable cost per unit, compute the number of kegs they would need to sell in a month to breakeven (assuming they only sell kegs). (1 point) 3. According to their digital marketing strategy and comparable conversion ratios they have researched on digital marketing analytics for their industry, they expect that they will see a conversion ratio of 15% (sales/impression). Research from IBISWorld suggests the average customer purchases two and a half drinks (pints) when visiting a microbrewery. Assuming they make a baseline of 10,000 impressions/month, compute their estimate revenue, gross profit, and earnings before interest, taxes, depreciation, & amortization (EBITDA) if they only sell pints during the first month. How much extra beer would that leave them with? (Start with the assumption that they brewed the maximum capacity prior to opening their doors). (3 points) 4. In month two, they continue their marketing activities that they anticipate will yield an additional 2500 new impressions. Assuming they maintain their first month sales forecast as a baseline (they sell the same number of units as month one, plus the new customers) and convert those new customers at the same rate of 10%, forecast revenue, gross profit, and EBITDA for month two. (3 points) 5. Looking at these projections, the founding team realizes they will have a surplus of inventory sitting in fermentation tanks at the end of month two that they need to sell before it goes bad. As stated previously, they have already brewed the max capacity (three batches with a 30 barrel system). They've decided they are going to keg all of the excess beer that they aren't going to sell in the first two months, and not brew any new beer until after month 2. As part of their marketing strategy, they intend on giving away 50% of the kegs for promotional purposes in month 1 and selling the remaining 50% for $ 150/keg in month two. Compute the new EBITDA in month two. (3 points) 6. Now let's make it fun. Let's start by assuming the promotional campaign of giving away all those kegs in Month two leads to a massive increase in demand in Month 3. Because they have you advising them, you've anticipated this spike in demand and encouraged them to begin brewing at capacity in Month 3. Based on your forecasts, you're assuming the following: Pandemic Brewing will be able to sell 75% of their total maximum production capacity in Month 3, and 100% of capacity in Months 4 through 12 (don't worry about carrying over the 25% extra beer from month 3 - we'll chalk that up to a lot of spilled beer. Simply use these capacity assumptions for forecasting revenue). 80% of their sales volume will be retail (pints) units, and 20% will be by distributing kegs. Due to increased demand, they hire two new employees in Month 4, increasing Salaries & Wages by $7000 from Month 4 on. . Compute EBITDA for the first year. (6 points) 7. Assume the founders are able to increase their total revenue from Year 1 by 25% in Year 2 and 15% in Year 3 (relative to Year 2). We will also assume that the gross margin remains the same, and total operating expenses increase by 10% in year 2 and 5% in year 3 (relative to year 2). Compute the net present value of a $500,000 investment assuming a 50% required rate of return. Create your own formula - using Excel's canned NPV function will not count. (3 points)