This problem uses the Transportation Problem model to address whether demand that is

predicted to grow over the next three years should be met by expanding the Dallas supply point

$($origin$)$ in year $3$ or year $4.$

Year $1$ is the current year and the following table provides unit cost, supply and demand data in

year $1$ for $4$ supply points $($origins$)$ and $6$ destinations. The total supply is currently $2200$ which

year $1$ for $4$ supply points $($origins$)$ and $6$ destinations.

exceeds the current total demand of $1550.$ Suppose the cost data will stay the same over the next

either year $3$ or $4$ by expanding the Dallas supply point. The supply at the three other origins $|$

$($Chicago$,$ San Diego, Toronto$)$ does not change.

In year $1,$ the demand is in the last row above $($totaling $1550),$ and the demand will grow by $100$

units at each destination except Montreal for each of the next $4$ years.

Thus, in year $2$ the demand totals $2050$ and is:

In year $3$ the demand totals $2550$ and is:

In year $4$ the demand totals $3050$ and is:

If nothing is done, the increasing demand will exceed the current supply in year $3.$ When the

total demand exceeds the total supply, then there is unmet demand which incurs a cost of $$5/$unit

To handle the increasing demand, the company is considering two expansion options at Dallas

Option $1$ expands the Dallas origin to $800$ in year $3$ and option $2$ expands the Dallas origin to $800$

in year $4.$ There is a one$-$time cost for the expansion at Dallas in year $3$ or year $4$ of $$15,000.$

Fill in the tables of costs below for option $1.$ For each row $($year$),$ solve a Transportation

Problem using the appropriate supply can demand to determine the minimum transportation

Problem using the appropriate supply can demand to determine the minimum transportation

Then, sum each row for the total cost per year $($column $6).$

Fill in the tables of costs below for option $2$ similar to the above, but using the appropriate

supply for expansion and year $4.$ Note that rows $1$ and $2$ are the same for both tables

Option $2$: Expand Dallas in year $4$

Based on the sum of total costs over the four years, which option is best?

Use Excel with the NPV formula to calculate the net present value $($NPV$)$ with a $2\%$ discount

rate for each option. Based on this, which option is best?

Option $1NPV$ with $2\%$ discount rate $=$

Option $2$ NPV with $2\%$ discount rate $=$

Which option is best?

Use Excel with the NPV formula to calculate the net present value $($NPV$)$ with a $10\%$

discount rate for each option. Based on this, which option is best?

Option $1$ NPV with $10\%$ discount rate $=$

Option $2$ NPV with $10\%$ discount rate $=$

Which option is best?

What $\%$ discount rate would make the two options have an equivalent NPV$?$