Excel Quantitative Methods for Bay City Movers: construct Linear programming model

Instructions: (1) The Microsoft Excel spreadsheet using Solver to find out the optimal solution. Include the algebraic equations for the problem if asked in the spreadsheet.

Bay City Movers is a local company that specializes in intercity moves. In the business plan submitted to its investors, Bay City has committed itself to a total trucking capacity of at least 42 tons.

The company is in the process of replacing its entire fleet of trucks with 1-ton pickup trucks and 2.5 ton moving van-type trucks. The 1-ton pickup trucks will be manned by one worker, whereas the large vans will utilize a total of four workers for larger moves.

Bay City Movers currently employs 63 workers and has facilities for at most 50 trucks. Pickup trucks cost the company $24,000 each; the moving vans cost $60,000 each. The company wishes to make the minimum investment of capital that will provide a trucking capacity of at least 42 tons while not requiring any additional workers or trucking facilities.

a) Construct, in equation form, the linear programming model that will determine the optimal number of pickup trucks and moving vans Bay City Movers should purchase.

b) Solve this problem using Solver.