ALL SHADED BOXES MUST BE FILLED IN USING EXCEL

$2$

$*$ Note that the decision variable, Annual Number of Joint Orders, should be initialized at $1$ for better SOLVER performance. A SOLVER constraint should be that N is an integer. b$.$ Calculate the total Annual Costs for the joint orders.

c$.$ Use SOLVER to determine the optimal number of joint orders per year, $\backslash ($ N$^\{\backslash$star$\}\backslash ),$ that will minimize total cost. Hint: Initialize to $1$ for optimal solver performance. Stevens Company purchases components from four suppliers. Components purchased from Supplier A are priced at $\backslash (\backslash$$ $5\backslash )$ each and used at the rate of $49,000$ units per year. Components purchased from Supplier B are priced at $\backslash$$$3$ each and are used at the rate of $31,000$ units per year. Components purchased from Supplier $\backslash ($ C $\backslash )$ are priced at $\backslash (\backslash$$ $12\backslash )$ each and used at the rate of $4,200$ units per year. Components purchased from Supplier D are priced at $\backslash$$$29$ each and used at a rate of $2,900$ units per year. Annual holding cost is $20\backslash \%($h $=.2).$

The trucking company charges a fixed cost of $\backslash (\backslash$$ $1,600\backslash )$ for the truck with an additional charge of $\backslash (\backslash$$ $175\backslash )$ for each stop. Thus, if Stevens Company asks for a pickup from only one supplier, the trucking company charges $\backslash$$$1775$; and from three suppliers, it charges $\backslash$$$2125(\backslash$$$1,600+\backslash$$$175+\backslash$$$175+\backslash$$$175),$ etc.

$1.(15$ points$)$ Currently, Stevens Company purchases a separate truckload from each supplier.

a$.$ Determine the EOQ and Cycle Inventory for each supplier. Note that you should NOT use Solver here.

b$.$ Calculate Annual Holding Cost, Annual Material Cost, and Annual Ordering Cost $($the truck cost including stops$)$ for each supplier. Fill in the totals with formulas where indicated.

c$.$ Calculate total annual cost for all components from all suppliers.

Hint: Orders per year can be a fraction of year

$2.(15$ points$)$ As part of its JIT drive, Stevens Company is considering aggregating purchases from the four suppliers, so that all orders would be a number $\backslash ($ N$^\{*\}\backslash )$ of joint orders during the year. In this scenario, each order would have the same mix of components, so that the truck for each order holds Annual$-$Demand$/\backslash ($ N$^\{*\}\backslash )$ units of each component.

a$.$ Fill in formulas to calculate Order Size, Cycle Inventory, Annual Holding Cost and Annual Material Cost for each supplier. Fill in the totals with formulas where indicated.