purchasing manager of a local manufacturing company
2. The purchasing manager of a local manufacturing company has requested
your help to better organize and develop a spreadsheet containing
information about the company’s purchase orders. Using the spreadsheet
P1-PurchaseOrders.Xlsx” complete the following (making sure to efficiently
organize and implement your spreadsheet using appropriate labels,
formatting, colors, formulas, and functions):
a. Rename “Sheet1” as “Purchase Orders”
b. Rename “Sheet2” as “Suppliers”
c. Move the information in column containing the “Order No.” so that it
appears in the first column of the spreadsheet (i.e. Column A)
d. Display the total amount of each order in a column labeled “Total.” The
information should appear in a column immediately to the right ofthe
column containing “Quantity” and to the left of “AP Terms)
e. Display the “Supplier Name” for each order in a column labeled “Supplier
Name.” The information should appear in a column immediately to the
right ofthe column containing “Supplier ID.”
f. Display the lead time for each order in a column labeled “Lead Time.” The
lead time should appear in a column immediately to the right of “Arrival
Date.” Recall that lead time is the amount of time that it takes to receive
an order.
g. Highlight all lead times that are 10 days or more.
h. Display the percentage of orders that are 10 days or more.
i. Data in every cell should be visible (i.e. nothing should appear “cut off”).
j. All column headings should be formatted as 1opt, bold, and the cells
should display a double-line border on the bottom edge.
k. The Supplier ID, Order No., Item No., AP Terms, Lead Time, and all date
information should be centered within their respective columns.
I. All data that is currency should be formatted as such displaying 2 decimal
points.
m. All dates should display a 4 digit year.
11. Add an appropriate heading to the spreadsheet in cell A1 and format the
text as 12pt.
0. Display “total number of orders,” “total amount of all orders,” “Average
amount per order,” and the “average lead time per order” on your
spreadsheet.
p. Sort the data so that it displays in descending order by “Total.”
Save your Excel file as “IDS410-P1Q2.xlsx” and upload it to Blackboard before
class begins on 9/17.
Supplier ID Order No. Item No. Item Description Item Cost Quantity A/P Terms (Months) Order Date Arrival Date
A8 A0111 6489 O-Ring 3 900 25 10-10-11 10-18-11
A6 A0115 5319 Shielded Cable/ft. 1.1 17500 30 08-20-11 08-31-11
A6 A0123 4312 Bolt-nut package 3.75 4250 30 08-25-11 09-01-11
A6 A0204 5319 Shielded Cable/ft. 1.1 16500 30 09-15-11 10-05-11
A6 A0205 5677 Side Panel 195 120 30 11-02-11 11-13-11
A6 A0207 4312 Bolt-nut package 3.75 4200 30 09-01-11 09-10-11
A5 A0223 4224 Bolt-nut package 3.95 4500 30 10-15-11 10-20-11
A5 A0433 5417 Control Panel 255 500 30 10-20-11 10-27-11
A5 A0443 1243 Airframe fasteners 4.25 10000 30 08-08-11 08-14-11
A5 A0446 5417 Control Panel 255 406 30 09-01-11 09-10-11
A8 A0533 9752 Gasket 4.05 1500 25 09-20-11 09-25-11
A8 A0555 6489 O-Ring 3 1100 25 10-05-11 10-10-11
A8 A0622 9752 Gasket 4.05 1550 25 09-25-11 10-05-11
A8 A0666 5125 Shielded Cable/ft. 1.15 15000 25 10-01-11 10-15-11
A8 A0777 6489 O-Ring 3 1050 25 10-29-11 11-10-11
A8 A1222 4111 Bolt-nut package 3.55 4200 25 09-15-11 10-15-11
A7 A1234 9399 Gasket 3.65 1250 45 10-01-11 10-06-11
A7 A1235 9399 Gasket 3.65 1450 45 10-03-11 10-08-11
A7 A1344 5454 Control Panel 220 550 45 10-09-11 10-14-11
A7 A1345 9399 Gasket 3.65 1470 45 10-07-11 10-12-11
A7 A1346 9399 Gasket 3.65 1985 45 10-05-11 10-11-11
A8 A1444 4111 Bolt-nut package 3.55 4250 25 09-20-11 10-10-11
A8 A1445 4111 Bolt-nut package 3.55 4200 25 09-25-11 10-25-11
A8 A1449 4111 Bolt-nut package 3.55 4600 25 10-05-11 10-19-11
A7 A1456 5454 Control Panel 220 500 45 10-15-11 10-20-11
A7 A1457 4569 Bolt-nut package 3.5 3900 45 10-05-11 10-10-11
A7 A1567 1369 Airframe fasteners 4.2 15000 45 09-25-11 09-30-11
A9 A2345 6431 O-Ring 2.85 1250 30 10-05-11 10-10-11
A9 A2356 7258 Pressure Gauge 100.5 95 30 10-20-11 10-29-11
A9 A2367 9977 Panel Decal 1 525 30 11-01-11 11-07-11
A9 A2378 6431 O-Ring 2.85 1350 30 10-01-11 10-07-11
A8 A3467 4111 Bolt-nut package 3.55 4800 25 09-05-11 09-20-11
A8 A5689 4111 Bolt-nut package 3.55 4585 25 09-10-11 09-30-11
A9 A9821 6431 O-Ring 2.85 1300 30 09-25-11 10-01-11
A9 A9842 7258 Pressure Gauge 100.5 100 30 10-15-11 10-24-11
A9 A9865 9967 Hatch Decal 0.85 550 30 11-05-11 11-11-11
A9 A9876 9955 Door Decal 0.55 150 30 11-01-11 11-06-11
A3 A9999 6433 O-Ring 2.95 1500 15 10-01-11 10-10-11
A5 B0247 1243 Airframe fasteners 4.25 9000 30 09-05-11 09-12-11
A6 B0445 4312 Bolt-nut package 3.75 4150 30 09-03-11 09-11-11
A5 B0447 5634 Side Panel 185 150 30 10-25-11 11-03-11
A5 B0479 5634 Side Panel 185 140 30 10-29-11 11-04-11
A5 B0567 1243 Airframe fasteners 4.25 10500 30 10-10-11 10-17-11
A3 B1111 9764 Gasket 3.75 1980 15 09-20-11 09-29-11
A7 B1234 7258 Pressure Gauge 90 100 45 08-25-11 08-28-11
A7 B1345 7258 Pressure Gauge 90 120 45 09-05-11 09-09-11
A7 B1468 1369 Airframe fasteners 4.2 14000 45 09-27-11 10-03-11
A7 B1589 5275 Shielded Cable/ft. 1 25000 45 10-25-11 10-30-11
A7 B1666 1369 Airframe fasteners 4.2 10000 45 09-29-11 10-04-11
A2 B2333 6321 O-Ring 2.45 1300 30 08-25-11 09-04-11
A2 B2345 6321 O-Ring 2.45 1200 30 09-12-11 09-23-11
A2 B2356 6321 O-Ring 2.45 2500 30 09-25-11 10-04-11
A2 B2367 6321 O-Ring 2.45 1250 30 10-12-11 10-21-11
A2 B2378 6321 O-Ring 2.45 1500 30 10-25-11 11-02-11
A2 B2498 5689 Side Panel 175 150 30 11-01-11 11-09-11
A2 B2499 7268 Pressure Gauge 95 110 30 11-01-11 11-12-11
A2 B2511 7268 Pressure Gauge 95 105 30 11-05-11 11-16-11
A2 B2519 5462 Shielded Cable/ft. 1.05 22500 30 08-20-11 08-26-11
A2 B2528 5689 Side Panel 175 175 30 11-05-11 11-15-11
A2 B2537 5462 Shielded Cable/ft. 1.05 21500 30 08-15-11 08-22-11
A2 B2566 5462 Shielded Cable/ft. 1.05 23000 30 08-10-11 08-15-11
A6 B3022 5677 Side Panel 195 110 30 11-05-11 11-17-11
A6 B3041 5234 Electrical Connector 1.65 4500 30 08-28-11 09-05-11
A6 B3042 5234 Electrical Connector 1.65 4750 30 09-05-11 09-13-11
A6 B3111 5234 Electrical Connector 1.65 4850 30 09-02-11 09-11-11
A6 B3222 8008 Machined Valve 645 150 30 10-15-11 10-26-11
A6 B3333 8008 Machined Valve 645 100 30 10-10-11 10-21-11
A2 C0234 5166 Electrical Connector 1.25 5650 30 10-01-11 10-06-11
A2 C0423 5689 Side Panel 175 155 30 10-25-11 11-03-11
A2 C0433 5462 Shielded Cable/ft. 1.05 22500 30 08-25-11 09-02-11
A6 C0456 5677 Side Panel 195 130 30 10-28-11 11-07-11
A6 C0467 8008 Machined Valve 645 120 30 10-28-11 11-04-11
A6 C0589 5319 Shielded Cable/ft. 1.1 18100 30 08-25-11 09-05-11
A9 C1111 9955 Door Decal 0.55 125 30 11-05-11 11-10-11
A4 C1212 1122 Airframe fasteners 4.25 19500 30 08-05-11 08-13-11
A4 C1313 3166 Electrical Connector 1.25 5600 30 08-25-11 08-29-11
A3 C2211 9764 Gasket 3.75 1850 15 09-25-11 10-05-11
A9 C2222 7258 Pressure Gauge 100.5 90 30 10-10-11 10-17-11
A4 C2323 1122 Airframe fasteners 4.25 15500 30 09-04-11 09-12-11
A4 C2929 3166 Electrical Connector 1.25 5500 30 09-01-11 09-06-11
A4 C3232 1122 Airframe fasteners 4.25 18000 30 10-01-11 10-08-11
A9 C3333 8148 Machined Valve 655.5 125 30 10-10-11 10-17-11
A4 C3434 1122 Airframe fasteners 4.25 12500 30 09-05-11 09-11-11
A4 C4545 1122 Airframe fasteners 4.25 15000 30 09-08-11 09-15-11
A4 C5656 1122 Airframe fasteners 4.25 14500 30 09-28-11 10-03-11
A4 C6765 5066 Shielded Cable/ft. 0.95 25000 30 09-05-11 09-12-11
A4 C7875 3166 Electrical Connector 1.25 5650 30 09-05-11 09-10-11
A4 C8854 3166 Electrical Connector 1.25 5425 30 09-10-11 09-15-11
A4 C8989 9966 Hatch Decal 0.75 500 30 08-25-11 08-31-11
A3 D1111 9764 Gasket 3.75 1800 15 09-28-11 10-05-11
A4 D1212 5066 Shielded Cable/ft. 0.95 17500 30 09-15-11 09-22-11
A4 D2121 1122 Airframe fasteners 4.25 17500 30 10-25-11 11-03-11
A4 D3232 1122 Airframe fasteners 4.25 17000 30 10-11-11 10-19-11
A3 D3333 9764 Gasket 3.75 1750 15 09-20-11 09-25-11
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂
