How to Create a Marketing Investment Plan

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

First, it explores analysing and processing data from an existing advertising campaign. Then, it uses the analysis result to calculate Cost per Acquisition and other metrics to create a marketing plan and budget.

Lastly, the tutorial focuses on making a Long-Term Marketing Forecast, including calculating Revenues, Customer Lifetime Value and Return on Investment.

Download my Marketing Investment Plan 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, 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, use 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 Microsoft 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 Create a Monthly Budget Forecast to learn more about budgeting and planning.

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, with 1,000 users in a given month and 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 Microsoft Excel or How to Create a Telecom Investment Model 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 Microsoft 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 helps 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 allows calculating the investment level required to fund the marketing plan.

Learn More: Visit my tutorials on How to Create a Telecom Investment Model 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 a business will retain over time.

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.

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

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 or How to Create a 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 helps 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 Microsoft 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 to choose 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 or How to Create a Project Finance Model for more examples of sensitivity analysis and what-if scenarios. 

Summary: How to Create a Marketing Investment Plan

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.

The Excel 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 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 the overall financial contribution of an average user.

The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].

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 Microsoft Excel, watch my video tutorial below:

 

Get in Touch

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

Explore my other tutorials to learn more about financial modelling or data analysis. If you need further support, find out about my One-to-One Training and Financial Modelling Services.

Please note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.

Learn More

Cohort Analysis to Calculate Retention and Churn Rate in Microsoft 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.

Monthly Budget Forecast – explore another way of converting actuals into near and long-term revenue and cost projections.

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

Telecom Investment Model – learn more about integrating user revenue forecast into a cash flow model.

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

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

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

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

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