Please solve this in Excel and click on the cells which has formulas and also please upload screen shorts of all excel sheets$($a$,$b$,$c$,$d$,$e$,$f$,$g$,$h$)$

Bill Kemp is an industrial engineer in charge of production planning at Keystone

Products. The company assembles three products from components purchased from outside

suppliers. Production might be limited by market demand, the assembly capacity at several

departments, and the availability of certain critical components.

Market demand during the planning period is limited to $400$ units$/$week of product A$,$

$500$ units$/$week of product B$,$ and $300$ units$/$week of product C$.$ The company has firm contracts

with vendors to supply at least $100$ units$/$week of product A$,150$ units$/$week of product B$,$ and

$100$ units$/$week of product C$.$ The company must produce enough units to meet pre$-$existing

contract, but should not produce more than the units demanded for any product. Profit

contribution has been determined to be $$150/$unit on product A$,$ $$100/$unit on product B$,$ and

$$200/$unit on product C$.$

Product A requires $5$ minutes of assembly time in Shop $1,4$ minutes of assembly time in

Shop $2,$ and $2$ minutes of assembly time in Shop $3.$ Product B requires $4$ minutes in Shop $1,2$

minutes in Shop $2,$ and $6$ minutes in Shop $4.$ Product C requires $3$ minutes in Shop $1,4$ minutes

in Shop $3,$ and $10$ minutes in Shop $5.$ The shops work $5$ days per week. Shop $1$ is able to operate

$12$ hours per day, Shop $2$ is limited to $14$ hours per day, Shop $3$ is limited to $8$ hours per day,

Shop $4$ is limited to $12$ hours per day, and Shop $5$ is limited to $10$ hours per day.

Product A uses a special microchip that is limited to $500$ chips$/$week$.$ We will call this

Microchip X$.$ Product B also uses Microchip X$.$ Product B also uses Microchip Y$,$ which is limited

to $400$ chips$/$week$.$ Product C uses a third chip, Microchip Z$,$ which is limited to $600$ chips$/$week$.$

a$.)$ How many units of each product should be scheduled for production each week, and

what would be the weekly profit?

b$.)$ What constraints are binding? That is$,$ which constraints are at their limit$?$

c$.)$ Should Kemp consider increasing capacity at any of the shops, and if so$,$ which ones?

d$.)$ Should Kemp consider looking for additional sources of any of the three microchips?

If so$,$ which ones?

e$.)$ Should Keystone$$s marketing director be urged to promote any of the products more

aggressively, to raise market demand? If so$,$ which ones?

f$.)$ How would your responses to parts a$.)$ and b$.)$ change if the supply of Microchip X

was increased by $50\%?$

g$.)$ How would the responses to parts a$.)$ and b$.)$ change if$,$ in addition to the increase in

availability of Microchip X$,$ the market demand for products A and C both increased

by $50\%?$

h$.)$ How would the responses to parts a$.)$ and b$.)$ change if$,$ in addition to the increase in

availability of Microchip X and the increase in market demand for products A and C$,$

the capacity of Shop $1$ was increased by $50\%?$