please solve in excel

The focus of the assignment is on using the calculations correctly and presenting the analysis with a professionally organized spreadsheet. You will need to make a few assumptions that you need to explain, but there are no right or wrong answers when it comes to the assumptions. Scenario You will assume that you are the brand manager at a Chicago based company that makes better-for-you cookies. The company currently has one brand called Oaties. Oaties is a brand of oatmeal cookies with different flavors. You are selling directly to Whole Foods, which will carry your product in all its Midwest stores. In other words, your distribution channel includes a retailer only. Your product comes in a box of 20 cookies. You are currently evaluating the launch of a different brand of cookies that will be higher in protein. Your goal is to evaluate the financial impact of launching this new brand. To make this evaluation you need to decide on a retail price for the new product, estimate how many units you will sell of the new product, and estimate the effect of cannibalization. The financials for the old and the new product that you start with are the following: Step 1. Calculating your selling price and unit contribution You first need to decide on the retail selling price for your new product. It can be the same as, or higher or lower than the price for your existing brand. Keep in mind that the new product will be slightly more expensive to make due to more expensive ingredients. Please add an explanation for your decision (1-2 sentences will be sufficient). Once you have decided on your retail price, you can calculate the price at which you will sell it to your retailer using the channel partner's margin, and your unit contribution based on your selling price and the COGS per unit. Step 2. Sales Forecast for the New Product The unit sales forecast for 2022 for the existing product is already provided above. To calculate a sales forecast for the new brand for 2022 , you need to estimate a sales forecast per month and then add up the monthly sales projections for a yearly sales projection. To complete the forecast, you need to make assumptions about the number of customers that will try this product every month, a repeat purchase rate, a purchase frequency, and how many units you think the average consumer with buy at a time. The table below provides an example of how you would organize the analysis for your sales forecast. You can either assume that the new customer number, the repurchase rate (95% in the example below), the average purchase frequency and the average purchase volume, is consistent from month to month or that these will change over time. While there are no right or wrong assumptions, you need to explain your thought process for making them (the calculations below are from Excel and minor inconsistencies are the effect of rounding). Keep in mind that the table above is just an example of the structure of this analysis. It will likely be reasonable with higher numbers of new customers each month given that you will be selling the cookies in all Whole Foods stores in the Midwest. Step 3. Calculating total contribution with and without cannibalization In the final step of your analysis, you will determine what the total contribution will be with and without cannibalization. You have discussed the issue of cannibalization with others in the company and based on this input you think the cannibalization rate will be somewhere between 5 to 30 percent. For the final analysis you need to decide on a specific cannibalization rate within that range and explain your decision (1-2 sentences will be sufficient). The table below presents a model you can use for the cannibalization analysis. You will use the information from the steps above about the existing product as well as the price, contribution margin and sales forecast you calculated for the new product. a cannibalization rate of 50% means that 50% of the sales of the new product, or 30 units in this case, will be taken from the existing brand. Based on your calculations you will comment on whether you think cannibalization will be a significant problem in this situation or not (2-3 sentences). How you will be graded The assignment will be graded on the correctness of the calculations, the clarity and organization of your spreadsheet, and the explanation of your choices. A grading rubric will be available on Blackboard. Please make sure that the formulas you use are visible so that I can easily track your analyses. The focus of the assignment is on using the calculations correctly and presenting the analysis with a professionally organized spreadsheet. You will need to make a few assumptions that you need to explain, but there are no right or wrong answers when it comes to the assumptions. Scenario You will assume that you are the brand manager at a Chicago based company that makes better-for-you cookies. The company currently has one brand called Oaties. Oaties is a brand of oatmeal cookies with different flavors. You are selling directly to Whole Foods, which will carry your product in all its Midwest stores. In other words, your distribution channel includes a retailer only. Your product comes in a box of 20 cookies. You are currently evaluating the launch of a different brand of cookies that will be higher in protein. Your goal is to evaluate the financial impact of launching this new brand. To make this evaluation you need to decide on a retail price for the new product, estimate how many units you will sell of the new product, and estimate the effect of cannibalization. The financials for the old and the new product that you start with are the following: Step 1. Calculating your selling price and unit contribution You first need to decide on the retail selling price for your new product. It can be the same as, or higher or lower than the price for your existing brand. Keep in mind that the new product will be slightly more expensive to make due to more expensive ingredients. Please add an explanation for your decision (1-2 sentences will be sufficient). Once you have decided on your retail price, you can calculate the price at which you will sell it to your retailer using the channel partner's margin, and your unit contribution based on your selling price and the COGS per unit. Step 2. Sales Forecast for the New Product The unit sales forecast for 2022 for the existing product is already provided above. To calculate a sales forecast for the new brand for 2022 , you need to estimate a sales forecast per month and then add up the monthly sales projections for a yearly sales projection. To complete the forecast, you need to make assumptions about the number of customers that will try this product every month, a repeat purchase rate, a purchase frequency, and how many units you think the average consumer with buy at a time. The table below provides an example of how you would organize the analysis for your sales forecast. You can either assume that the new customer number, the repurchase rate (95% in the example below), the average purchase frequency and the average purchase volume, is consistent from month to month or that these will change over time. While there are no right or wrong assumptions, you need to explain your thought process for making them (the calculations below are from Excel and minor inconsistencies are the effect of rounding). Keep in mind that the table above is just an example of the structure of this analysis. It will likely be reasonable with higher numbers of new customers each month given that you will be selling the cookies in all Whole Foods stores in the Midwest. Step 3. Calculating total contribution with and without cannibalization In the final step of your analysis, you will determine what the total contribution will be with and without cannibalization. You have discussed the issue of cannibalization with others in the company and based on this input you think the cannibalization rate will be somewhere between 5 to 30 percent. For the final analysis you need to decide on a specific cannibalization rate within that range and explain your decision (1-2 sentences will be sufficient). The table below presents a model you can use for the cannibalization analysis. You will use the information from the steps above about the existing product as well as the price, contribution margin and sales forecast you calculated for the new product. a cannibalization rate of 50% means that 50% of the sales of the new product, or 30 units in this case, will be taken from the existing brand. Based on your calculations you will comment on whether you think cannibalization will be a significant problem in this situation or not (2-3 sentences). How you will be graded The assignment will be graded on the correctness of the calculations, the clarity and organization of your spreadsheet, and the explanation of your choices. A grading rubric will be available on Blackboard. Please make sure that the formulas you use are visible so that I can easily track your analyses