Name _____________________________
AGME 2903
Sample Hour Exam #3
Spreadsheets continued
Actual Exams consist of approximately 10 multiple choice items worth 2 pts. each and a practicum that accounts for the remaining points. Partial credit is possible on the practicum.
Directions: Complete each task described below. You may use notes and other printed materials on the practicum only. You may not receive or provide assistance to another student. If you do not understand any item, please ask your instructor for clarification.
1. Pop-up messages that appear when spreadsheet users roll over certain cells with the cursor or pointer tool are created with which set of commands?
Practicum
Directions: Prepare a spreadsheet template according to the criteria specified below. The spreadsheet template must automatically recalculate all values if the "Quantity" or "Catalog Code" entry is changed.
You are to make a spreadsheet template that the manufacturer can use to produce a sales ticket for each customer purchasing one or more bicycles. The template should look something similar to the one shown below:ACME Bicycle Company
| Customer Name | |||
| Quantity | Catalog Code | List Price ($/unit) | Total Item Cost ($) |
| Total Pre-Tax / Pre-Discount Purchase: | |||
| Discount | |||
| Subtotal | |||
| Tax (@ 6.25 %) | |||
| Please Pay this Amount | |||
The template should be set up (using Special Functions and Formulas) so that the sales person can enter the number of bicycles purchased. ("Quantity") and a "Catalog Code" [number(s) or letter(s) you select and reference to each bicycle model], the "List Price" and "Total Item Cost" will be retrieved and/ or calculated and appear automatically.
The second page should contain a coded price template with the information listed below. Be sure to include headings. The manufacturer makes six bicycle models and sells each for the price shown.
| A | Rocket Flyer | $540.00 |
| B | Jet Flyer | $490.00 |
| C | Racer Flyer | $425.00 |
| D | Sport Flyer | $350.00 |
| E | Peddle Pusher | $200.00 |
| F | Tow Behind | $10.00 |
The "Total Pre-Tax / Pre-Discount Purchase" amount should be automatically calculated and appear in the appropriate cell.
A customer should earn a 10 % "Discount" if his/her purchase is $10,000 or more. (The discount amount should be calculated automatically and appear in the appropriate cell.)
The "Subtotal" ("Total Pre-Tax / Pre-Discount" amount minus the "Discount") should be calculated automatically and appear in the appropriate cell.
The "Tax" should automatically be calculated at 6.25 % of the "Subtotal" and appear in the appropriate cell.
The amount owed by the customer ("Please Pay this Amount") should be automatically calculated and appear in the designated cell.
Prepare a sales ticket for a customer named S. Claus. He has purchased 8 Rocket Flyers, 10 Racer Flyers, 15 Jet Flyers, and 7 Tow Behind bicycles. What will be the amount of his bill?
Save the spreadsheet as practice4 in a folder called test4xxx where xxx are your initials.