Given this case, please answer each question in a seperate excel sheet and please provide any formulas$/$steps that you took to solve each qyestion. Thank you. The questions are provied in the case but will provide here as well:

$1.$ Which option delivers the maximum profit for the supply chain: Sandra$$s plan, Bill$$s plan, or no promotion plan at all? Assume ending inventory of $0.$

$2.$ How does the answer change if a discount of $$10$ must be given to reach the same level of impact that the $$5$ discount received?

$3.$ Suppose Sandra$$s fears about increasing outsourcing costs come to fruition and the cost rises to $$22/$unit for subcontracting. Does this change the decision when the discount is $$5?$

It is late June, and Sandra Huchim, head of operations at

Mintendo, and Bill Smith, head of sales of We $"$R$"$ Toys,

are about to get together to discuss production and mar$-$

keting plans for the next $6$ months. Mintendo is the man$-$

ufacturer of the popular Game Girl handheld electronic

game that is sold exclusively through We $"$R$"$ Toys retail

stores. The second half of the year is critical to Game

Girl's success, because a majority of its sales occur dur$-$

ing the holiday shopping period.

Sandra is worried about the impact that the upcom$-$

ing holiday surge in demand will have on her production

line. Costs to subcontract assembly of the Game Girls

are expected to increase, and she has been trying to keep

costs down, given that her bonus depends on the level of

production costs.

Bill is worried about competing toy stores gaining

share in the handheld electronic game market during the

Christmas buying season. He has seen many companies

lose their share by failing to keep prices in line with the

performance of their products. He would like to maxi$-$

mize the Game Girl market share in the handheld elec$-$

tronic game market.

Both Sandra's and Bill's teams produce a joint

forecast of demand over the next six months, as shown

in Table $9-7.$

We $"$R$"$ Toys sells Game Girls for $$50$ apiece. At the

end of June, the company has an inventory of $50,000$

Game Girls. Capacity of the production facility is set

purely by the number of workers assembling the Game

Girls. At the end of June, the company has a workforce of

$300$ employees, each of whom works $8$ hours of regular

time at $$\frac{15}{?}$ hour for $20$ days each month. Work rules

require that no employee work more than $40$ hours of over$-$

time per month. The various costs are shown in Table $9-8.$

TABLE $9-8$ Costs for Mintendo$/$We $"$R$"$ Toys

Sandra, concerned about controlling costs during

the periods of surging demand over the holidays, pro$-$

poses to Bill that the price be lowered by $$5$ for the

month of September. This would likely increase Septem$-$

ber's demand by $50$ percent due to new customers being

attracted to Game Girl. In addition, $30$ percent of each of

the following two months of demand would occur in

September as forward buys. She believes strongly that

this leveling of demand will help the company.

Bill counters with the idea of offering the same

promotion in November, during the heart of the buying

season. In this case, the promotion increases Novem$-$

ber's demand by $50$ percent, owing to new customers

being attracted to Game Girl. Additionally, $30$ percent of

December's demand would occur in November as for$-$

ward buying. Bill wants to increase revenue and sees no

better way to do this than to offer a promotion during the

peak season.

Questions

Which option delivers the maximum profit for the supply

chain: Sandra's plan, Bill's plan, or no promotion plan at

all? Assume ending inventory of $0.$

How does the answer change if a discount of $$10$ must be

given to reach the same level of impact that the $$5$ discount

received?

Suppose Sandra's fears about increasing outsourcing costs

come to fruition and the cost rises to $$\frac{22}{?}$ unit for subcon$-$

tracting. Does this change the decision when the discount

is $$5?$Suppose Sandra$$s fears about increasing outsourcing costs come to fruition and the cost rises to $$22/$unit for subcontracting. Does this change the decision when the discount is $$5?$