should be solve by using excel solver. please also provide formula as well.

should be solve by using excel solver. please also provide formula as well.

should be solve by using excel solver. please also provide formula as well.

Based on Table 5-14 Annual demand at CoolWipes and Table 5-15 Transportation costs per unit, without using Excel Solver, can you recommend in what cities should the new plant(s) be built for wipes or ointment? Please explain your reasons.

What is the annual cost, including fixed costs, variable costs, and transportation costs, of serving the entire nation from Chicago?

What is the minimal annual cost and in what cities should the plant(s) be built and what product lines should be included? Assume that the Chicago plant will be maintained at its current capacity.

What is the minimal annual cost if Chicago does not need to stay open?

CASE STUDY Designing the Production Network at CoolWipes Matt O'Grady, vice president of supply chain at save the company significant amounts in transportation CoolWipes, thought that his current production and dis- expense in the future. tribution network was not appropriate, given the significant increase in transportation costs over the past few years. Compared to when the company had set up its CoolWipes production facility in Chicago, transportation costs had CoolWipes was founded in the late 1980s and produced increased by a factor of more than four and were baby wipes and diaper ointment. Annual demand for the expected to continue growing in the next few years. A two products was as shown in Table 5-14. At that time, quick decision on building one or more new plants could the company had one factory in Chicago that produced both products for the entire country. The wipes line in the Chicago facility had an annual capacity of 5 million units, an annualized fixed cost of $5 million a year, and a variable cost of $10 per unit. The ointment line in the Chicago facility had an annual capacity of 1 million units, an annualized fixed cost of $1.5 million a year, and a variable cost of $20 per unit. The transportation costs per unit (for both wipes and ointment) are shown in Table 5-15. New Network Options Matt had identified Princeton, New Jersey; Atlanta; and Los Angeles as potential sites for new plants. Each new plant could have a wipes line, an ointment line, or both. A new wipes line had an annual capacity of 2 million units, an annual fixed cost of $2.2 million, and a variable production cost of $10 per unit. A new ointment line had an annual capacity of 1 million units, an annual fixed cost of $1.5 million, and a variable cost of $20 per unit. The current transportation costs per unit are shown in Table 5-15. Table 5-14 Regibnal Annual Demand at CoolWipes (in thousnads) \begin{tabular}{cccccc} Zone & Wipes Demand & Ointment Demand & Zone & Wipes Demand & Ointment Demand \\ \hline Northwest & 500 & 50 & Lower Midwest & 800 & 65 \\ Southwest & 700 & 90 & Northeast & 1,000 & 120 \\ Upper Midwest & 900 & 120 & Southeast & 600 & 70 \\ \hline \end{tabular} Table 5-15 Transportation Costs per Unit \begin{tabular}{ccccccc} \hline & Northwest & Southwest & Upper Midwest & Lower Midwest & Northeast & Southeast \\ \hline Chicago & $6.32 & $6.32 & $3.68 & $4.04 & $5.76 & $5.96 \\ Princeton, NJ & $6.60 & $6.60 & $5.76 & $5.92 & $3.68 & $4.08 \\ Atlanta & $6.72 & $6.48 & $5.92 & $4.08 & $5.14 & $3.64 \\ LA & $4.36 & $3.68 & $6.32 & $6.32 & $6.72 & $6.60 \\ \hline \end{tabular} Cost and Capacity Data (in thousands) \begin{tabular}{ccccccc} \hline & \multicolumn{3}{c}{ Wipes } & & \multicolumn{2}{c}{ Ointment } \\ & Fixed Cost & Variable Cost/Unit & Capacity & Fixed Cost & Variable Cost/Unit & Capacity \\ \hline Chicago & $5,000 & $10 & 5,000 & $1,500 & $20 & 1,000 \\ Princeton, NJ & $2,200 & $10 & 2,000 & $1,500 & $20 & 1,000 \\ Atlanta & $2,200 & $10 & 2,000 & $1,500 & $20 & 1,000 \\ LA & $2,200 & $10 & 2,000 & $1,500 & $20 & 1,000 \\ \hline \end{tabular} Decisions: Plant Open? Wipes Line Open? Ointment Line Yes =1, No =0 Open? Yes =1, No =0 Chicago Princeton, NJ Atlanta LA Decisions: Supply units (in thousands) Costs Wipes Line fixed cost variable cost transportation cost Ointment Line fixed cost variable cost transportation cost total cost