Specialist!

WORKORDER
The Microsoft Office Experts

Customer Name: Jazz My Wheels

r~ Ic * I* *•
tXCei ^peCiailSI Assigned to: You, the MS Excel Specialist
Dri-»;/-n-t-
project fftt-. cc-iu
1n -
Project Title: Inventory Markup

Date: (current date, 20xx)

Note: Prior to beginning this project, review the Company Overview provided on page 17.

Project Description
Jazz My Wheels is adding new products to its line of exterior and interior car accessories. They
need to determine how the inventory should be marked up by both dollars and percents. They will
use this information to set prices on the merchandise.

Your Job
As the Microsoft Excel Specialist, you will create a spreadsheet to determine how the inventory
should be marked up by both dollars and percents.

Tips and Strategies

1. Read all of the instructions before proceeding with the project.
2. Carefully check your results after entering the formulas in the spreadsheet.

mst^

H TheOfficeSpecialist.com ^•••••^••^H
My Wheels » Project E-1Q * Inventory Markup

instructions to the MS Excel Specialist

1. Using Microsoft Excel, create a new blank workbook.
2. Save the document as Project E-10 Inventory Markup in the "Excel Projects" folder within the
"Jazz My Wheels Projects" folder.
3. Key the data into the spreadsheet as it appears in Spreadsheet E-10. Unless otherwise noted,
the font should be set to Arial 10 point.
4. Select cells E8 - G29 and format them as currency with 2 decimal places.
5. Format cells H8 - H29 as percentages displaying 2 decimal places.
6. Enter a formula in cell G8 that will subtract the UNIT COST from the SELLING PRICE.
Hint: In cell G8, key =F8-E8.

7. Enter a formula in cell H8 that will divide the $ MARKUP by the UNIT COST.
Hint: In cell H8, key =G8/E8.

8. Copy the formula in cell G8 and paste it into cells G9 - G17 and G20 - G28.
9. Copy the formula in cell H8 and paste it into cells H9 - H17 and H20 - H28.
10. Enter formulas in cells B18 and B29 to determine the number of items for exterior accessories
and for interior accessories as follows:
In cell B18, key =COUNT(B8:B17).
In cell B29, key =COUNT(B20:B28).
11. Select cells Al - H29 and set the AutoFormat feature to "List 2."
Note: If "List 2" is not on option, select an alternate AutoFormat style to apply to the above cells.
Although readily available in earlier versions, Microsoft did not include AutoFormat on the ribbon
in Excel 2007. To use the AutoFormat feature in Excel 2007, you must add the AutoFormat button to
the Quick Access Toolbar.

12. Display formulas in your spreadsheet by using <CTRL>+~ to check for accuracy.
13. Carefully proofread your work for accuracy.
14. Resave the file.
15. Set the Print Area to include all cells containing data in the spreadsheet.
16. Use Print Preview and adjust the Page Setup so that the spreadsheet fits on one page.
17. Print a copy of the spreadsheet if required by your instructor.

173
The Office Specialist.com
c/v
T3
""I - V
n>
01 ,
Q.
t/1
<t>
ft-'
A B C D E F G H
1 Jazz My Wheels
2 Markup Schedule
3
4
5 ITEM UNIT SELLING $ %
6 NUMBER ITEM MAKE COST PRICE MARKUP MARKUP
7
8 Exterior Accessories 59813457 Retro Style Billet Grille PT Cruiser 176 281.6 <formula> <formula>
9 15793578 Hood Vent Corvette 32 48
10 25493572 Rear Spoiler Mustang 146 248.2
11 58134759 Removable Roof Rack PT Cruiser 42 58.8
12 15793584 Headlight Covers Chrysler 300 17 27.2
13 35812492 Rear Hatch Accent Strip PT Cruiser 14 22.4
14 57905428 Splash Guards Corvette 21 35.7
15 58143579 Roof Mount Bike Carrier Mustang 34 57.8
16 65497845 Front End and Hood Cover PT Cruiser 51 76.5
17 25807456 Backup Sensors Chrysler 300 _ 43 68.8 ^ ^ r
18 Total Items <formula>
19
20 Interior Accessories 48751358 Satellite Receiver Kit Chrysler 300 177 300.9 <formula> <formula>
21 24586897 Door Sill Plate PT Cruiser 16 25.6
22 58784596 Climate Control Knobs Mustang 17 25.5
23 58468752 Dash Accent Kits Corvette 44 74.8
24 25489635 Polished Oval Mirror PT Cruiser 15 22.5
25 78495135 Alarm Systems Corvette 52 83.2
26 25794583 Carpeted Floor Mats Mustang 31 49.6
27 54789824 Double Wide Arm Rest PT Cruiser 52 88.4
^ T
28 45879512 Custom Leather Seat Covers PT Cruiser 40 40
29 Total Items <formula>