Student Name(s):
(Maximum 2 students)
Student Number:
Student Number:
1
2
Assignment 2 – Create a “functional” tool (spreadsheet) to assist you in making good sourcing decisions.
Using the information contained in Tab 2. Data-Facts, determine the “best” supplier to produce the car door mirror assembly
Use this spreadsheet for your answer, the data facts are on the 2nd tab, and I want you to create your function tool on the 3rd tab.
Requirement:
On tab 3, create a One page, working, spreadsheet, not a Word document, a spreadsheet.
– Must be functional, that is, a working spreadsheet i.e. must do calculations which will
change automatically if the input variables change
– Must show clearly the logic used to come to your final selection
1. Go to any car dealership and inspect the models in the showroom. Talk to a salesperson, tell them what your assignment is and they will
give you the features or functions that the mirror assemblies perform. List them below“ituational Analysis
Name of Car that you researchede.Mercedes C300; Acura MDX; Toyota Camry, etc.:
What was the trim level i.e. LX; EX; EX-L:
List the features of the mirror assembly below below®..
Availabl
e Marks
2
1
2
3
4.
5.
6.
Based on the course text, Ppt slides, your research, or the “functional tool” you created on TAB 3, answer the following questions:
1. Define the following:
a)
Black Box Supplier
b)
Grey Box Supplier
c)
White Box Supplier
d)
Given the complexity of the product and definitions above, what level of supplier do you think is
required to make the product in question?
2
1
2. When considering product quality, define “PPM” and how is it calculated?
1
3. Based on the information provided in the Data-Facts tab, please provide the calculated PPM for each supplier
2
Supplier A
?
ppm
Supplier B
?
ppm
Supplier C
?
ppm
Supplier D
?
ppm
4. Define and calculate: Total Landed Cost:
2
2
3. Based on the information provided in the Data-Facts tab, please calculate the “landed” cost for each supplier
Supplier A
?
Supplier B
?
Supplier C
?
Supplier D
?
6. Who is your preferred supplier? Please explain why the supplier selected is the best choice?
3
Total Available Points (This tab)
Actual Received
Will be updated when marked
Total available points (Functional Tab)
Actual Received
Will be updated when marked
Total Available
Total Received
15
0
10
0
25
0
0.0%
0.0%
0.0%
Assignment 1 – Value 15%
PRODUCT to be SOURCED: AUTOMOTIVE DOOR MIRRORS
Door mirror assemblies are considered a complex sub-assembly that is painted to the body colour of the vehicle
and needs some level of engineering design and tseting by the supplier. Also note that painted parts must be
handled with care during shipping.
Quoted Annual Volume (5 Year
Program)
Supplier Status
Location

Supplier A
Supplier B
Supplier C
200,000
Existing
Toronto, ON
200,000
New
New Deli, India
200,000
New
Hamilton, ON
Note: Your plant location is Toronto, ON.
Quality History
Units Shipped
Units Rejected
Estimated Warranty Cost (per year)
PPM
Cost Information
Cost/Piece
Average Annual Cost Reduction
offered to Customer
500,000
120
$10,000
2,000,000
400
$75,000
150,000
100
$30,000
From Your Co. Records
Supplied by Supplier
Supplied by Supplier
As calcuated
As calcuated
As calcuated
$33.00
$24.00
$28.00
3.0%
2.0%
3.0%
From Your Co. Records
Supplied by Supplier
Supplied by Supplier
Supplier Plant
Supplier Plant
10%
35%
Supplier Plant
15%
15%
99%
100%
98%
Black Box
White Box
Grey Box
Delivery Information
FOB
Import Duty and Tax (If Applicable)
Freight Cost as a % of Piece Price
Delivery History
Current Supplier Development
Capability
Note: Data from new suppliers is their current performance with their existing customers
RS
the body colour of the vehicle
e that painted parts must be
Supplier D
200,000
Existing
Montreal, PQ
Supplier
nto, ON.
Calculation of PPM
800,000
200
$20,000
From Your Co. Records
As calcuated
Variable Name
# of Rejections
?
A
Value
120
B
Value
400
C
Value
100
D
Value
200
?
?
PPM Calculation
$30.00
Supplier
2.5%
From Your Co. Records
Calculation of Landed Cost
Item/Description
Supplier Plant
20%
99%
Grey Box
Unit Cost
?
?
?
Landed Cost Calculation
A
Value
B
Value
C
Value
D
Value
$33.00
$24.00
$28.00
$30.00
Create your own “functional” spreadsheet hereJPoints Available
Points Received
10
0

Purchase answer to see full
attachment