Computers Unlimited sells microcomputers to universities and colleges on the East Coast and ships them from three distribution warehouses. The firm is able to supply the following numbers of microcomputers to the universities by the beginning of the academic year:

Four universities have ordered microcomputers that must be delivered and installed by the beginning of the academic year:

The shipping and installation costs per microcomputer from each distributor to each university are as follows:

1. To better meet demand at the four universities it supplies Computers Unlimited is considering two alternatives: (1) expand its warehouse at Richmond to a capacity of 600, at a cost equivalent to an additional $6 in handling and shipping per unit; or (2) purchase a new warehouse in Charlotte that can supply 300 units with shipping costs of $19 to Tech, $26 to A & M, $22 to State, and $16 to Central. Which alternative should management select, based solely on transportation costs (i.e., no capital costs)?
2. Computers Unlimited has determined that when it cannot meet the demand for microcomputers at the universities it supplies, the universities tend to purchase microcomputers elsewhere in the future. Thus, the firm has estimated a shortage cost for each microcomputer demanded but not supplied which reflects the loss of future sales and goodwill. These costs for each university are as follows:

Using the shortage cost, compute the total transportation cost and the total shortage cost. Please solve this problem using the EXCEL solver. Please show the steps to set up the EXCEL spreadsheet and solver. Images of the tables, formulas, and solver would be greatly appreciated.