Job Description: SunFlora is a leading company in the production of solar panels at one of the factories in California. The duties of the selected applicant are the followings:a) Make sure the demand for solar panels are satisfied in the following quarters b) Communicate with the department sales and marketing to know what would be the solar panels demand the following quarters;c) Communicate with the customer service department to make sure all customer complaints are satisfied job salary: $95,560Days of work: Remote work Monday to Wednesday. Work at the plant Thursday and Friday. We know the solar panel market has been growing really quick in the past years, and some people are very anxious about the delivery times due to high demand. Some of the new customers needed to wait weeks or even months until the solar panels were completely installed in their houses. Some customers were not willing to wait and decided to go with the competitors (which have lower quality and efficiency, by the way). We needed to check our forecasts from the marketing and sales department and adjust them so this time we dont face that situation and lose customers again! We have captured the sales data for our solar panels and it is available for you to analyze it in the file b)).We trust that the new model for forecasting will be very good and we dont need to worry about inaccurate forecasts anymore, but we need to use those forecasts to make sure we produce enough solar for the coming 4 quarters.The production capacity is 45 thousand solar panels per quarter. Nevertheless, a subcontractor can be hired to have an extra capacity of 10 thousand solar panels perquarter, but that will require an initial (fixed) payment of $1,000,000 each quarter thatwe require its production capacity (there may be quarters in which we dont need thatextra capacity), independently of how many panels it will be producing. Each solarpanel can be sold for $2,500. The production cost is $1,000 per solar panel if they are manufactured in our facilities, and $1,200 if they are produced by the subcontractor.If the solar panels are kept in inventory, it costs $100 per solar panel per quarter, in-dependently if it was produced at our facilities or at the subcontractors. BUS2 194B - Business AnalyticsModule 4 - Nonlinear Optimization third option of giving up the demand of panels that we cannot satisfy, but that would represent an opportunity cost of not selling the panels (at $2,500 each panel not sold Please do the following: a) Present your work progress of parts b)andc)by the end of todays session (50 points).b) Utilize the curve-fitting method to create a regression model (trendline) to predict the follow-ing 4 quarters, based on the deseasonalized data that appears on the file BUS2-194BNLO-Case-Data.xlsx.Hint: you can use the quarter number (1, 2, 3, 4, 5, ..., 46) as the independent variable(variable x) to predict the sales (variable y)c) What would be the optimal production, inventory, and subcontracting plan (how many units per quarter) to satisfy the demand for the following 4 quarters (that you predicted in part b))? How many customers are you willing to give up to the competitors? (50 points).Hint 1: This problem is similar to the second problem in the Module 2 - ProblemHomework 2. Dont worry, the video of the solution is availableHint 2: This problem is interesting in the sense that you need to use the knowledge from Modules 2, 3, and 4 to solve it.

BUS2-194BNLO-Case-Data.xlsx

Solar panels sold by quarter (2007-2018)
Quarter	Solar panels sold (thousand)	Sales (de-seasonalized, thousand)
Q3 '07	0.27	0.35
Q4 '07	1.12	1.16
Q1 '08	2.32	1.79
Q2 '08	1.7	1.75
Q3 '08	0.72	0.95
Q4 '08	6.89	7.13
Q1 '09	4.36	3.35
Q2 '09	3.79	3.90
Q3 '09	5.21	6.84
Q4 '09	7.37	7.62
Q1 '10	8.74	6.73
Q2 '10	8.75	9.00
Q3 '10	8.4	11.03
Q4 '10	14.1	14.59
Q1 '11	16.24	12.50
Q2 '11	18.65	19.19
Q3 '11	20.34	26.70
Q4 '11	17.07	17.66
Q1 '12	37.04	28.50
Q2 '12	35.06	36.07
Q3 '12	26.03	34.17
Q4 '12	26.91	27.84
Q1 '13	47.79	36.77
Q2 '13	37.43	38.51
Q3 '13	31.24	41.01
Q4 '13	33.8	34.96
Q1 '14	51.03	39.27
Q2 '14	43.72	44.98
Q3 '14	35.2	46.21
Q4 '14	39.27	40.62
Q1 '15	74.47	57.30
Q2 '15	61.17	62.93
Q3 '15	47.53	62.40
Q4 '15	48.05	49.71
Q1 '16	74.78	57.54
Q2 '16	51.19	52.67
Q3 '16	40.4	53.04
Q4 '16	45.51	47.08
Q1 '17	78.29	60.24
Q2 '17	50.76	52.22
Q3 '17	41.03	53.86
Q4 '17	46.68	48.29
Q1 '18	77.32	59.50
Q2 '18	52.22	53.73
Q3 '18	41.3	54.22
Q4 '18	46.89	48.51