MKTG$4313$ Module $6$ Assignment $-$ Using

Excel Solver Assignment

$1.$ Joe works for a logistics company that wants to minimize total transportation costs. He wants to meet each retailer's demands but can't change the quantity available from each factory.

He needs to regularly transport units from the $2$ different factories to $4$ different retailers. Factory $1$ has $60,000$ units and Factory $2$ has $80,000$ units.

Set up your Excel File showing shipping costs and then the number of products that should ship from each factory.

$\backslash$begin$\{$tabular$\}\{|$c$|$c$|$c$|$c$|$c$|\}$

$\backslash$hline $\backslash$multicolumn$\{2\}\{|$l$|\}\{$Cost of Shipping$\}$ & & & $\backslash \backslash$

$\backslash$hline & Retail customer & Retail customer B & Retail customer C & Retail customer D $\backslash \backslash$

$\backslash$hline Factory

$1$ & $\backslash$$$1\mathrm{.}00$ & $\backslash$$$3\mathrm{.}00$ & $\backslash$$$0\mathrm{.}50$ & $\backslash$$$4\mathrm{.}00\backslash \backslash$

$\backslash$hline Factory

$\backslash [$

$2$

$\backslash ]$ & $\backslash$$$2\mathrm{.}50$ & $\backslash$$$5\mathrm{.}00$ & $\backslash$$$1\mathrm{.}50$ & $\backslash$$$2\mathrm{.}50\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

Use the data$/$Excel solver to help Joe make a decision.

Calculate the optimal quantity of goods to deliver to each customer from each warehouse.

After you use the Solver, save your solution in a worksheet called transportation.

What should Joe tell his managers he should do$?$

$2.$ Move to Worksheet $2$ and use the Solver to make the next decision.

Joe's boss is considering adding new technology to make the process more efficient.

$\backslash$begin$\{$tabular$\}\{|$l$|$l$|\}$

$\backslash$hline Cost of technology & $\backslash (\backslash$$ $40,000\backslash )\backslash \backslash$

$\backslash$hline $\backslash$begin$\{$tabular$\}\{$l$\}$

Projected increase in $\backslash \backslash$

order processed

$\backslash$end$\{$tabular$\}$ & $50\backslash \backslash$

$\backslash$hline $\backslash$begin$\{$tabular$\}\{$l$\}$

Cost added to each $\backslash \backslash$

order shipped

$\backslash$end$\{$tabular$\}$ & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

Use the Solver to determine the cost that needs to be added to each order. Set the objective to $12$ for the number of months to pay for the technology.

What should Joe tell his managers is the cost that needs to be added to each order shipped?

After you use the Solver, save your solution in a worksheet A

$3.$ Move to Worksheet $3$ and use the Solver to make the next decision. Use the same data as in problem $2,$ but assume you want to pay for the technology in a shorter time frame.

What should Joe tell his managers is the cost that needs to be added to each order shipped?

After you use the Solver, save your solution in a worksheet B$.$