Reference no: EM133575492

The office manager of a large New York-based firm needs to replace the aging and out of-style office furniture in their offices. The firm has decided to purchase desk/chair/credenza furniture sets for all 2,000 offices in New York. The company has received bids from four different furniture companies who are willing to supply the furniture sets, as follows:

Carolina Woodworks has bid to deliver up to 1,000 furniture sets at a cost of $2,500 per set and with a one-time charge of $10,000. • Niagara Millworks has bid to deliver up to 1,200 furniture sets at a cost of $2,450 per set and with a one-time charge of $17,500. • Adirondack Furnishing Designs has bid to deliver up to 800 furniture sets at a cost of $2,510 per set with no additional charges. • Lancaster Artisan Company has bid to deliver up to 1,100 furniture sets at a cost of $2,470 per set and with a one-time charge of $12,500.

Formulate a mixed integer optimization model that could be used to determine how many furniture sets to purchase from each of the four potential suppliers in order to minimize cost.

use excel solver to find the optimal solution. please submit the spreadsheet and answer the following question

a. how many furniture sets should we order from each supplier?

b. what is the total cost for the optimal solution.