Question

Inventory

.

Orders

Warranty Types

You own a small Computer Retailing company that sells computer systems. You have created a spreadsheet that stores basic sales information.

Inventory: Lists the computer & upgrade costs for each system identified by product number and product name.

Order: Lists basic customer information such as, their name, computer purchased, and warranty cost (if chosen) by the customer.

Warranty Types: Lists the warranty costs. The cost of the warranty is based on the type of warranty chosen and the total computer cost in column G of the Inventory worksheet, (Computer +Upgrades). The cell range B2:E4 has been named Warranty.

The warranty costs are based on whether or not the customer purchased the warranty and the cost of the computer as follows.

If the customer purchased the Extended 3 Year Warranty Only

If the cost of the cost of the computer is less than $1,000, the cost of the warranty is $25. If the cost of the computer is greater than or equal to $1,000 and less than $2,000, the cost of the warranty is $35. If the cost of the computer is greater than or equal to $2,000 and less than $5,000, the cost of the warranty is $45. If the cost of the computer is greater than or equal to $5,000, the cost of the warranty is $60.

If the customer purchased the Extended 3 Year Warranty and Accidental Damage or Theft

If the cost of the computer is less than $1,000, the cost of the warranty is $40. If the cost of the computer is greater than or equal to $1,000 and less than $2,000, the cost of the warranty is $55. If the cost of the computer is greater than or equal to $2,000 and less than $5,000, the cost of the warranty is $70. If the cost of the computer is greater than or equal to $5,000, the cost of the warranty is $80.

1. Write an Excel formula in cell Orders!D2, which can be copied down the column to determine the corresponding computer name based on the product number in the Inventory worksheet. If the product number does not exist in the inventory, display the word, “INVALID”.

2. Write an Excel formula in cell Orders!G2, which can be copied down the column, to determine the corresponding computer cost based on the product number in the Inventory worksheet. (Assume all clients will purchase the computer and the associated upgrades.) If the product number does not exist in the inventory, display the word, “INVALID”.

3. Write an Excel formula in cell Orders!H2, which can be copied down the column, to determine the warranty cost of the computer based on the warranty type (if chosen) and the cost of the computer. If the product number does not exist in the inventory, display a 0 in the cell. (Use the named range Warranty in your formula.) Note: If the customer chooses to purchase a warranty, they can only select one of the options.

4.   Write an Excel formula in cell Orders!I2, which can be copied down the column, to determine the total cost of the purchase. (Total Computer Cost + Warranty)

Answer 1

the data is updated as per the conditions provided.

Inventory

Warrenty

Order

Conditions

1)

2)

3)

4)