Create a Linear Optimization mathematical model based on a specified goal and a set of predetermined market values.

In order to satisfy this part of the project, you will review the scenario and the parameters supplied in an Excel workbook.

You will formulate an Excel-based spreadsheet model. Finally, you'll specify cells or cell ranges necessary to find an optimized solution using Solver.

Scenario: You are a confectionary manufacturer reviewing bids for the retail packaging you need for your products next quarter under a bid process. You have received one to five bids from each of six suppliers on 10 packaging products.

There are 18 bids in total, reflected in two tables in a supplied worksheet. The first table shows, for each bid, the maximum production commitment for each type of packaging. The second table shows the unit cost for each type of packaging.

In addition to these two tables, you have an array (a one-dimensional array) showing your minimum needs for each type of packaging. You will also see some cells that are shaded but have been left empty.

Step 2:

Complete a spreadsheet model of this problem in Excel. You will use the two matrices, the minimum needs array, and the blank, shaded cells to complete your model. Be sure to open the Solver dialog box and specify the objective, decision variables, and all constraints before saving the workbook.

UNIT COSTS (specified by Suppliers as part of bid) Bid#1 Bid#2 Bid#3 Bid#4 Bid#5 Bid#6 Bid#7 Bid#8 Bid#9 Bid# 10 Bid# 11 Bid# 12 Bid# 13 Bid# 14 Bid# 15 Bid# 16 Bid# 17 Bid# 18 PRODUCT 1 0 0 0.1 0.3 0.2 0 0 0.5 0 0 0 0.6 0 0.9 0.3 0 0.3 0 PRODUCT 2 0 0.4 0 0 0.6 0 0 0 0 0 0 0.5 0.9 0 0 0.3 0.7 0.9 PRODUCT 3 0 0 0 0.5 0 0 0 0.2 0.7 0 0 0 0 0.7 0 0 0 0 PRODUCT 4 0 0 0 0 0 0 0.2 0 0.7 0.4 0 0 0 0 0 1 0 0 PRODUCT 5 0.1 0 0 0.5 0 0.4 0 0.6 0 0 0.7 0 0 0.6 0 0.5 0 0 PRODUCT 6 0 0.3 0.1 0 0 0 0.6 0 0 0.3 0 0 0 0.3 0 1 0 0 PRODUCT 7 0.8 0.2 0 0.7 0.5 0 0.9 0 0 0.3 0 0.2 0 0.8 0 0.1 0 0 PRODUCT 8 0.9 0 0.7 0.1 0.9 0 0.6 0.9 0 0 0.1 0.8 0 0 0 0 0 0 PRODUCT 9 0.3 0.8 0 0.8 0 0 0 0.7 0 0.9 0 0.9 0 0 0 0.4 0 0 PRODUCT 10 0 0 0.1 0 0 0 0.7 0 0 0.6 0 0.5 0 0 0 0.7 0 0 D G H M O Q R S W MINIMUM UNITS BIDS (offered by Suppliers) NEEDED FOR SUPPLIER # 4 5 5 6 4 1 6 5 2 1 4 2 1 3 4 5 5 EACH PRODUCT Bid# 1 Bid# 2 Bid# 3 Bid# 4 Bid# 5 Bid# 6 Bid# 7 Bid# 8 Bid# 9 Bid# 10 Bid# 11 Bid# 12 Bid# 13 Bid# 14 Bid# 15 Bid# 16 Bid# 17 Bid# 18 PRODUCT 1 10000 PRODUCT 1 0 8000 6000 6000 0 0 3000 0 0 0 3000 0 6000 9000 0 2000 0 PRODUCT 2 9000 PRODUCT 2 0 7000 0 9000 0 0 0 0 8000 5000 0 0 7000 3000 2000 PRODUCT 3 2000 PRODUCT 3 0 0 0 1000 0 0 0 7000 5000 0 0 0 8000 0 0 0 PRODUCT 4 3000 PRODUCT 4 0 0 O 0 0 0 5000 0 4000 2000 0 0 0 0 7000 4000 0 0 PRODUCT 5 5000 PRODUCT 5 9000 0 0 2000 0 8000 0 9000 0 0 6000 0 0 2000 O 7000 0 PRODUCT 6 5000 PRODUCT 6 9000 2000 0 0 1000 0 0 3000 0 0 0 1000 0 7000 0 9000 PRODUCT 7 11000 PRODUCT 7 3000 2000 3000 8000 0 9000 0 5000 0 3000 0 8000 7000 0 0 PRODUCT 8 2000 PRODUCT 8 7000 0 3000 2000 7000 5000 5000 0 4000 3000 0 0 0 0 PRODUCT 9 PRODUCT 9 7000 2000 1000 4000 7000 0 2000 7000 ooo 8000 PRODUCT 10 1000 PRODUCT 10 0 0 9000 0 0 0 6000 0 1000 0 400 0 0 1000 0