In this problem, you will determine the quantity of muffins to be produced under the demand uncertainty. You will use marginal economic analysis.
Problem
Inventory Problem | ||||||||
Macrina Bakery is a locally owned bakery started by Leslie Mackie based in Kent, WA. This bakery has four cafés in the Seattle area and also sells its products to other restaurants and retailers in the Seattle area. One of their most popular pastry items is the Morning Glory muffin which is baked with fruit, nuts, and coconut. Suppose your are helping a bakery like Macrina Bakery decide how many muffins to bake each morning for sale in a café based on their last year of daily muffin sales. The bakery charges $3 for a muffin the day they are baked and $1 for day old muffins; the average total cost for producing a muffin is $1.50 (assume MC is the same as AC). Demand for muffins is typically between 20 and 30 each day. Demand has been lower than 20; however, this is an exceptionally rare occurrence. Demand in excess of 30 muffins is accompanied with an order for muffins in advance and is not considered a random variable. Use the information in the table below to complete the following tasks and answer the following questions. | ||||||||
Original Price | $3.00 | |||||||
Day-Old Price | $1.00 | |||||||
Cost | $1.50 | |||||||
a) | Using the demand frequency information provided, determine the probability of each quantity demanded. | |||||||
b) | Using the probabilities from part a, find the probability that the bakery will sell each QD or more and each QD or less. | |||||||
c) | Using the original price and cost information, find the expected marginal benefit of selling muffins at each QD. | |||||||
d) | Using the day-old price and cost information, find the expected marginal cost of selling muffins at each QD. | |||||||
Quantity | Frequency | Probability | Probability of Selling | Probability of Selling | Expected | Expected | ||
Demanded-QD | of QD | of QD | this QD or More | Less than QD | Marginal Benefit | Marginal Cost | ||
20 | 5 | |||||||
21 | 14 | |||||||
22 | 30 | |||||||
23 | 48 | |||||||
24 | 56 | |||||||
25 | 73 | |||||||
26 | 59 | |||||||
27 | 32 | |||||||
28 | 26 | |||||||
29 | 15 | |||||||
30 | 7 | |||||||
TOTALS | ||||||||
e) | What quantity of muffins would you recommend this bakery to produce each day? | |||||||
The bakery should produce | muffins each day. | |||||||
f) | Suppose a new low-carb diet fad emerges which shifts demand for the bakery's muffins. To maintain demand, the bakery must drop their price from $3 to $2. They also decide to give excess muffins to their employees to take home instead of keeping their day-old muffin policy as a boost for employee morale. Find the expected marginal benefit and expected marginal cost of selling muffins at each QD under new prices. | |||||||
Original Price | $2.00 | |||||||
Day-Old Price | $0.00 | |||||||
Quantity | Frequency | Expected | Expected | |||||
Demanded-QD | of QD | Marginal Benefit | Marginal Cost | |||||
20 | 5 | |||||||
21 | 14 | |||||||
22 | 30 | |||||||
23 | 48 | |||||||
24 | 56 | |||||||
25 | 73 | |||||||
26 | 59 | |||||||
27 | 32 | |||||||
28 | 26 | |||||||
29 | 15 | |||||||
30 | 7 | |||||||
How many muffins each day should the bakery produce now? | ||||||||
Now the bakery should produce | muffins each day. | |||||||
Instructions
Project Description: In this problem, you will determine the quantity of muffins to be produced under the demand uncertainty. You will use marginal economic analysis. | ||
For the purpose of grading the project you are required to perform the following tasks: | ||
Step | Instructions | Points Possible |
1 | Start Excel. | 0 |
2 | In cell D25, by using cell references, calculate the total number of frequencies of the quantities demanded. Use the Excel SUM function and cells D14-D24. | 1 |
3 | In cell E14, by using relative and absolute cell references, calculate the probability of the quantity demanded specified in cell C14. Use cells D14 and D25. Copy the formula from cell E14 down the column to cell E24. | 1 |
4 | In cell E25, by using cell references, calculate the sum of all the probabilities. Use the Excel SUM function and cells E14-E24. | 1 |
5 | In cells F14-F24, do the following: In cell F24, by using a cell reference, determine the probability of selling the quantity demanded specified in cell C24. Refer to an appropriate cell among E14-E24. In cell F23, by using cell references, calculate the probability of selling the quantity demanded specified in cell C23 or more. Use cells E23 and F24. Copy the formula from cell F23 up the column to cell F14. | 2 |
6 | In cell G14, by using relative and absolute cell references, calculate the probability of selling less than the quantity demanded specified in cell C14. Use cells F14 and E25. Copy the formula from cell G14 down the column to cell G24. | 1 |
7 | In cell H14, by using relative and absolute cell references, calculate the expected marginal benefit corresponding to the quantity demanded specified in cell C14 and the frequency specified in cell D14. Use cells D5, D7, and F14. Copy the formula from cell H14 down the column to cell H24. | 1 |
8 | In cell I14, by using relative and absolute cell references, calculate the expected marginal cost corresponding to the quantity demanded specified in cell C14 and the frequency specified in cell D14. Use cells D6, D7, and G14. Copy the formula from cell I14 down the column to cell I24. | 1 |
9 | In cell E27, by using a cell reference, determine the quantity of muffins the bakery should produce each day. Refer to an appropriate cell among C14-C24. | 1 |
10 | In cell E33, by using relative and absolute cell references, calculate the expected marginal benefit assuming the conditions described in part f. Use cells D7, D29, and F14. Copy the formula from cell E33 down the column to cell E43. | 1 |
11 | In cell F33, by using relative and absolute cell references, calculate the expected marginal cost assuming the conditions described in part f. Use cells D7, D30, and G14. Copy the formula from cell F33 down the column to cell F43. | 1 |
12 | In cell E45, by using a cell reference, determine the quantity of muffins the bakery should produce each day assuming the conditions described in part f. Refer to an appropriate cell among C33-C43. | 1 |
13 | Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. | 0 |
,
Grader – Instructions Excel 2016 Project
Chapter 14 Inventory Problem (v1)
Project Description:
In this problem, you will determine the quantity of muffins to be produced under the demand uncertainty. You will use marginal economic analysis.
Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Start Excel. Download and open the workbook named: Chapter_14_Inventory_Problem_Start. |
0 |
2 |
In cell D25, by using cell references, calculate the total number of frequencies of the quantities demanded. Use the Excel SUM function and cells D14-D24. |
1 |
3 |
In cell E14, by using relative and absolute cell references, calculate the probability of the quantity demanded specified in cell C14. Use cells D14 and D25. Copy the formula from cell E14 down the column to cell E24. |
1 |
4 |
In cell E25, by using cell references, calculate the sum of all the probabilities. Use the Excel SUM function and cells E14-E24. |
1 |
5 |
In cells F14-F24, do the following: In cell F24, by using a cell reference, determine the probability of selling the quantity demanded specified in cell C24. Refer to an appropriate cell among E14-E24. In cell F23, by using cell references, calculate the probability of selling the quantity demanded specified in cell C23 or more. Use cells E23 and F24. Copy the formula from cell F23 up the column to cell F14. |
2 |
6 |
In cell G14, by using relative and absolute cell references, calculate the probability of selling less than the quantity demanded specified in cell C14. Use cells F14 and E25. Copy the formula from cell G14 down the column to cell G24. |
1 |
7 |
In cell H14, by using relative and absolute cell references, calculate the expected marginal benefit corresponding to the quantity demanded specified in cell C14 and the frequency specified in cell D14. Use cells D5, D7, and F14. Copy the formula from cell H14 down the column to cell H24. |
1 |
8 |
In cell I14, by using relative and absolute cell references, calculate the expected marginal cost corresponding to the quantity demanded specified in cell C14 and the frequency specified in cell D14. Use cells D6, D7, and G14. Copy the formula from cell I14 down the column to cell I24. |
1 |
9 |
In cell E27, by using a cell reference, determine the quantity of muffins the bakery should produce each day. Refer to an appropriate cell among C14-C24. |
1 |
10 |
In cell E33, by using relative and absolute cell references, calculate the expected marginal benefit assuming the conditions described in part f. Use cells D7, D29, and F14. Copy the formula from cell E33 down the column to cell E43. |
1 |
11 |
In cell F33, by using relative and absolute cell references, calculate the expected marginal cost assuming the conditions described in part f. Use cells D7, D30, and G14. Copy the formula from cell F33 down the column to cell F43. |
1 |
12 |
In cell E45, by using a cell reference, determine the new quantity of muffins the bakery should produce each day assuming the conditions described in part f. Refer to an appropriate cell among C33-C43. |
1 |
13 |
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. |
0 |
Total Points |
12 |
Created On: 07/05/2019 1 Chapter 14 Inventory Problem (v1)