Please show excel tables!

Part A: Lowest Manufacturing Cost

Based on the given information, we can build a linear optimization model in Excel to determine the optimal arrangement of 10 key components for each roadway light that minimizes the manufacturing cost per light.

a) (2 pts) Specify the decision variables.

b) (3 pts) Define the objective in terms of the decision variables defined in a).

c) (15 points) Build a model in Excel and title it Model A.

(i) Explain the constraints in your model.

(ii) According to Excel Solver, what is the minimum manufacturing cost per light?

(iii) List the 10 key components for the light.

Part B: Lowest Replacement Cost

Although LightWise selected the best suppliers in the industry, all components have the potential for inherent defects during the five-year guarantee period that the client requested. For instance, Component 2 supplied by C21 (see Table 1) has a probability ratio of 0.043 of malfunctioning during the five-year guarantee period. If this component malfunctions, LightWise will need to replace it. The roadway light system was designed in such a way that the failure of any one of the 10 components would be statistically independent. Therefore, if any one component fails, it would not affect any of the other components; only that specific component would need to be replaced.

LightWise has set a budget cap of $5,300 per roadway light for this project, which includes the manufacturing cost and potential replacement cost for any defective components within the five-year guarantee period. To ensure that the project stays within budget, the second task is to build an optimization model that selects the arrangement of 10 key components that minimizes the replacement cost per light while staying within the $5,300 budget cap.

a) (5 pts) Specify the objective for this new problem.

b) (10 points) Build a model in Excel and title it Model B.

(i) According to Excel Solver, what is the minimum replacement cost per light?

(ii) List the 10 key components for the light and the manufacturing cost.

Part C: What-if Analysis

Let us assume that component and pre-assembly costs are at their minimum and the probability of defects cannot be lowered any further. To analyze the trade-off between quality and different budget levels, we need to carry out a sensitivity analysis. We will consider the replacement cost and the number of FCC certifications as measures of quality. We will vary the budget levels from $5100 to $5600 with an increment of $50.

a) (9 pts) Based on the optimization models we built earlier, calculate the total cost (manufacture + replacement cost), the replacement cost, and the number of FCC certifications for each budget level. Generate plots that can help to analyze the trade-offs..

b) (6 pts) Discuss the results in (a) and provide a recommendation for LightWise.

Table 1 Components list for LiteWise

Component #	Supplier	Defect Rate	FCC Certified	Cost ($)	Extra Pre-Assembly Cost ($)
1	C11	0.037	1	1380.00
1	C12	0.017	1	1668.00
1	C13	0.015	1	1464.00
1	C14	0.018	1	1620.00
2	C21	0.043	1	278.40
2	C22	0.013	0	289.20
2	C23	0.060	1	252.00
2	C24	0.026	1	264.00
2	C25	0.024	1	270.00
3	C31	0.034	1	224.00	710
3	C32	0.100	0	212.00	587
3	C33	0.011	1	236.00	647
3	C34	0.091	0	219.60	680
4	C41	0.031	1	271.00
4	C42	0.012	1	278.00
4	C43	0.021	1	257.00
4	C44	0.020	1	292.00
4	C45	0.016	1	299.00
5	C51	0.068	0	213.00
5	C52	0.085	0	221.25
5	C53	0.075	1	229.50
5	C54	0.067	0	218.00
6	C61	0.014	1	684.00
6	C62	0.014	1	596.00
6	C63	0.061	1	460.00
6	C64	0.020	1	560.00
7	C71	0.016	1	570.00	109
7	C72	0.013	1	620.00	107
7	C73	0.030	1	540.00	88
7	C74	0.057	1	500.00	131
8	C81	0.035	1	275.00
8	C82	0.058	1	257.00
8	C83	0.063	1	260.00
8	C84	0.046	1	300.00
8	C85	0.014	1	310.00
9	C91	0.019	1	420.00
9	C92	0.029	0	404.00
9	C93	0.059	0	388.00
9	C94	0.091	0	388.00
9	C95	0.035	1	412.00
10	C101	0.077	0	203.00
10	C102	0.031	0	215.00
10	C103	0.038	0	212.00

LightWise is a company that specializes in the manufacturing of lighting products using light-emitting diode (LED) technology, with a strong focus on indoor LED lighting. Recently, the company has expanded its product line to include roadway LED lights that are both durable and eco-friendly. LightWise was recently awarded a contract to supply and install 10,000 units of 50 -watt ecological roadway lights in several towns within one of Maryland's counties. The roadway light model that meets the contract specifications consists of 10 key components, including a solar panel, LED tube, integrated circuit, capacitor, glass cover, and aluminum body. Each of these 10 components is sourced from 3-5 suppliers, with some requiring pre-assembly that incurs additional labor costs. For example, component 7, supplied by C71, would cost LightWise $570 plus an additional $109 if selected for the roadway light project. The contract required that at least seven out of the 10 key components in the roadway light be certified by the Federal Communications Commission (FCC). LightWise's design for the roadway light included components 1,4,6,7, and 8 , all of which were FCC-certified, as indicated in Table 1. During their lab tests, LightWise became aware of certain compatibility issues and complementary features among the different components. The components supplied by C13 and C23 were found to be incompatible, which meant that they could not be assembled together in the project. On the other hand, C21 and C42 were produced from the same supplier and were complementary. This means that if one of these two components was selected, the other component would also need to be chosen. Conversely, if one of these two components was not selected, then the other component would not be selected either. LightWise faced the task of selecting the best combination of 10 components, out of the various options available for each component, to assemble the roadway lights for the project. The success of this task could play a significant role in securing LightWise's future. The primary goal of this case study is to identify the optimal arrangement of components that meets the certification, compatibility, and incompatibility constraints for each roadway light assembly