Based on the data please help out with the following questions. Please also take a look at units for the cells as some units are in dollars and some are in euros. For this analysis I want to use euros, we can assume 1 dollar is equal to 0.85 euros. I need exact answers and excel formulas that are used. thank you!

	Advertising (K $)	Promotion (K )	Sales (K Units)	Price ($/unit)	Contribution Margin ($/unit)	marketing expenses (K$)
1-Nov-15	1050	2600	2810	7.1	1.67	3650.00
1-Dec-15	1100	2750	3340	7	1.65	3850.00
1-Jan-16	1180	2750	3560	7	1.65	3930.00
1-Feb-16	1000	2000	2420	7.05	1.66	3000.00
1-Mar-16	1000	1800	2430	7.05	1.66	2800.00
1-Apr-16	900	2200	2350	7.1	1.68	3100.00
1-May-16	950	2300	2400	7.1	1.68	3250.00
1-Jun-16	1000	2350	2600	7.05	1.65	3350.00
1-Jul-16	1000	2250	2550	7.05	1.65	3250.00
1-Aug-16	1050	2300	2510	7.1	1.66	3350.00
1-Sep-16	950	2400	2520	7.1	1.66	3350.00
1-Oct-16	950	2450	2600	7.1	1.66	3400.00
1-Nov-16	1100	2675	2780	7.1	1.66	3775.00
1-Dec-16	1150	2710	3235	7.05	1.65	3860.00
1-Jan-17	1185	2750	3415	7	1.64	3935.00
1-Feb-17	1050	2000	2500	7.05	1.64	3050.00
1-Mar-17	1000	1750	2365	7.1	1.66	2750.00
1-Apr-17	900	2415	2555	7	1.63	3315.00
1-May-17	1075	2500	2700	7	1.63	3575.00
1-Jun-17	1050	2460	2670	7.05	1.64	3510.00
1-Jul-17	1050	2400	2650	7	1.64	3450.00
1-Aug-17	1100	2460	2700	7	1.63	3560.00
1-Sep-17	900	2600	2620	7	1.63	3500.00
1-Oct-17	950	2350	2570	7	1.63	3300.00
1-Nov-17	1195	2750	2855	7	1.63	3945.00
1-Dec-17	1185	2915	3335	6.95	1.62	4100.00
1-Jan-18	1190	2915	3485	6.95	1.62	4105.00
1-Feb-18	1050	2250	2595	7	1.63	3300.00
1-Mar-18	1050	2000	2500	7	1.63	3050.00
1-Apr-18	1150	2625	2785	7	1.63	3775.00
1-May-18	1060	2585	2715	7	1.62	3645.00
1-Jun-18	1100	2835	2925	6.95	1.62	3935.00
1-Jul-18	1000	2790	2745	6.95	1.62	3790.00
1-Aug-18	1050	2790	2780	6.95	1.62	3840.00
1-Sep-18	1150	2915	2880	6.9	1.61	4065.00
1-Oct-18	1100	2750	2795	6.9	1.61	3850.00

• Calculate Monthly Net Contribution and ROI:
• Using the data in the spreadsheet, calculate the monthly net contribution (in thousands of euros) and the return on investment (ROI).
• Plot the data to visualize trends and patterns. Analyze what you observe from the plots regarding sales and profit.

• Identify Key Drivers of Sales & Profit:
• Determine the key drivers of sales and profit based on your analysis of the plotted data.

• Evaluate Advertising and Sales Promotion Spending:
• Assess whether Atrium has been overspending or underspending on advertising and sales promotion.
• Provide a quantitative estimate of how much they are overspending or underspending.

• Propose Budgets for the Next Year:
• Based on your analysis, propose advertising and sales promotion budgets for the upcoming year.
• Justify your recommendations with data.

• Regression Analysis:
• To gain deeper insights, perform a multiple regression analysis to separate the effects of advertising and promotion while controlling for other sales drivers.
• In Excel, navigate to Data > Data Analysis > Regression. If 'Data Analysis' is not visible, install the 'Analysis Tool Pak'.
• For the regression:
  • Set 'Input Y Range' to sales units (e.g., 'b2:b37').
  • Set 'Input X Range' to advertising and promotion (e.g., 'c2:d37').
• Review the regression output, focusing on R-squared and coefficients.

• Calculate Elasticities:
• Calculate the elasticities of sales to marketing using the coefficients from the regression. Use the average monthly sales and marketing data from the last year as baselines.
• Alternatively, perform a log-log regression by calculating the logarithm of sales and marketing variables, and use these in the regression tool.

• Defend Your Choices:
  • Reflect on your regression model. Are there any sales drivers that may have been omitted, leading to potential bias? Ensure you can defend your choices in a hypothetical meeting.

• Optimal Marketing Budget:
• Use Wright's formula to calculate the optimal marketing budget by multiplying each marketing elasticity by the expected contribution margin and sales.
• Sum the optimal advertising and promotion budgets to determine the overall optimal marketing budget.