Quick Start

Quick Start Guide

Overview

Marketing Profit Plans (MPP) is a versatile toolkit of Excel templates to build marketing plans focused on bottom line profits.

  • Plans are created for two types of marketing:
    • Web Pay Per Click known as PPC
    • Direct Marketing: Direct mail, space advertising or special event programs.
  • Plans can be setup three ways for both DM and PPC:
    • A single advertisement
    • A series or combination of up to 15 single plans
    • A plan of up to 199 segments with product and operational parameters all the same but each segment having unique parameters like DM response rates or Pay Per Click budgets.

To Install MPP

MPP runs on top of Microsoft Excel. Before starting MPP, be sure the Excel Options are set to allow macros to run. You do this at File /Options /Trust Center / Trust Center Settings/ Macro Settings/

Click the bottom choice: “Enable VBA macros (not recommended; potentially dangerous code can run)”

Before Starting

Create a new folder to contain MPP.xlsm

MPP plans can be printed or can be saved as new files or PDF’s. We recommend you place the MPP program in a new folder where you can add other new folders below it to store plans.

Create a few folders under the MPP folder to contain workbooks and PDF’s that MPP creates. A hierarchy of folder names based on how you manage campaigns or ads. Folder examples are Google PPC, Customer DM, Outside List DM, Publications, Dates, Tests, etc.

Start Up

Place MPP in the folder for it. Double click on MPP. It will open to a beginning tab. The second tab is DM Plan. Click that and you are off and running,

Once MPP is opened, if you open or create another Excel Workbook, it will not have all the controls on the ribbon and there will be no headings.

To use another Workbook side by side with MPP, open the other Workbook first.

Using MPP

The workbook sheet tabs and plan types MPP creates are:

DM Plan – Direct Marketing – a double page with plan and analytics based on response rates.

DM Plan Multi – Similar to DM Plan without analytics and up to 15 separate plans totaled. Note Multi is a large sheet and to help navigation and fitting screens has a toggle button to turn zoom on and off.

DM Group – Similar to DM Plan but with 3 to 199 rows for segment source codes. Note, the example sheet in MPP and the data in the sheet are for example only. To create a DM Group sheet, use the worksheet controls to choose how many rows (3 to 199) to set up a new external worksheet. The new worksheet name will end with a dash (–) so you can change the name. The process will also ask you to select the folder to hold the new worksheet. (That is why a folder organization plan will be helpful.)

PPC Plan – Web based Pay Per Click – single page.

PPC Plan Multi – Similar to DM Multi Plan but focused on PPC parameters.

PPC Group – The same style as DM Group but focused on PPC parameters.

On every plan, the first field is for a plan number. Because MMP is such a good what-if tool, you might use a prefix to the number like WF (for what-if) and FP (for final plan) as a prefix to a number. After you make a few plans and get used to the software you might want to make a hierarchy to plans organized.

Important: None of the plans are saved or stored internally when you close MPP. You can save Single and Multi plans as  PDF’s or print the sheets. Group sheets are set up as new Excel workbooks, so they are savable. But every time you start MPP, the templates are in a blank state.

Starting a new plan

On every plan, the first field to fill in is a plan number. Because MPP is such a good what-if tool, you might use a prefix to the number like P (for real, final plan) and WF 9 for a what-if. After you make a few plans and get used to the software you might want to come back to this topic to keep your work organized.

If a cost field is not appropriate for a plan, the field can be left blank or a zero can be inputted.

The basic structure of the plans is the input variables are all above the horizontal dotted line midway down the sheet. Everything below that double line are calculations that result in an Income Statement and other information.

The open input fields are white. All other fields are protected and light grey. The entire workbook is protected so the formulas and features like printing or creating a PDF cannot become corrupted. The Excel ribbon features are also disabled in MPP.

You close MPP by clicking the upper right corner X. There is no “File Save As.”

The Glossary

The Glossary is a sheet in the workbook and explains all the input field numbers and calculations. A separate PDF copy is included with the software zip file. This is for reference as you fill in the fields.

A word about accuracy.

The MPP Income Statement accuracy is completely dependent on the input numbers. You can use ball park estimates when you first start planning, but please start researching the actual cost and expense numbers to input in the data fields. We recommend starting that research as soon as possible. But before investing actual advertising money based on the MPP Income Statement calculations, be aware of the accuracy of the inputs.

To get a feel for how MPP works and how easy it is to create a plan, for your first plan we suggest you use ball park numbers.

First Plan I – Input Overview

First Plan I – Input Overview

The best template to start your first plan is with either the DM Single or PPC Single templates, depending which type of advertising channel you will be using. DM covers direct response campaigns, space advertising, and specialized promotions like events. (We will cover those separately.) PPC covers Pay Per Click internet advertising.

This post explains the different sections.

Reference Data

The top left section for Plan Number and other descriptive information is for storing and retrieving plans.

MPP plans can be printed or can be saved as new files or PDF’s. We recommend you place the MPP program in a new folder where you can add other new folders below it to store plans.

Create a few folders under the MPP folder to contain workbooks and PDF’s that MPP creates. A hierarchy of folder names based on how you manage campaigns or ads. Folder examples are Google PPC, Customer DM, Outside List DM, Publications, Dates, Tests, etc.

Product / Service Data

This section contains input relevant to the product, packaging and operational costs of fulfilling sales orders. See the detailed explanations in the Glossary. These costs are not included in General & Administrative Expense for several reasons. If these operations are outsourced, the vendors charges are based on the specifics of the individual products. If all fulfillment is done internally, tracking the fulfillment costs on each unit sold allows the pricing decision to be based on actual costs.

Transaction Costs

This section contains Credit Card Discount % – Credit Card Use Rate % – Sellers Fees % charged by web commerce platforms used for advertising, like Amazon, Etsy, eBay, and shopify – and G & A Expense %. G & A is your company General and Administrative, which is things like rent, utilities, insurance, and some manpower. The manpower (and rent – overhead) costs of packaging the product, warehouse packing and shipping, distribution, order entry & customer service are not included in G&A because they are calculated separately in the Product/Service Data section.

Sellers Fees % that are related to product warehousing, packing, shipping and returns should be included in Product/Service Data sections. The Sellers Fees % is the commission amount the seller’s distribution channel charges for using their platform.

Timing Data

This is the number of days to calculate the outflow and inflow dates in the Cash Flow Data section. The timing starts from Promotion Date input.

Direct mail Promotion Date should be the estimated date the mail will be received in the mail box.  This time span is longer for advertising class mail than first class mail. When entering the Response Flow In Days use judgement and keep a record of the basis the establish the input. Direct mail campaign responses usually come in over a span of many days, not all at one time. Therefore, most direct mail planners use what is known as the “Half-life” of campaign response as the midpoint for number of days when responses are 50% in as the Promotion Date. Space advertising is the date the magazine or newspaper is delivered or on the newsstand.

PPC internet Promotion Date starts when the campaign is turned on. Convert Process Days is when actual sales start. Keep in mind the Cash Flow Data dates are estimates or indications. All dates are calculated forward, meaning each flow period starts after the Promotion Date. In some cases, inventory must be paid for before the Promotion date. Some advertising purchases can be paid after an ad run. Postage for direct mail campaigns is paid when the mailing enters the postal facility.

Cash Flow Data 000’sDate
Fixed Exp, Adv, Prod, Pack Outflow$0.07/23/2023
Shipment Exp Outflow$0.07/23/2023
Payments Inflow$0.07/23/2023
Net Cash Flow$0.0 

Qualify and Order Ship Rate.

This shows you will create 200 responses.

Promotion date is explained above. The other inputs are explained in the Glossary.

Forecast Data

The strength of Marketing Profit Plan is that every plan has a forecast result based on specific parameters. Direct Marketing plans are intended to get positive results. That’s why the creative almost always has “Calls to Action.” The DM Plan has the key cost variables in the Advertising Data section because these variable can be manipulated to control or improve the profitability of results. If you have no response history upon which to estimate Forecast Data, start with industry averages and what if back and forth to change parameters like costs and other factors to arrive at a profitable forecast.

Special promotions like event participations use the DM Plan template to achieve a forecast the following way. If you cannot forecast any result coming from the promotion expense, enter in the dollar cost in the Advertising Fixed Expense input. This will calculate a loss in the Income Statement for the same amount. But let’s forecast the result of the event the following way. Input 2.0 in Advertising Circulation,  $2.9 in Advertising Fixed Cost, 10.0% in Response Rate, and 100%) at a loss of $2,900.   This is a good time to use this promotion event to introduce the DM Multi template.

PPC forecast responses are determined in the Advertising Data section. The two key drivers are Cost Per Click Maximum (Bid) and Time Period Spend Maximum. The Responses Received are the calculation of Spend Maximum divided by Cost Per Click Maximum.

Group Templates

Both DM and PPC Templates for Multi and Group use the same basic inputs, except Groups do not use Timing Data and calculate cash flows.

After the basic campaign strategy is calculated using a Single template, Group is to breakdown the campaign into individual key codes. For example, a DM campaign might consist of 10 different outside mailing lists – or 10 different segments of the house customer list. Group allows each to vary the response rate, mailing cost, circulations, and other parameters to show the detailed view. A PPC campaign uses the same format to allow for different keywords with different Cost Per Click Maximums and Time Period Spend Maximums.

Multi Templates

Multi templates are all-purpose templates. Multis create from 1 up to 15 single plans on one worksheet with each plan in one column. Multi calculates each plan’s results separately and combines them in a total. The calculations for each plan are the same as Single. All plans are combined in the Income Statement and cash flow table.

Multi is for planning comparisons and multi part strategies:
• multi-step sales funnels
• Inquiry to sale conversion programs
• Time periods like month, quarter or year
• Comparing results of different channel types like email, PPC, direct mail, customer vs outside lists

Multi can also display Customer Lifetime Value scenarios and plan retention strategies.

First Plan II – Fast Start Fields

The essential fast start fields to calculate sales.

Not all fields are necessary to create a first draft prelimininary or fast plan.

Some fields, like Plan Number, Description, Group Number, Product SKU, are descriptive and are not needed. They can be left blank.

Not all cost inputs are needed to calculate Sales and Income, as some cost factors might not apply. For example, if your product has no packaging cost that field can be left empty. Only the product Sell Price and Product Cost are needed.

The other essential fields needed to create Sales and an Income Statement are the fields that drive orders and conversions. They are different for DM plans and PPC plans.

The specific fields that are needed are shown below and numbered for Direct Marketing and Pay Per Click Single Plans.

DM Single Plan

DM Single Required Fields

Pay Per Click Single Plan

The five fields to create a basic Income Statement Calculation are identified.