Question

You are employed by Symphony International which sells gourmet chocolate assortments door-to-door. Every morning you have to purchase your inventory of assortments before selling them. However, you never know what the daily demand is going to be and any unsold boxes can be sold at a discount to a local store. To help you determine how much you should purchase in the morning, you have started to develop the following spreadsheet model which will calculate your daily profit given any initial purchase quantity and daily demand. All prices are given as per assortment. А B с D 1 2 DATA 3 4 Selling Price Purchasing Cost Discount Price 25 14 5 6 7 7 MODEL co 09 10 11 Demand Purchase Quantity 12 13 Quantity Sold Surplus Quantity 14 15 Profit 16 17 Based on this spreadsheet model, which formula should be used to determine the Surplus Quantity (cell C14)?

Answer 1

The formula to calculate Surplus Quantity in cell C14 is '=MAX(C11-C10,0)'. It considers the Purchase Quantity and the Demand to ensure that the surplus cannot be negative.

Step-by-step explanation:

To calculate the Surplus Quantity in cell C14, we need to use a formula that considers both the Purchase Quantity and the Demand. The Surplus Quantity represents the number of chocolate assortments left unsold after meeting the daily demand. The appropriate formula to determine the Surplus Quantity would be the Purchase Quantity minus the Demand (or Quantity Sold), but with a constraint that it cannot be negative.

Surplus Quantity would be calculated as: =MAX(C11-C10,0)

This formula takes the value in cell C11 (which represents Purchase Quantity) and subtracts the value in cell C10 (which represents Demand). If the result is a negative number, it will return 0 since we cannot have a negative quantity of chocolate assortments. The MAX function ensures that the surplus is never less than zero.