How to Create a Marketing Investment Plan in Excel

This tutorial will show you how to create a Marketing Investment Plan in Excel.

First, I will show you how to analyse and process data from an existing advertising campaign. Then, I will use the analysis result to calculate Cost per Acquisition and other metrics to create a marketing plan and budget.

Lastly, the tutorial will focus on making a Long-Term Marketing Forecast, including the calculation of Revenues, Customer Lifetime Value and Return on Investment.

Download my Marketing Investment Plan Excel Template to follow the examples used in this tutorial.

You can also watch the video version of the tutorial at the end of this post.

Step 1. Analyse Marketing Data from Existing Campaign

To create a Marketing Investment Plan, you will need to estimate how your marketing budget converts into users. This tutorial assumes that you have already run a test advertising campaign or have comparable data.

To calculate the marketing metrics for your marketing investment plan, you will need a data set with the following breakdown:

• Marketing Channel – e.g. social media, online ads
• Number of Conversions – e.g. purchases or subscriber sign-ups
• Cost of Advertisement – the amount of money spent on a campaign
• Date – if you run a campaign for a more extended period and want to analyse monthly trends
• Impressions, Clicks (optional) – those metrics will be helpful if you want to examine your campaign further

You can calculate metrics such as Cost per Click, Cost per Conversion, or CPA using the values from the dataset. Note that the Cost per Acquisition will be one of the key metrics to create a Marketing Budget and Revenue Forecast.

Learn More: Read my post on How to Analyse Data in Excel with Power Query and a Pivot Table or How to Use Python and Pandas for Data Consolidation and Transformation to learn more about data analysis and transformation.

Step 2: Use a Marketing Budget to Estimate Customer Growth

To create a marketing budget, start with assumptions about your expected monthly marketing spend and the Cost per Acquisition (CPA). Dividing the spending by the CPA will result in the projected number of new customers. For example, with a marketing budget of $1,000 and a CPA of $10, you will forecast 100 new customers (i.e. $1,000 / $10 = 100)

You can expand your marketing budget further by breaking it into marketing channels and estimating their corresponding CPAs.

For example, my Marketing Investment Plan Template assumes the following marketing mix:

• Internet Advertising (e.g. Google Ads)
• Social Media (e.g. Facebook, Instagram)
• Offline Channels – for example, posters or leaflets advertising
• Other / Experiment – any other marketing channels that you may want to test in the future

The template takes data from digital marketing campaigns and converts it into a monthly view. It breaks down the historical investment into marketing channels, the number of new acquisitions and the resulting cost per acquisition.

With the expected marketing budget and cost per acquisition, you can now estimate the number of new customer growth. As a result, with pricing assumptions, you can also forecast customer revenues.

You can read more about marketing mix strategy here [external link].

Learn More: Visit my blog on How to Start a Consulting Business from Scratch to learn more about creating a monthly budget in Excel.

Step 3: How to Use Marketing Investment to Forecast Customer Revenues

With the Cost per Acquisition and Marketing Investment to estimate the number of new customers, you can now use the number to forecast Customer Revenues.

Show the monthly pricing to see the relation between the number of users and the resulting income. For example, if you have 1,000 users in a given month with a charge of $10, the estimated revenue for that period will be $10,000 (i.e. 1,000 x $10 = $10,000)

Finally, add other inputs, such as the pricing index, which reflects expected rising customer fees over time.

You can add more complexity by creating more than one user group with different pricing. For example, my Marketing Investment Plan Template breaks down users into two groups according to their subscription plan: Basic and Premium.

In addition, you can split the revenues within the group. For example, you can show the pricing for the initial, first-month users and then charges that will apply after that.

Learn More: Visit my tutorials on How to Build a Cash Flow Forecast in Excel or How to Create a Telecom Investment Model in Excel for more examples of forecasting Customer Revenue.

Step 4: Add Other Customer Costs to Calculate Net Revenues

In addition to marketing investment, include any variable and fixed expenses directly related to providing the service to the customers.

Variable costs can be revenue share payments to the third parties, royalties and licencing fees or processing the payments.

My Marketing Investment Plan Template assumes two methods to calculate variable costs:

• Variable Cost as a percentage of Revenues: the expense is directly related to the amount of forecast revenue. For example, a revenue share model would estimate the cost to a third party as a percentage of received fees (e.g. with a 30% share and $100 of revenue, the expense would equal $30),
• Fixed Cost per User: the expense is related to the number of customers. For example, to calculate software licensing costs, try multiplying the number of users by an average monthly fee (e.g. with a $2 charge and 100 users, the expense would equal $200)

For the fixed cost, you can add a Cost Price Index calculation to increase the expense over time.

To forecast Customer Net Revenues, subtract the Customer Costs from the Customer Revenue forecast.

Learn More: See my tutorials on How to Build a Cash Flow Forecast in Excel or How to Create a Subscription Model with Churn Calculation for more examples of calculating Customer Costs.

Step 5: Summarise Marketing Investment and Cash Flow Forecast

Show the Marketing Investment and resulting Customer Net Revenue Forecast as an annual view. The yearly summary will allow you to analyse long-term trends in your estimates.

In addition, show the plan’s inputs next to the summary. Having assumptions and the output on the same page makes it easy to see how input changes affect forecast revenues and costs.

For example, my Marketing Investment Plan Template presents the annual cash flow starting with the main drivers, such as the marketing spend, the Cost per Acquisition and finally, the number of users added. It then follows with revenues, expenses and cash flow contribution.

To the plan summary’s right, I listed the main assumptions, such as monthly subscription fees, expected monthly marketing investment and customer costs.

Note that the cumulative cash flow will allow you to calculate the level of investment required to fund the marketing plan.

Learn More: Visit my tutorials on How to Create a Telecom Investment Model in Excel or How to Create a Subscription Model with Churn Calculation for more examples of monthly cash flow forecasts.

Step 6: Include Churn to Calculate Customer Lifetime Value

Remember to include churn assumptions when creating a Marketing Investment Plan if it depends on subscriptions or expected recurring payments. Churn indicates the number of subscribers your business will retain over time. Therefore, it will be necessary to calculate the expected average user lifetime and its value.

My Marketing Investment Plan Template breaks down customer churn into two categories:

• Initial User Retention: calculated as the percentage of users renewing their subscription after signing up. For example, 100 new sign-ups with 75% retention will result in 75 users renewing their subscriptions.
• Monthly Churn Rate: the ratio of the user base that will leave the service in a given month. For example, with 1000 subscribers and a 5% churn rate, the plan will show a churn of 50 users.

The example below is a simulation of the monthly impact of churn on an initial cohort of 100 users.

Multiply the user’s expected lifespan value by the average revenue to calculate Customer Lifetime Value. For example, an average user lifespan of 18 months with a monthly net income of $10 will result in a Customer Lifetime Value of $180 (i.e. 18 x $10 = $180).

Note that even a small change in churn assumption may significantly impact the overall profitability of a marketing investment plan.

Learn More: Visit my tutorials on How to Use Cohort Analysis to Calculate Retention and Churn Rate in Excel or How to Create a Subscription Model with Churn Calculation for more examples of analysing churn and calculating user lifetime value.

Step 7: Calculate the Return on Marketing Investment

With an estimated User Lifetime Value, you can now calculate the return on your marketing investment. The formula is relatively simple: subtract the invested amount (i.e. cost per acquisition) from the user lifetime value. Then, divide that resulting figure by the investment.

For example, with a User Lifetime Value of $100 and a cost per acquisition of $80, the Return on Investment (ROI) will equal 25% ($100 LTV – $80 Marketing Investment = Return of $20. Then, $20 Return / $80 Investment = ROI of 25%.

My Marketing Investment Plan Template tracks and amortises the cumulative user lifetime value over time. As a result, you can estimate your user base’s value at any point in your marketing forecast and calculate returns on the overall investment.

Notice that in the above example, the basic plan users’ cost per acquisition is the lowest, but so is their Lifetime Value and ROI compared to the Premium customers. Hence, the latter group is more profitable over time, although they require more marketing investment upfront.

Learn More: Visit my tutorials on How to Create a Telecom Investment Model in Excel or How to Create an Excel Financial Model with Debt Financing for more examples of valuation estimates and Return on Investment calculations.

Step 8: Stress Test Marketing Investment Scenarios

With the annual summary, a list of inputs and the calculation of returns, you can now easily see how a change in assumptions affects the overall profitability of the marketing plan.

For example, my Marketing Investment Plan Template allows you to run a what-if analysis. You can change the initial marketing spend and adjust its growth or decline over time. Then, adjust the pricing and the pricing plan uptake.

Check the returns on investment metrics to see how the changes affect the overall profitability and sensitivity of the plan. For example, I have used Excel’s data table to summarise and visualise the results of my sensitivity analysis. 

Stress testing the Investment Plan’s assumptions can be a good way when choosing the optimal marketing strategy. The results will help you decide how to allocate your investment and how those decisions will affect the overall profitability of your marketing plan.

Learn More: Visit my tutorial on How to Create a Telecom Investment Model in Excel or How to Create a Project Finance Model in Excel for more examples of sensitivity analysis and what-if scenarios. 

Summary: How to Create a Marketing Investment Plan in Excel

This tutorial started with an introduction to analysing the existing marketing data. Then, it discussed extracting the main metrics from the data to create a marketing budget and forecast customer revenue. Finally, it covered calculations of long-term customer value and returns on marketing investment.

Here is the summary of the main steps:

1. Calculate the Main Metrics from the Existing Data. You will need Cost per Acquisition (CPA) to estimate the number of future users.
2. Forecast Customer Growth. Take the expected marketing investment and divide it by the predicted CPA.
3. Forecast Customer Revenues. Multiply the forecast users by the monthly pricing inputs.
4. Calculate Net Revenues. Add customer costs to estimate the users’ overall net cash flow contribution.
5. Summarise Marketing Investment and Cash Flow. Add an annual revenue and cost view to analyse trends and the plan’s profitability.
6. Calculate Customer Lifetime Value. If the business model relies on recurring payments from a user, estimate their lifetime value by including churn assumptions.
7. Calculate the Return on Investment. Divide the overall marketing spend by the customer lifetime value to calculate the overall profitability of the plan.
8. Stress Test the Plan. Check the model’s output to test its sensitivity to changes in the primary assumptions.

Download Marketing Investment Plan Template

Click the button below to download my Marketing Investment Model Template in Excel.

The spreadsheet is an example of how to convert a marketing budget into a long-term customer revenue forecast. It contains sample data from an advertising campaign and analyses it to calculate the cost per acquisition. Then, it combines the output with an estimated marketing investment and forecasts customers and revenue.

The Excel file contains the following tabs and sections:

• Data: sample output from an advertising campaign containing costs, impressions, clicks and conversions.
• Actuals: transforms the advertising data into a monthly view and calculates the acquisition and marketing costs per customer type.
• Forecast: an estimate of the future growth and revenue based on the existing customer data.
• Summary: an annual view of actuals and forecast with the main assumptions and the calculation of the return on marketing investment.
• Customer Lifetime Value Calculator: estimates an average customer lifespan and combines it with revenue and cost assumptions to forecast an overall financial contribution of an average user.

 

Video: How to Use the Marketing Investment Plan Template

For more details and a step-by-step explanation of using and creating the Marketing Investment Plan Template in Excel, watch my video tutorial below:

 

Get in Touch

Hi, my name is Jacek, and I love Excel. I hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about Marketing Investment Plans and Financial Models in particular or financial analysis in general, don’t hesitate to get in touch.

Click here if you would like to book my One-to-One Financial Modelling Crash Course. For more Financial Modelling Tutorials, visit this page. If you need help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.

Learn More:

How to Use Cohort Analysis to Calculate Retention and Churn Rate in Excel – this tutorial discusses calculating and analysing churn using existing customer data.

Creating a Subscription Model with Churn Calculation – here, you can learn more about forecasting customer revenues, growth and lifetime value.

Merger and Acquisition Model in Excel – this post shows an alternative example of using the revenue to value a company and its user base.

How to Create a Telecom Investment Model in Excel – learn more about integrating user revenue forecast into a cash flow model.

Analyse Data in Excel with Power Query and a Pivot Table – this is a practical example of using Excel to analyse and transform data.

How to Use Python and Pandas for Data Consolidation and Transformation – an introduction to using Python for data extraction and manipulation.

How to Build a Cash Flow Forecast in Excel – another example of predicting and valuing customer revenue in a cash flow model.

How to Create a Capital Investment Plan in Excel – learn more about including variable costs based on customer growth in a financial model.

Your First Steps in Excel – Beginner’s Crash Tutorial – a quick introduction to creating and using spreadsheets.