Return on Marketing Plan Information
EGOLDEN3
MKT365ROMIFinal2.xlsxBasic ROMI Calc
| Marketing ROI Campaign Planning Tool | ||||
| Campaign Name | Le Grill | |||
| Campaign Manager | Turki Julaydan | |||
| Date Updated: | December 9, 2018 | |||
| Return on Marketing Plan Information and Assignment Details: | ||||
| You have been asked to plan a marketing campaign for "Le Grill", where you must specify your anticipated marketing investment and translate that figure into a return on investment (ROI) percentage. | ||||
| Use the assumptions and the marketing expense worksheet located below the ROI spreadsheet to help you fill out the information required to calculate the ROI of your planned marketing campaign. Once you | ||||
| have arrived at your ROI percentage, please answer the questions in the marketing plan Word doc. Submit both this worksheet and your case study to Canvas. | ||||
| ASSUMPTIONS | ||||
| Directions: Assume the following to help you plan your "Le Grill" marketing campaign ROI and answer the case study questions for the marketing plan. | ||||
| Assumption 1: Marketing Budget | You have been allotted a maximum of $165,000 for marketing expenses, not including staff. | |||
| I would use $50,000 for campaign development to arrange the events that will take place as the campaign itself. For the email marketing, I would use $10,000 for new customers who are willing to buy Le Grill ex, fathers or other potential customers. For the events and trade shows, I would use $15,000 for the actual event and all its need ex, chairs and tables etc... For the sponsorship, I would use $30,000 for the partners that relate to fathers ex, shaving blade company or others. For the social media, I would use $60,000 to advertise on Instagram, YouTube and Facebook. | ||||
| Assumption 2: Staff Expense Budget | There are three employees that will be working on this campaign, one with an annual salary of $112,500, the second with an annual salary of $72,830, and | |||
| the third with an annual salary of $36,400. Assume it will take approximately 6.25 days to complete the campaign. | ||||
| Assumption 3: Offers/Incentives/Temporary Discounts | Assume there are no other offers/incentives/temporary discounts applicable to this marketing campaign. | |||
| Assumption 4: Other Expenses Outside Marketing Budget | Assume there are no other expenses outside of the marketing budget that are applicable to this marketing campaign. | |||
| Assumption 5: Marketing Impact | Assume you want this marketing campaign to reach a target of 27,000 people (meaning the number of people you want to drive to your website to buy "Le Grill") | |||
| Assumption 6: Net Conversion Rate of Incremental Sales | Assume that of the 27,000 people who go to the website, 594 purchase "Le Grill." | |||
| Assumption 7: Average Revenue per Sale or Customer (NPV) | Assume your NPV of each "Le Grill" Sale will net you $875. | |||
| Assumption 8: Gross Margin | Assume your gross margin is 38% | |||
| Assumption 9: Reduction to Gross Margin from New Sales | Assume that 30% of the profit from incremental sales is refunded to customers who filed complaints within the money-back guarantee period. | |||
| Assumption 10:Product Quality Improvement and A/B Testing *note: use spreadsheet to | You assume that if the product quality of Le Grill had been better, this marketing campaign would have been successful. | |||
| calculate; and answer questions in the Word doc. Submit the original spreadsheet that reflects | Previously, you had not run an A/B test, and because you know product quality has been improved, you run an A/B test using your current website as the control. | |||
| Assumptions 1-9. | You relaunch a marketing campaign after product improvements are made. You find that your conversion rate increases to 3.8% and refunds drop to 7%. | |||
| Note: Light blue cells are for input of campaign-specific data. All input values should be Net Present Value. | ||||
| Return on Marketing Plan | ||||
| Marketing Expenses (excluding offer costs) | Marketing Impact | % Progression | Quantity | |
| Campaign Development | $50,000 | Target Reached | 100% | 27,000 |
| Mass Media Advertising | $0 | |||
| Direct Mail & Mailing Services | $0 | |||
| E-mail Marketing | $10,000 | Incremental Sales | 2.2% | 594 |
| Events & Trade Shows | $15,000 | (net conversion rate) | ||
| Sponsorships | $30,000 | |||
| Trade Promotion / Channel Marketing | $0 | Average Revenue per Sale or Customer (NPV) | $875 | |
| Social Media | $60,000 | Incremental Revenue | $519,750 | |
| Other | $0 | |||
| Marketing Budget | $165,000 | Average Gross Margin % Jim Lenskold: Jim Lenskold: The Gross Margin % is the Net Profit % without a deduction for marketing. If marketing staff is entered as an investment, the gross margin should not include a deduction for marketing staff expenses. (an increased % of revenue flows to gross margin and then marketing and staff investments are deducted to get to net profit) | 8.0% | |
| Profit from Incremental Sales | $41,580 | |||
| Marketing Staff Expense | ||||
| Daily Rate Lenskold: Lenskold: Click to access input section that will calculate daily rates | # of Days | Offers/Incentives/Temporary Discounts | ||
| Staff Level 1 | $450 | 250.00 | Offer Cost per Sale (for sales driven expenses) | $0 |
| Staff Level 2 | $364 | 250.00 | Incremental Sales (from above) | 594 |
| Staff Level 3 | $182 | 250.00 | Reduction to Gross Margin from New Sales | |
|
Jim Lenskold: Jim Lenskold: This expense amount is deducted from the Profit from Incremental Sales | Marketing Staff Expense | $249,000 | ||
| Other Expenses (outside of marketing budget) | ||||
| Offers/Incentives/Temporary Discounts | Cost per Sale (for sales driven expenses) | $0 | ||
| Cost per Baseline Sale | $0 | Incremental Sales (from above) | 594 | |
| Baseline Sales | 0 | Less Inc'l Cost of Goods (Var) | $0 | |
| Lost Margin Existing Sales | $0 Jim Lenskold: Jim Lenskold: This expense amount is added into the investment. |
|||
| INCREMENTAL GROSS MARGIN | $41,580 | |||
| Other Expenses (outside of marketing budget) | ||||
| Inc'l Cost of Goods (Fixed) | $0 | RETURN (i.e., NET PROFIT) | ($372,420) | |
| ROI | -90.0% | |||
| TOTAL MARKETING INVESTMENT | $414,000 | |||
| Spreadsheet adapted from the Lenskold Group | ||||
| Marketing Staff Expense Worksheet | ||||
| Formula to Calculate Daily Staff Rate | ||||
| Benefits = (as a percent of salary) | 25% | |||
| Total Annual Cost = Salary + Benefits | ||||
| Working Days per Year | 250 | |||
| Daily Rate = Total Annual Cost / Working Days | ||||
| Inputs | Annual Salary | Daily Rate | ||
| Staff Level 1 | $112,500 | $450 | ||
| Staff Level 2 | $72,830 | $364 | ||
| Staff Level 3 | $36,400 | $182 |
