Goal:
Using Excel, design a simple sheet that involves "lookup tables."
Lecture:
You are the manager of our computer warehouse, Oly Discount Computer Store. Many small computer stores order their hardware from us. When companies buy in bulk, they qualify for discounts up to 33%.
Because our prices change regularly, it is important that our 'data table' be updated easily, and the resulting invoice reflects these changes. 'Lookup tables' are a spreadsheet concept that will accomplish this task.
You will design a spreadsheet that consists of a 'master lookup table' containing our current price sheet, followed by an actual Order Form for the client.
The user will only have to enter two (2) pieces of information in the Order Form, namely the Quantity and Item Number.
The remainder of the Order Form will fill in automatically (via your formulas).
When your project is completed, you have been requested to create a presentation to place your product on the market for sale to small businesses (Powerpoint).
You may also wish to create a web page to place this in the ECommerce market.
Procedure:
Following is a sample of the finished product.
Notice that in the ORDER FORM (rows 21-24 in the example) that only Qty and ItemNumber are entered by the user. Everything else is calculated automatically.
Save your project as "proje-computerland.xls"
To learn how to create lookup table formulas, see Illustration #1 below this image.
Illustration #1
In the example below, Joey has a grade percentage of 76%. Our goal is to create a formula that will assign a letter grade to this automatically.
A 'lookup table' was designed, and our formula (in cell C64) will simply go up to this table and pluck out the correct letter grade and enter it in C64.
You may wish to try this example before applying it to the Oly Order Form above.
Grading:
Select this link to view the grading requirements for this semester
