While Simon is happy with the pie prices, volumes of pies sold and contributions of the...

 

Transcribed Image Text:

While Simon is happy with the pie prices, volumes of pies sold and contributions of the individual four pie types, he notices that his production capacity is limited to 24,000 pies. For each additional pie 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. Represent the profit for different capacity limits and different apple pie prices. The apple pie price should be on the x-axis. 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. While Simon is happy with the pie prices, volumes of pies sold and contributions of the individual four pie types, he notices that his production capacity is limited to 24,000 pies. For each additional pie 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. Represent the profit for different capacity limits and different apple pie prices. The apple pie price should be on the x-axis. 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.