Exercise 1 Solver Resource Allocation Problem In this exercise you are the supply chain manager for a company that sells exercise equipment. You have created a model to allocate shipping resources (trucks and tractor-trailers) to each store so that you can ensure you are fulfilling your stores' inventory needs. Your goalisto ensure that each store's demand for three products is met while minimizing shipping costs. Task 1-create the necessary formulas to make the model work Your first task is to create the necessary formulas to make the model work. The cells that require formulas are shaded light blue. The formulas you must create are listed below: Volume required for all products is equal to the sum total of the demand for each product multiplied by its volume (different products have different volumes) Volume for trucks is equal to the number of trucks assigned to that store multiplied by the volume for a truck. Volume for tractor-trailers works the same way Total vehicle capacity is sum total of volume for trucks and volume for tractor-trailers. Percent of capacity utilized is equal to the volume required divided by the total vehicle capacity shipping cost is equal to the product of operating cost per mile for trucks, number of trucks assigned, and miles plus the product of operating cost per mile for tractor-trailers, number of tractor-trailers assigned, and miles The Totals row is simple the sum of each column with the exception of vehicle capacity utilized which continues to be a ratio Exercise 1 Solver Resource Allocation Problem In this exercise you are the supply chain manager for a company that sells exercise equipment. You have created a model to allocate shipping resources (trucks and tractor-trailers) to each store so that you can ensure you are fulfilling your stores' inventory needs. Your goalisto ensure that each store's demand for three products is met while minimizing shipping costs. Task 1-create the necessary formulas to make the model work Your first task is to create the necessary formulas to make the model work. The cells that require formulas are shaded light blue. The formulas you must create are listed below: Volume required for all products is equal to the sum total of the demand for each product multiplied by its volume (different products have different volumes) Volume for trucks is equal to the number of trucks assigned to that store multiplied by the volume for a truck. Volume for tractor-trailers works the same way Total vehicle capacity is sum total of volume for trucks and volume for tractor-trailers. Percent of capacity utilized is equal to the volume required divided by the total vehicle capacity shipping cost is equal to the product of operating cost per mile for trucks, number of trucks assigned, and miles plus the product of operating cost per mile for tractor-trailers, number of tractor-trailers assigned, and miles The Totals row is simple the sum of each column with the exception of vehicle capacity utilized which continues to be a ratio
