BUAN $571$: Introduction to Business Analytics

Analytics Assignment Week $6$: Linear Programming$--$Automation of Solver via VBA

Study the transportation model and its solution via the Excel interface $($TP$.$xlsm$)$ posted under this week$$s Learning Materials & Resources, along with the videos on how to automate Solver through VBA code. As practice, solve this transportation problem using VBA code. For the assignment, do the following: The current amount of Supply $($i$.$e$.$ Capacity$)$ at Dallas is $30.$ Suppose there is an opportunity to outsource additional capacity at this location. The question is how much additional capacity should we target? For example, should we double capacity to $60,$ triple it to $90,$ or choose some other level? To answer this question, you will solve a series of transportation problems inside a VBA loop with the Dallas capacity varied from $30$ to $90$ in steps of $10,$ i$.$e$.30,40,50,...90.$ The results should be automatically entered by VBA on the spreadsheet as follows:

Dallas Supply Total Cost

$30$

$40$

$50$

$60$

$70$

$80$

$90$

Based on the above results, how much additional capacity would you recommend at Dallas?

I am confused what cell references to implement.