Question 8 Mr. Simon is considering the expansion of his business. He wants to produce additional fruit-base pies: lemon, strawberry and cherry. The selling prices of these pies are set in terms of the apple pie price. The cherry pie is known to have the same price as an apple pie; the lemon pie is cheaper than the apple one by S 1.00; and the strawberry pic costs $ 0.25 more than an apple pie. This expansion however raises the overhead cost to $ 33,000. As expected, Mr. Simon finds that the demand and processing cost equations as well as the unit ingredient costs are slightly different from one fruit to another (see the table below). The demand equations are respectively: Apple Pies Demanded = 52.4 - 4.2x Apple Pie Price. Lemon Pies Demanded = 55.6-4.6x Lemon Pie Price Strawberry Pies Demanded = 55-4.5x Strawberry Pie Price Cherry Pies Demanded=56.6-4.7xCherry Pie Price The apple pie is $9.12. The processing costs are respectively: Apple: see Question 5 Lemon: Processing Cost =-12.5+4.5x Production Strawberry: Processing Cost = -12.4 +3.6x Production Cherry: Processing Cost-12.6+3.26x Production In addition, the ingredients costs are provided in the table below, Apple Lemon Strawberry Cherry Total Unit cost fruit filling (per pic) 3,48 2,2 3.9 3,1 Unit cost, dough 0,3 0.28 0.35 0,33 Fixed cost (000's) a. Compute the usual financial outcomes. In particular show the contributions of each pie type and deduce the profit. b. Draw a stacked column plot of the revenue, the contribution, the ingredient cost and the processing cost with the fruits on the horizontal axis. Note: Create Sheet 08 containing all calculations as well as the stacked column plot. Question 9 While Simon is happy with the pie prices, volumes of pics sold and contributions of the individual four pie types, he notices that his production capacity is limited to 24,000 pies. For cach additional pic to be produced he incurs a $ 0.85 cost, known as the overtime processing cost. a. By means of an appropriate IF statement implemented in an excel sheet (that you will label Sheet 09) find the new total cost processing. The usual processing cost will be renamed as "normal processing cost" opposed to the overtime processing cost which occurs when the capacity limit of 24,000 pies is exceeded. b. Find the new ingredient costs, total cost, contributions and the profit Question 10 Run two sensitivity analyses of questions 9 by means of the Data Table tool available in Excel. a. In the first analysis one varies the normal processing capacity (from 24,000 to 34,000 pies) and the apple pie price (from 80% to 120% of the base apple price $9.12) and then assesses the impact on the profit. (i) Represent the profit for different capacity limits and different apple pie prices. The apple pie price should be on the x-axis. (ii) Interpret the results. Are the processing capacity and the apple pie price independent? b. The second sensitivity analysis evaluates the impact of the apple pie price (from 70% to 120% of the base apple price $9.12) on the different financial outcomes. Use the Data Table tool with just one input variable. Draw the related graph. Interpret the findings. Note: Create Sheet_Q10a and Sheet_Q10b containing all calculations and related graphs. Question 11 (Bonus) Redo the sensitivity analysis of questions 4 and 5 by means of the Data Table tools. Note: Create Sheet Q11 containing all calculations. Question 8 Mr. Simon is considering the expansion of his business. He wants to produce additional fruit-base pies: lemon, strawberry and cherry. The selling prices of these pies are set in terms of the apple pie price. The cherry pie is known to have the same price as an apple pie; the lemon pie is cheaper than the apple one by S 1.00; and the strawberry pic costs $ 0.25 more than an apple pie. This expansion however raises the overhead cost to $ 33,000. As expected, Mr. Simon finds that the demand and processing cost equations as well as the unit ingredient costs are slightly different from one fruit to another (see the table below). The demand equations are respectively: Apple Pies Demanded = 52.4 - 4.2x Apple Pie Price. Lemon Pies Demanded = 55.6-4.6x Lemon Pie Price Strawberry Pies Demanded = 55-4.5x Strawberry Pie Price Cherry Pies Demanded=56.6-4.7xCherry Pie Price The apple pie is $9.12. The processing costs are respectively: Apple: see Question 5 Lemon: Processing Cost =-12.5+4.5x Production Strawberry: Processing Cost = -12.4 +3.6x Production Cherry: Processing Cost-12.6+3.26x Production In addition, the ingredients costs are provided in the table below, Apple Lemon Strawberry Cherry Total Unit cost fruit filling (per pic) 3,48 2,2 3.9 3,1 Unit cost, dough 0,3 0.28 0.35 0,33 Fixed cost (000's) a. Compute the usual financial outcomes. In particular show the contributions of each pie type and deduce the profit. b. Draw a stacked column plot of the revenue, the contribution, the ingredient cost and the processing cost with the fruits on the horizontal axis. Note: Create Sheet 08 containing all calculations as well as the stacked column plot. Question 9 While Simon is happy with the pie prices, volumes of pics sold and contributions of the individual four pie types, he notices that his production capacity is limited to 24,000 pies. For cach additional pic to be produced he incurs a $ 0.85 cost, known as the overtime processing cost. a. By means of an appropriate IF statement implemented in an excel sheet (that you will label Sheet 09) find the new total cost processing. The usual processing cost will be renamed as "normal processing cost" opposed to the overtime processing cost which occurs when the capacity limit of 24,000 pies is exceeded. b. Find the new ingredient costs, total cost, contributions and the profit Question 10 Run two sensitivity analyses of questions 9 by means of the Data Table tool available in Excel. a. In the first analysis one varies the normal processing capacity (from 24,000 to 34,000 pies) and the apple pie price (from 80% to 120% of the base apple price $9.12) and then assesses the impact on the profit. (i) Represent the profit for different capacity limits and different apple pie prices. The apple pie price should be on the x-axis. (ii) Interpret the results. Are the processing capacity and the apple pie price independent? b. The second sensitivity analysis evaluates the impact of the apple pie price (from 70% to 120% of the base apple price $9.12) on the different financial outcomes. Use the Data Table tool with just one input variable. Draw the related graph. Interpret the findings. Note: Create Sheet_Q10a and Sheet_Q10b containing all calculations and related graphs. Question 11 (Bonus) Redo the sensitivity analysis of questions 4 and 5 by means of the Data Table tools. Note: Create Sheet Q11 containing all calculations