Project 5Tom’s, Inc, peoduces varisus Merican food products and sells them to Western Foods, a…

Project 5
Tom’s, Inc, peoduces varisus Merican food products and sells them to Western Foods, a chain of gocery stores located in Texas and New Mevico. Tom’s, Inc, makes two salsa products: Westem Foods Salsa and Merico City Salsa. Essentially, the two products have different blends of whole tomatoes, tomato sauce, and tomute pante. The Western Foods Salsa is a Blend of 50% whole tomatoes, 30% tomato sauct, and 20% tomato paste. The Mexicp C7y Salsa, which Mas a thicker and chunkier conslstency, consists of 70% whole tomatoes, 10% tomato sauct, and 20% tomato paste. Exch jur of 5 alsa produced weighs 10 ounces. For the current production period, Tom’s, Inc.can purchase up to 250 pounds of whole tomatoes, 130 pounds of tomato savse, and 100 pounds of tomats paste, the price per pound for these ingedients is $0.96, 50.04 , and $0.56, respectively. The cost of the spices and the other ingedients is approsimately $0.10 per jer. Tomis, Inc., buys empty dass jars for $0.02 each, and labeling and filing costs are estimated to be $0.03 for each jer of salos produced. Tom’s contract With Westem Foods results in sales revenue of $1.64 for each jar of Western Foods $ ass and $1.93 for each jar of Merico City Salsa.
Letting W = jars of Westem Foods Salsa
M = jars of Mexice City Salsa
Max 1W 1.25M s.t.
5W + 7M ≤ 4480 (Whole tomatoes)
3W + 1M ≤ 2080 (Tomato sauce)
2W + 2M ≤ 1600 (Tomato paste)
W, M ≥ 0
Tom’s lint Objective Coefficient Constraints Whole Tomanoes Tomato Sauce Tomato Paste Proft Constrints Jars of Westem Foods Salsa Jars of Medco City Salsa 1.25 Whole Tomatoes Tomato Sauct Tomato Pastelas of Westem Foods Salsa
Jars of Westen Foods Salsa, ,560 ,560
Jars of Medco Cily Salsa 240 300 860 R H S L H S 4480 2080 1920 1600
Tomato Sauce, Tomato Paste, Whole Tomatoes entries and other model coefficients as presented in the problem statement.
Spreadsheet Model with Solver setup 2%
User Interface (be sure to set Object Properties as appropriate) 2%
VBA Code to link Model and Interface 2%
VBA Code for Formatting 2%
VBA Code for Error Trapping 2%