The “Costs” sheet is dedicated to making inputs and providing calculations regarding all operating expenses (= overheads / indirect costs). The inputs and calculations are subdivided into three divisions:

1. Marketing and Sales Costs

2. Research and Development Costs

3. General and Administration Costs

Payroll costs are already linked in from sheet “Human Resources”. For each section there are multiple input rows available. Variable descriptions can be changed (input cells). The input values in column "F" allow for setting of individual credit periods (number of months => see below screenshot). Allowed input values are integers from 1 to 4 (or 0 for immediate cash payment => same month).

 

Allocation of selected M&S costs to Products/Services

It is necessary to allocate certain Marketing & Sales costs to the products/services (= offerings) to allow for detailed calculation of sales metrics (Customer Gross Profit, Customer Lifetime Value (CLTV), etc.) on the "Sales Summ" sheet. This allocation, including the breakdown into separate costs for customer acquisition and customer retention, is done automatically based on only two user inputs. Calculations can be found right below the Marketing and Sales Costs section on the "Costs" sheet.

1. First, decide which cost categories out of the Marketing and Sales section should be allocated by selecting "1" in column E in the corresponding row.

2. Second, enter a percentage value for the allocation of marketing expenses for customer acquisition. Enter this global input in the miscellaneous section on the "Inputs" sheet.

The modell automatically calculates how much of the allocated costs (selected in step 1) to use for customer acquistion. The balance of the expense is allocated to customer retention (see screenshot below).

The costs to each single product/service (offering) are distributed automatically, based on net sales (see red marking in screenshot). If you are not satisfied with the automatic distribution, you may overwrite the fomulas with individual percentage values.