QSO320 Mgmt Science Thru Spreadsheets Problem 5-13 Part A and Part B

I need help with the second portion of this activity (Transshipment). Thanks in advance for any help that you can offer. I don't want just answers please, I need total explanation, perhaps even screenshots as well.

Marc Hernandezs construction firm currently has three projects in progress. Each requires a specific supply of gravel. There are three gravel pits available to provide for Hernandezs needs, but shipping costs differ from location to location.

To From Job 1 Job 2 Job 3 Tonnage Allowance

Central pit $9 $ 8 $ 7 3,000

Rock pit $7 $11 $ 6 4,000

Acme pit $4 $ 3 $12 6,000

Job requirements (tons) (Job1) 2,500 (Job 2) 3,750 (Job 3) 4,850

A. Determine Hernandezs optimal shipping quantities so as to minimize total transportation costs.

B. It is the case that Rock Pit and Central Pit can send gravel by rail to Acme for $1 per ton. Once the gravel is relocated, it can be trucked to the jobs. Reformulate this problem to determine how shipping by rail could reduce the transportation costs for the gravel.

Problem 5-13
Shipments:	To					Flow balance equations
From	Job 1	Job 2	Job 3	Flow out		Location	Flow in	Flow out	Net flow	Sign	RHS
Central				0.0		Central	0	0	0	>=	-3000
Rock				0.0		Rock	0	0	0	>=	-4000
Acme				0.0		Acme	0	0	0	>=	-6000
Flow in	0.0	0.0	0.0			Job 1	0	0.0	0	=	2500
						Job 2	0	0.0	0	=	3750
Unit costs:	To					Job 3	0	0.0	0	=	4850
From	Job 1	Job 2	Job 3
Central	$9	$8	$7
Rock	$7	$11	$6
Acme	$4	$3	$12
Total cost =	$0		<--- Minimize total transportation costs. Formula = SUMPRODUCT(B5:D7,B12:D14)
Note:
Once all values are entered in the appropriate shaded areas, go to the DATA tab on the Excel sheet ribbon, click on the Data Analysis Group, and then choose Solver. Click SOLVE to run Excel's Solver add-in to obtain the optimized solution. Note that if Solver is not on the DATA tab, refer to the Help file (Solver) for instructions or pages 569571 of Balakrishnan (2013) Managerial Decision Modeling With Spreadsheets. For more information on entering information in Solver, refer to pages 4449 of Balakrishnan (2013). To learn more about how to set up and solve linear programming (LP) problems, refer to pages 4051 of Balakrishnan (2013). For the changing variable cells (yellow shaded), the initial entries in the cells can be blank or any value of your choice based on the given constraints.

Problem 5-13 (Transshipment)
Shipments:	To						Flow balance equations
From	Job 1	Job 2	Job 3	Acme	Flow out		Location	Flow in	Flow out	Net flow	Sign	RHS
Central					0.0		Central	0	0	0	>=	-3000
Rock					0.0		Rock	0	0	0	>=	-4000
Acme					0.0		Acme	0.0	0	0.0	>=	-6000
Flow in	0.0	0.0	0.0	0.0			Job 1	0	0	0	=	2500
							Job 2	0	0	0	=	3750
Unit costs:	To						Job 3	0	0	0	=	4850
From	Job 1	Job 2	Job 3	Acme
Central	$9	$8	$7	$1
Rock	$7	$11	$6	$1
Acme	$4	$3	$12	$0
Total cost =	$0
Note:
Once all values are entered in the appropriate shaded areas, go to the DATA tab on the Excel sheet ribbon, click on the Data Analysis Group, and then choose Solver. Click SOLVE to run Excel's Solver add-in to obtain the optimized solution. Note that if Solver is not on the DATA tab, refer to the Help file (Solver) for instructions or pages 569571 of Balakrishnan (2013) Managerial Decision Modeling With Spreadsheets. For more information on entering information in Solver, refer to pages 4449 of Balakrishnan (2013). To learn more about how to set up and solve linear programming (LP) problems, refer to pages 4051 of Balakrishnan (2013). For the changing variable cells (yellow shaded), the initial entries in the cells can be blank or any value of your choice based on the given constraints.