Spreadsheet Analysis of Demand and Supply for Sunbest Orange Juice (Case Study)
A. Set up a spreadsheet to illustrate the
effects of changing economic assumptions on the demand for Sunbest orange
juice. Use the demand function to
calculate demand based on three different underlying assumptions concerning
changes in the operating environment.
First, assume that all demand factors change in unison from levels
indicated in Optimistic Scenario #1 to the levels indicated in Pessimistic
Scenario #10. Second, fix all demand
factors except the price of Sunbest at Scenario #6 levels, and then calculate
the quantity demanded at each scenario price level. Finally, fix all demand factors except temperature
at Scenario #6 levels, and then calculate demand at each scenario temperature
level.
B. Set up a spreadsheet to illustrate the
effects of changing economic assumptions on the supply of Sunbest orange
juice. Use the supply function to
calculate supply based on three different underlying assumptions concerning
changes in the operating environment.
First, assume that all supply factors change in unison from levels
indicated in Optimistic Scenario #1 to the levels indicated in Pessimistic
Scenario #10. Second, fix all supply
factors except the price of Sunbest at Scenario #6 levels, and then calculate
the quantity supplied at each scenario price level. Finally, fix all supply factors except
temperature at Scenario #6 levels, and then calculate supply at each scenario
temperature level.
C. Set up a spreadsheet to illustrate the
effect of changing economic assumptions on the surplus or shortage of Sunbest
orange juice that results from each scenario detailed in part A and part
B. Which operating scenario results in
market equilibrium?
CASE
STUDY SOLUTION
A. A
spreadsheet that illustrates the effects of changing economic assumptions on
the demand for Sunbest orange juice is as follows:
Operating
Environment for Demand
|
Demand
if All Factors Change (QD)
|
Quantity
Demanded if Price Chgs. Only(QD)
|
Demand
if Temp. Chgs. Only (QD)
|
Optimistic
Scenario 1
|
13,062,500
|
0
|
25,062,500
|
2
|
15,450,000
|
5,000,000
|
25,050,000
|
3
|
17,837,500
|
10,000,000
|
25,037,500
|
4
|
20,225,000
|
15,000,000
|
25,025,000
|
5
|
22,612,500
|
20,000,000
|
25,012,500
|
6
|
25,000,000
|
25,000,000
|
25,000,000
|
7
|
27,387,500
|
30,000,000
|
24,987,500
|
8
|
29,775,000
|
35,000,000
|
24,975,000
|
9
|
32,162,500
|
40,000,000
|
24,962,500
|
Pessimistic Scenario 10
|
34,550,000
|
45,000,000
|
24,950,000
|
B. A
spreadsheet that depicts the consequence of changing economic assumptions on
the supply of Sunbest orange juice is as follows:
Operating
Environment for Supply
|
Supply
if All Factors Change (QS)
|
Quantity
Supplied if Price Chgs. Only(QS)
|
Supply
if Temp. Chgs. Only (QS)
|
Optimistic
Scenario 1
|
33,237,500
|
33,000,000
|
24,812,500
|
2
|
31,590,000
|
31,400,000
|
31,400,000
|
3
|
29,942,500
|
29,800,000
|
29,800,000
|
4
|
28,295,000
|
28,200,000
|
28,200,000
|
5
|
26,647,500
|
26,600,000
|
26,600,000
|
6
|
25,000,000
|
25,000,000
|
25,000,000
|
7
|
23,352,500
|
23,400,000
|
23,400,000
|
8
|
21,705,000
|
21,800,000
|
21,800,000
|
9
|
20,057,500
|
20,200,000
|
20,200,000
|
Pessimistic Scenario 10
|
18,410,000
|
18,600,000
|
18,600,000
|
C. A
spreadsheet illustration of the effect of changing economic assumptions on the
surplus and shortage of Sunbest is:
Operating
Environment for Demand and Supply
|
Surplus
or Shortage All Factors Change
|
Surplus
or Shortage Price Chg. Only
|
Surplus
or Shortage Temp. Chg. Only
|
Optimistic
Scenario 1
|
20,175,000
|
33,000,000
|
-250,000
|
2
|
16,140,000
|
26,400,000
|
6,350,000
|
3
|
12,105,000
|
19,800,000
|
4,762,500
|
4
|
8,070,000
|
13,200,000
|
3,175,000
|
5
|
4,035,000
|
6,600,000
|
1,587,500
|
6
|
0
|
0
|
0
|
7
|
-4,035,000
|
-6,600,000
|
-1,587,500
|
8
|
-8,070,000
|
-13,200,000
|
-3,175,000
|
9
|
-12,105,000
|
-19,800,000
|
-4,762,500
|
Pessimistic Scenario 10
|
-16,140,000
|
-26,400,000
|
-6,350,000
|
As
is obvious from this spreadsheet, market equilibrium occurs under operating
Scenario #6.
