Using Excel to choose target supplierOrders and Suppliers
Vendor Name
Spacetime Technologies
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Alum Sheeting
Alum Sheeting
Alum Sheeting
Alum Sheeting
Spacetime Technologies
Spacetime Technologies
Spacetime Technologies
Spacetime Technologies
Spacetime Technologies
Vendor No.Order No. Item No.
2
A0111
6489
6
6
6
6
6
5
5
5
5
2
2
2
2
2
A0125
A0123
A0204
A0205
A0207
A0223
A0433
A0443
A0446
A0533
A0555
A0622
A0666
A0777
Spacetime Technologies
Durrable Products
Durrable Products
2
3
3
Durrable Products
Item Description
O-Ring
Item Cost
$ 3.00
$ 1.10
$ 3.75
$ 1.10
$ 195.00
$ 3.75
$ 3.95
$ 255.00
$ 4.25
$ 255.00
$ 4.05
$ 3.00
$ 4.05
$ 1.15
$ 3.00
5319
Shielded Cable/ft.
4312
Bolt-nut package
5319
5677
Shielded Cable/ft.
Side Panel
4312
4224
Bolt-nut package
Bolt-nut package
5417
Control Panel
1243
Airframe fasteners
5417
Control Panel
9752
6489
9752
Gasket
O-Ring
Gasket
5125
Shielded Cable/ft.
A1222
A1234
A1235
6489
4111
9399
9399
O-Ring
Bolt-nut package
Gasket
Gasket
3
A1344
5454
Control Panel
$ 220.00
Durrable Products
Durrable Products
Spacetime Technologies
Spacetime Technologies
Spacetime Technologies
3
3
2
2
2
A1345
A1346
A1444
A1445
A1449
9399
9399
4111
4111
4111
Gasket
Gasket
Bolt-nut package
Bolt-nut package
Bolt-nut package
$
$
$
$
$
Durrable Products
Durrable Products
Durrable Products
Manley Valve
Manley Valve
Manley Valve
Manley Valve
3
3
3
7
7
7
7
A1456
A1457
A1567
A2345
A2356
A2367
A2378
5454
Control Panel
4569
1369
6431
Bolt-nut package
Airframe fasteners
O-Ring
7258
9977
Pressure Gauge
Panel Decal
Spacetime Technologies
Spacetime Technologies
2
2
A3467
A5689
Manley Valve
Manley Valve
Manley Valve
Manley Valve
Pylon Accessories
Alum Sheeting
Steelpin Inc.
Alum Sheeting
Alum Sheeting
Alum Sheeting
7
7
7
7
8
5
6
5
5
5
A9821
A9842
A9865
A9876
A9999
B0247
B0445
B0447
B0479
B0567
6431
4111
4111
6431
O-Ring
Bolt-nut package
Bolt-nut package
O-Ring
$ 220.00
$ 3.50
$ 4.20
$ 2.85
$ 100.50
$ 1.00
$ 2.85
7258
9967
9955
Pressure Gauge
Hatch Decal
Door Decal
6433
1243
4312
O-Ring
Airframe fasteners
Bolt-nut package
5634
5634
Side Panel
Side Panel
1243
Airframe fasteners
$
$
$
$
$
3.55
3.65
3.65
3.65
3.65
3.55
3.55
3.55
3.55
3.55
$ 2.85
$ 100.50
$ 0.85
$ 0.55
$ 2.95
$ 4.25
$ 3.75
$ 185.00
$ 185.00
$ 4.25
Pylon Accessories
Durrable Products
Durrable Products
Durrable Products
Durrable Products
Durrable Products
8
3
3
3
3
3
B1111
B1234
B1345
B1468
B1589
B1666
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
4
4
4
4
4
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Fast-Tie Aerospace
Fast-Tie Aerospace
Fast-Tie Aerospace
Steelpin Inc.
Steelpin Inc.
Steelpin Inc.
Manley Valve
9764
Gasket
7258
7258
Pressure Gauge
Pressure Gauge
$ 3.75
$ 90.00
$ 90.00
$ 4.20
$ 1.00
$ 4.20
1369
Airframe fasteners
5275
Shielded Cable/ft.
B2333
B2345
B2356
B2367
B2378
1369
6321
6321
6321
6321
6321
Airframe fasteners
O-Ring
O-Ring
O-Ring
O-Ring
O-Ring
4
4
4
4
4
4
4
6
6
6
6
6
6
4
4
4
6
6
6
7
B2498
B2499
B2511
B2519
B2528
B2537
B2566
B3022
B3041
B3042
B3111
B3222
B3333
C0234
C0423
C0433
C0456
C0467
C0589
C1111
5689
7268
7268
5462
5689
5462
5462
5677
5234
5234
5234
8008
8008
5166
5689
5462
5677
8008
5319
9955
Side Panel
Pressure Gauge
Pressure Gauge
Shielded Cable/ft.
Side Panel
Shielded Cable/ft.
Shielded Cable/ft.
Side Panel
Electrical Connector
Electrical Connector
Electrical Connector
Machined Valve
Machined Valve
Electrical Connector
Side Panel
Shielded Cable/ft.
Side Panel
Machined Valve
Shielded Cable/ft.
Door Decal
$ 175.00
$ 95.00
$ 95.00
$ 1.05
$ 175.00
$ 1.05
$ 1.05
$ 195.00
$ 1.65
$ 1.65
$ 1.65
$ 645.00
$ 645.00
$ 1.25
$ 175.00
$ 1.05
$ 195.00
$ 645.00
$ 1.10
$ 0.55
Hulkey Fasteners
1
C1212
1122
Airframe fasteners
$
Hulkey Fasteners
Pylon Accessories
Manley Valve
1
8
7
C1313
C2211
C2222
3166
Electrical Connector
9764
Gasket
7258
Pressure Gauge
$ 1.25
$ 3.75
$ 100.50
Hulkey Fasteners
1
C2323
1122
Airframe fasteners
$
4.25
Hulkey Fasteners
1
C2929
3166
Electrical Connector
$
1.25
Hulkey Fasteners
1
C3232
1122
Airframe fasteners
$
4.25
Manley Valve
7
C3333
8148
Machined Valve
$ 655.50
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
1
1
1
C3434
C4545
C5656
1122
1122
1122
Airframe fasteners
Airframe fasteners
Airframe fasteners
$
$
$
4.25
4.25
4.25
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
Hulkey Fasteners
Pylon Accessories
1
1
1
1
8
C6765
C7875
C8854
C8989
D1111
5066
3166
3166
9966
Shielded Cable/ft.
Electrical Connector
Electrical Connector
Hatch Decal
9764
Gasket
$
$
$
$
$
0.95
1.25
1.25
0.75
3.75
$
$
$
$
$
2.45
2.45
2.45
2.45
2.45
4.25
Hulkey Fasteners
1
D1212
5066
Shielded Cable/ft.
$
0.95
Hulkey Fasteners
Hulkey Fasteners
1
1
D2121
D3232
4.25
4.25
8
D3333
Airframe fasteners
Airframe fasteners
Gasket
$
$
Pylon Accessories
1122
1122
9764
$
3.75
Quantity
Cost per order
900
17,500
4,250
16,500
120
4,200
4,500
500
10,000
406
1,500
1,100
1,550
15,000
1,050
$
$
$
$
$
$
$
$
2,700.00
19,250.00
15,937.50
18,150.00
23,400.00
15,750.00
17,775.00
127,500.00
$
42,500.00
$
$
$
$
$
$
103,530.00
6,075.00
3,300.00
6,277.50
17,250.00
3,150.00
4,200
1,250
1,450
$
$
$
14,910.00
4,562.50
5,292.50
550
$
121,000.00
1,470
1,985
4,250
4,200
4,600
$
$
$
$
$
5,365.50
7,245.25
15,087.50
14,910.00
16,330.00
500 $
3,900 $
15,000 $
1,250 $
95 $
525 $
1,350 $
110,000.00
13,650.00
4,800
4,585
63,000.00
3,562.50
9,547.50
525.00
3,847.50
$
$
17,040.00
16,276.75
1,300 $
100 $
550 $
150 $
1,500 $
9,000 $
4,150 $
150 $
140 $
10,500 $
3,705.00
10,050.00
467.50
82.50
4,425.00
38,250.00
15,562.50
27,750.00
25,900.00
44,625.00
A/P Terms
25
30
30
30
30
30
30
30
30
30
25
25
25
25
25
25
45
45
45
45
45
25
25
25
45
45
45
30
30
30
30
25
25
30
30
30
30
15
30
30
30
30
30
Order Date
Arrival Date
10/10/13
08/20/13
08/25/13
09/15/13
12/02/13
09/01/13
10/15/13
10/20/13
08/08/13
09/01/13
09/20/13
10/05/13
09/25/13
10/01/13
10/29/13
10/18/13
08/31/13
09/01/13
10/05/13
12/13/13
09/10/13
10/20/13
10/27/13
08/14/13
09/10/13
09/25/13
10/10/13
10/08/13
10/15/13
12/10/13
07/15/13
10/01/13
10/03/13
10/15/13
10/06/13
10/08/13
10/09/13
10/14/13
10/07/13
10/05/13
09/20/13
06/25/13
10/05/13
10/15/13
11/11/13
10/10/13
10/25/13
10/19/13
10/15/13
10/05/13
09/25/13
10/05/13
10/20/13
12/01/13
10/01/13
10/20/13
10/10/13
10/30/13
10/10/13
10/29/13
09/05/13
09/10/13
09/20/13
09/30/13
09/25/13
10/15/13
12/05/13
12/01/13
10/01/13
09/05/13
09/03/13
10/25/13
10/29/13
10/10/13
10/01/13
10/24/13
12/12/13
12/06/13
10/10/13
09/13/13
09/13/13
12/03/13
12/04/13
10/17/13
12/07/13
10/07/13
1,980
100
120
14,000
25,000
10,000
$
$
$
7,425.00
9,000.00
10,800.00
$
58,800.00
$
25,000.00
1,300
1,200
2,500
1,250
1,500
$
$
$
$
$
$
42,000.00
3,185.00
2,940.00
6,125.00
3,062.50
3,675.00
150
110
105
22,500
175
21,500
23,000
110
4,500
4,750
4,850
150
100
5,650
155
22,500
130
120
18,100
125
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
26,250.00
10,450.00
9,975.00
23,625.00
30,625.00
22,575.00
24,150.00
21,450.00
7,425.00
7,837.50
8,002.50
96,750.00
64,500.00
7,062.50
27,125.00
23,625.00
25,350.00
77,400.00
19,910.00
68.75
19,500
$
82,875.00
5,600
1,850
90
$
$
$
7,000.00
6,937.50
9,045.00
15,500
$
65,875.00
5,500
$
6,875.00
18,000
$
76,500.00
125
$
81,937.50
12,500
15,000
14,500
$
$
$
53,125.00
63,750.00
61,625.00
25,000 $
5,650 $
5,425 $
500 $
1,800 $
23,750.00
7,062.50
6,781.25
375.00
6,750.00
15
45
45
45
45
45
09/20/13
08/25/13
09/05/13
09/27/13
10/25/13
09/29/13
09/29/13
08/28/13
09/09/13
10/03/13
10/30/13
10/04/13
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
30
08/25/13
09/13/13
09/25/13
10/13/13
10/25/13
09/04/13
09/23/13
10/04/13
10/21/13
12/02/13
12/01/13
12/01/13
12/05/13
08/20/13
12/05/13
08/15/13
08/10/13
12/05/13
08/28/13
09/05/13
09/02/13
10/15/13
10/10/13
10/01/13
10/25/13
08/25/13
10/28/13
10/28/13
08/25/13
12/05/13
12/09/13
12/12/13
12/16/13
08/26/13
12/15/13
08/22/13
08/15/13
12/17/13
09/05/13
09/13/13
09/13/13
10/26/13
10/21/13
10/06/13
03/14/14
09/02/13
03/07/14
12/04/13
09/05/13
12/10/13
08/05/13
08/13/13
08/25/13
09/25/13
10/10/13
08/29/13
10/05/13
10/17/13
09/04/13
09/13/13
09/01/13
09/06/13
10/01/13
10/08/13
10/10/13
10/17/13
09/05/13
09/08/13
09/28/13
09/13/13
09/15/13
10/03/13
09/05/13
09/05/13
09/10/13
08/25/13
09/28/13
09/13/13
09/10/13
09/15/13
08/31/13
10/05/13
15
30
30
30
30
30
30
30
30
30
30
30
30
15
17,500
$
16,625.00
09/22/13
$
$
74,375.00
72,250.00
30
30
30
09/15/13
17,500
17,000
10/25/13
10/13/13
12/03/13
10/19/13
1,750
$
6,562.50
15
09/20/13
09/25/13
Category
Excellent (10)
Good (8)
Fair (6)
Inadequate (4)
Most appropriate
features of the software
have been employed.
Most Fields used in
decision used Excel
formulas to do
calculations and formula
use was appropriate.
Includes appropriate,
accurate displays of the
data.
Use of the software tool
is rudimentary. A few
Fields used in decision
used Excel formulas to
do calculations and
formula use was
inappropriate.
Capabilities of the
software have not been
adequately utilized.
Formulas not used for
calculations and/or
used incorrectly
Data Display (10%)
Use Excel graphs
and charts to
present findings
Evidence shows mastery
of the software with all
appropriate features
used. All Fields used in
decision used Excel
formulas to do
calculations and formula
use was appropriate.
Includes appropriate,
well-labeled, accurate
displays (graphs and/or
tables) of the data.
Includes appropriate
but not accurate
displays of the data.
Does not include
appropriate displays of
the data.
Category
Excellent (20)
Good (16)
Fair (12)
Analysis of the data is
appropriate, mostly
accurate, and fairly
thorough.
Conclusion includes an
answer to the statistical
question that is
consistent with the data
analysis but not with the
data collection method.
Some chapter topics are
related in the
recommendations with
some support from the
analysis.
Gives a good overall
picture of the decision
process taken —logical,
well thought out, steps
taken were focused and
made sense, but was
weak in one of these
areas.
Analysis of the data is
appropriate, but only
somewhat accurate and
thorough.
Conclusion does not
include an answer to
the statistical question
that is consistent with
the data analysis.
Analysis of the data is
not appropriate or is
missing.
The recommendations
have little connection to
the chapter topic and
have little support from
the analysis.
Overall picture of the
decision process was
presented but
contained gaps in
demonstrating or
explaining.
Recommendations are
unrelated to chapter
topics or has minimal
relation to the analysis.
Use of software
tool (10%)
Use Excel to
calculate values,
sort results
Analysis
Procedure (20%)
Analysis of the data is
accurate, thorough, and
appropriate.
Data Analysis
Conclusion (20%)
Conclusion includes a
clear answer to the
statistical question that is
consistent with the data
analysis and the method
of data collection.
Recommendations are
consistent with the
chapter material and are
backed up by the analysis
results.
Gives a good overall
picture of the decision
process taken —logical,
well thought out, steps
taken were focused and
made sense.
Recommended
Actions (20%)
Decision Approach
(20%)
Approach must be
described and
explained in
writing in a Word
Doc or in the Excel
Spreasheet.
The rubric is designed to yield a total score of 100 for a perfect assignment.
Inadequate (8)
No conclusion is
presented.
No evidence of a
decision process was
presented
Chapter 2 Hands-on MIS Application
Problem Statement
IMPROVING DECISION MAKING: USE A SPREADSHEET TO SELECT SUPPLIERS
Software skills: Spreadsheet date functions, data filtering, DAVERAGE function
Business skills: Analyzing supplier performance and pricing 2- 11
In this exercise, you will learn how to use spreadsheet software to improve management decisions about
selecting suppliers. You will filter transactional data on suppliers based on several different criteria to
select the best suppliers for your company. You run a company that manufactures aircraft components.
You have many competitors who are trying to offer lower prices and better service to customers, and
you are trying to determine whether you can benefit from better supply chain management. In the
BlackBoard “Learning Module” folder you will find a spreadsheet file that contains a list of all of the
items that your firm has ordered from its suppliers during the past three months. The fields in the
spreadsheet file include vendor name, vendor identification number, purchaser’s order number, item
identification number and item description ( for each item ordered from the vendor), cost per item,
number of units of the item ordered ( quantity), total cost of each order, vendor’s accounts payable
terms, order date, and actual arrival date for each order. Prepare a recommendation of how you can use
the data in this spreadsheet database to improve your decisions about selecting suppliers. Some criteria
to consider for identifying preferred suppliers include the supplier’s track record for on- time deliveries,
suppliers offering the best accounts payable terms, and suppliers offering lower pricing when the same
item can be provided by multiple suppliers. Use your spreadsheet software to prepare reports to support
your recommendations.
Hint on Excel Skills and Usage (See Excel Tutorial Links Below)
This exercise requires some student knowledge of spreadsheet database functions. At a
minimum, students should know how to sort the database by various criteria such as item description,
item cost, vendor number, vendor, name, or A/P terms. Students may need to be told that A/P Terms is
expressed as the number of days that the customer has to pay the vendor for a purchase. In other words,
30 designates net 30 days. The vendor that allows customers the longest amount of time to pay for an
order would, of course, offer the most favorable payment terms.
Students will need to add additional columns for calculating the delivery time for each order.
The delivery time can be calculated by subtracting the Order Date from the Arrival Date. Vendors with
the shortest delivery times are obviously desirable.
These numbers are useful when trying to determine who is the vendor with the best on-time
delivery track record. Students can use the DAVERAGE or the SUMIF and COUNTIF functions to
determine the average delivery time for each vendor. Students can also use one of the database
functions to determine the vendor with the best accounts payable terms. To determine the vendor with
the lowest prices for the same item when it is supplied by multiple vendors, students can filter the
database using the item description. This filtered list can then be sorted by item cost and vendor
number.
Excel Tutorials Links
Choose Link below Based on Your current Excel Version:
http://www.gcflearnfree.org/excel2013/27 For MS Excel 2013 users – 28 Lessons in all subjects
needed
http://www.tutorialspoint.com/excel/ For MS Excel 2010 users – Lessons with examples in all
subjects needed – click on training of interest from the Left Side Menu.
http://www.gcflearnfree.org/excel2007 For MS Excel 2007 users – Lessons in all subjects needed –
avoid the Ads, the training material is there as you scroll down the pages.
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.