Vinader Jewelry is well-known for its line of friendship bracelets. The company is planning to purchase leather bands to make a bracelet that will be the focus of the summer 2022 campaign.

Production manager Su Simpson is trying to determine the number of leather bands to order from their supplier (assuming that all of the bands ordered will be used to produce this particular bracelet.)

The supplier gives the firm a quantity discount on the band so the cost will change depending on the order size as indicated in the table below.

Bands ordered	Cost per Band
1 to 299	$15
300 to 499	$13
500 to 799	$11
800+	$9

Additional variable costs for materials and labor are estimated as $8.50 per bracelet.

The summer season usually runs from May to September and the bracelets will be sold during those months for $35 each. After September they will be sold from the company’s website sale page for $20 each.

The cost for placing the order with the supplier is $120.

a. Create a profit model for the company so that Su can investigate different Band Order Quantities vs potential Demand during the season.

(You must use IF and VLOOKUP functions)

b. Create a two-way table (use Excel Data/What-if/Data Table) to show the sensitivity of Profit to changes in Demand during Season and Band Order Quantity.

• Put Demand as the row, Order Quantity as the column.
• Use the values 100, 200, 300, 400, and 500 for both.

Answer rating: 100% (QA)

Answer Profit model is built in Excel as follows EXCEL FO... View the full answer