Problem:
Longhorn Office Supplies sells office supplies to small businesses in Austin area. The company is trying to manage its inventory more effectively by monitoring the inventory levels for some frequently sold items. Lonhorn’s manager, Dell, has decided that assigning priorities to the various types of inventory based on the number on hand would help her focus on the which items needed to be to reordered quickly. Priority 1 will be assigned to items with up to 249 units on hand, Priority 2 to items with 250-399 units on hand, and so forth. Dell’s time is limited, so she wants to have the priority established for her automatically rather than having someone type the priority in by hand for each inventory item. She has hired you to help her implement this new inventory management plan.
Required:
Using Excel, create an inventory table from the data in Table 1 below. Also include an Amount field and a Priority field. Both are calculated columns. Amount equals Inventory times Price. Create a Priority Code table in the range I1: J6 using the data shown in Table 2.
Use the VLOOKUP function to determine the priority to assign to each record. Add a total row to the table. Use the Header function to title your worksheet
Table 1 Longhorn office supple inventory Table 2 Priority Codes
Item Number Description Inventory Price Inventory Priority
B60338 1GB Flash drives 167 14.25 0 1
M44910 Marking Pens(boxed) 798 9.50 250 2
C71610 Legal Pad 426 1.50 400 3
S80787 5GB flash drive 894 22.50 600 4
T36275 Printer Paper(ream) 304 1.25 800 5
T74695 Printer paper (case) 252 8.30
W59366 Hanging File Folder 18 7.95
C24890 Heavy Duty Staples 503 13.80
C87343 Pencils(boxed) 816 2.85
W15840 Hole Punch 604 6.85
C49955 pens(boxed) 237 8.25