please put your formulas beside the answer

Assignment 1- Create "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 3 rd 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 will give you the features or functions that the mirror assemblies perform. List them below... Situational Analysis Name of Car that you researched...ie.Mercedes C300; Acura MDX; Toyota Camry: What was the trim level i.e. LX; EX; EX-L; List the features of the mifror assembly below below...... 1 2 3 4. 5. 6. Based on the course text, Ppt slides, or your research, answer the following questions: 1. Define the following: a) Black Box Suonlier b) Grev Box Supolier d) 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? 3. Based on the information provided in the Data-Facts tab, please provide the calculated PPM for each supplier 4. Define: Total Landed rner. 2. When considering product aualitv. what is "PPM" and how is it calculated? 3. Based on the information provided in the Data-Facts tab, please calculate the "landed" cost for each supplier SupplierASupplierBSupplierCSupplierD???? 6. Who is your preferred supplier? Please explain why the supplier selected is the best choice? PRODUCT to be SOURCED: AUTOMOTIVE DOOR MIRRORS Door mirror assemblies are considered a complex sub-assembly that is painted to the body col our of the vehicle ?d parts must be Supplier D 200,000 Existing Montreal, PQ our of the vehicle ?d parts must be Supplier D 200,000 Existing Montreal, PQ \begin{tabular}{|c|} \hline \begin{tabular}{c} 800,000 \\ 200 \\ \hline From Your Co. Records \end{tabular} \\ \hline \\ \hline 30.00 \\ \hline 2.5% \\ \hline From Your Co. Records \\ \hline Their Plant \\ \hline \\ \hline 12% \\ \hline 99% \\ \hline \end{tabular}