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!

• 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.