MODULE 5 HOMEWORK ASSIGNMENT
(45 points)
There are two (2) separate scenarios in this assignment. Data given in each scenario is shaded gray. You
will be providing proper formulas or functions for the remaining data in the unshaded cells by answering
the questions using cell references from each scenario’s set of worksheets. You will type your answers in
this document. There are no Excel files provided for this assignment. Note that once you have
completed a question, you may use the results of that question in subsequent problems. Remember to
start all formulas or functions with an equal (=) sign and to always use cell references and functions
where possible. Only use a $ if necessary when copying formulas down or across. Also, used named
ranges where possible. Treat each scenario separately and answer the Meet session question at the end
of this assignment.
Scenario One
You own a small Computer Retailing company that sells computer systems. You have created a
spreadsheet that stores basic sales information.
Inventory worksheet: Lists the computer base price & upgrade costs for each system identified by
product number and computer (product) name.
Orders worksheet: Lists basic customer information such as their name, computer purchased (product
number and product), if the customer purchased the 3 year warranty only or the 3 year and extended
accidental warranty (TRUE or FALSE), computer cost, warranty cost (if chosen), and total cost by
customer.
Warranty Types worksheet: 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 Orders 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.
Page 1 of 7
