Estimate media effectiveness using the regression function in the Data Analysis tool, ad-in that has to be activated in Excel. Update the budget optimization tab with info about the last period (expenditures, etc) and media effectiveness. Calculate upper and lower boundaries for investment in each media using a 15% threshold (above or below the last period). Calculate maximum budget using a 3% increase. Calculate total exposure of the future period Create the calculations for Estimated sales based on new media expenditure (which initially is identical as last period) and price of product. Calculate future exposure by using the new media budget as well as the cost per thousand impressions provided. Run “Solver in which you Maximize the total sales deriving from media investment Change the New Media Expenditure Constrain the New Media Expenditure to be between lower and upper bound defined before. Constrain the total media expenditure to be between the last period total expenditure and the ceiling you calculated based on the 3% increase. Constrain the total exposure to be at least 4,400,000. After you solve the budget optimization, compare last with current period ROI (or ROAS) Calculate total predicted sales of the previous period and predicted sales from media investment of the previous period by using the effects calculated through the regression and the respective investments of last period. Calculate total predicted sales of the future period and predicted sales from media investment of the future period by using the effects calculated through the regression and the optimized investments in media. Calculate profd based on expected costs in media, costs in production (80% of the price of product), and Earnings (predicted sales from media*price of last period. Calculate ROI based on calculated profit and investment in advertising.
