base on data facets picture one ,we need to feel the overview instruction and then make a functional spread sheet on purchasing

instruction sheet

now we need a functional spread sheet

Supplier A Supplier B Supplier C Supplier D Quoted Annual Volume (5 Year Program) Supplier Status Location 200,000 Existing Toronto, ON 200,000 200,000 New New New Deli, India Hamilton, ON Move: borplantsoenis Toronto, ON 200,000 Existing Montreal, PO Quality History Units Shipped Units Rejected 500,000 120 2,000,000 400 Sooled by Spoker 150,000 100 Supplay by Supplier 800,000 200 Sombor PPM $33.00 $24.00 $28.00 $30.00 Cost Information Cost/Piece Average Annual Cost Reduction given to Customer 3.0% 2.0% 3.0% 2.5% Racons Their Plant Their Plant Their Plant Delivery Information FOB Import Duty and Tax (If Applicable) Freight Cost as a % of Piece Price Their Plant 15% 10% 6% 25% 12% Delivery History 99% 100% 98% 99% Current Supplier Development Capability Black Box White Box Grey Box Grey Box Note: Data from new suppliers is their current performance with their existing customers Overview - Instructions Data-Facts Functional Spreadsheet A B C N K L M D OPM600 F G H Assignment 2 - Supplier Evaluation and Sourcing Model Student Name: Student Number: Assignment 2 - Create a "functional" tool (spreadsheet) to assist you in making good sourcing decisions, Using the Data-Facts contained in this file, determine the "best" supplier to produce the car door mirror assenbly Use this tab 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: 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 w give you the features or functions that the mirror assemblies perform. List them below. vailabl e Marks Situational Analysis Name of Car that you researchedie. Mercedes C300; Acura MDX; Toyota Camry: What was the trim level i.e. LX; EX; EX-L: List the features of the mirror assembly below below....... 3 4. 5. 6. 2 3 + Based on the course text, Ppt slides, or your research, answer the following questions: 1. Define the following: Black Box Supplier Grey Box Supplier 2 c) White Box Supplier Given the complexity of the product above and definitions above, what level of supplier do you think is required to make the product in question? 2. When considering product quality, what is "PPM" and how is it calculated? Overview - Instructions Data-Facts Functional Spreadsheet + Type here to search A B @ D FIL G 0 -1 2. When considering product quality what is "PPMiland how is it calculated? 4 15 16 2 3. Based on the information provided in the Data-Facts tab, please provide the calculated PPM for each supplier Supplier A 2 ppm Supplier B 2 ppm Supplier C ppm Supplier D 2 ppm ? 37 18 19 50 4. Define: Total Landed Cost: 1 51 52 33 54 55 56 57 2. 3. Based on the information provided in the Data-Facts tab, please calculate the Manded" cost for each supplier Supplier A 12 Supplier B Supplier C ? Supplier D 58 59 60 16. Who is your preferred supplier? Please explain why the supplier selected is the best choice? 4 61 63 64 Total Available Points (This tab) 15 Actual Received 10.0% 65 will when marked 15 66 67 Total available points (Functional Tab 20 68 Actual Received 0.0% 0 69 Total Available 25 71 Total Received 60.0% 15 72 73 34 75 76 Overview Instructions Data-Facts Functional Spreadsheet G I H E F D A B 10 Points Available Create your own "functional" spreadsheet here...... 0 Points Received 1 2 3 1 5 6 7 8 9 1 20 Overview - Instructions Data-Facts Functional Spreadsheet Pady