Sheet Inputs

To be as flexible as possible, Excel-Financial-Model allows for changing between UK/International or US/Canadian accounting formats and terminologies. The main differences relate to spelling (labour vs. labor for example),  terminology (see table below for examples), and the accommodation of taxes on sales, valued added (VAT) and goods & services (GST).

Note: Printer page sizes (European A4 vs. US Letter) must be set individually within Excel according to your needs.

Selected differences between UK/International and US/Canadian terminology:

This feature is implemented by using Excel’s CHOOSE function.

Syntax:   CHOOSE(index_num, value1, [value2], ...)

with index_num being the named cell “language”. This argument can be 1 or 2. If index_num (language) is 1, CHOOSE returns value1 (= UK Terminology); if it is 2, CHOOSE returns value2 (US Terminology); and so on.

Example: =CHOOSE(language;"Profit and Loss Statement";"Income Statement")

Flexibility is high priority within Excel-Financial-Model, you may change the terminology or add additional text as needed.

You can define different currencies codes or symbols (GBP or £, USD or $, EUR or €, etc.) and denominations (1 or 000s). Currency inputs are independent of your language settings (UK vs. US). We recommend the international standard three-letter ISO 4217 currency codes (EUR, USD, GBP, AUS, etc.).

For more information: http://www.iso.org/iso/home/standards/currency_codes.htm

In this section, enter a start date and the planning horizon. The start date has to be the first day of a month. Otherwise, you will get an error message.

Fiscal Year

It is possible to define fiscal years (FY) which differ from calendar years (CY). In this case use input cell “Last month of financial year” to choose a specific timing. In case the input value is December (Month 12) fiscal year and calendar year are the same. The terms “fiscal year” and “financial year” are synonymous. They both refer to an accounting period of 12 consecutive months beginning on a date other than January 1.

However, to qualify for a different fiscal year, you must meet certain requirements, depending on country-specific laws. We cannot give any advice at this point, as this would go beyond the scope of this manual.

Tax year

A tax year is the accounting period you choose to calculate your income and expenses for taxation purposes. In the current version of Excel-Financial-Model the tax year always equals the fiscal year (i.e. if you set the last month of the fiscal year to December => fiscal year = tax year = calendar year).

Planning Horizon

Excel-Financial-Model allows for a maximum planning horizon of 5 Years (start year plus max. 4 additional years). Depending on the start date and fiscal year you have entered, the first year may be a short (less than 12 months) business year.

Overview of possible input assumptions:

Click image to enlarge

By using the "Active?" switch, you can decide whether your model case should include or ignore each offering. You can change this at any time. When you deactivate an offering, all corresponding input fields are grayed out (see below screenshot) and the deactivated offering is not included in any model calculation (Sales, P&L, Cash Flow, customer metrics, etc.) going forward. You can use this functionality to create various scenarios very quickly and without having to delete all corresponding inputs.

For the contract duration, select from the drop-down menu between monthly, quarterly, semiannual or annual.

Although EFM "Digital Economy" was primarily designed to plan recurring business models, it is also possible to analyze and plan one-time sales models, like offering products or services. In this case, you can skip the input fields (renewals, upsells and existing customers) since they are not relevant to your case.

Note: Churn Rate = 1 - Retention Rate and vice versa (90% retention = 10% churn rate, 20% churn = 80% retention). The retention rate/churn always refers to the selected contract duration.

Optionally, you can define the number of existing customers (for both base and upgrade product) when planning for an established company.

Overview of possible input assumptions:

Click image to enlarge

Enter the (net) sales prices for the planning periods. Note that the input value is per month. Example: If you have a quarterly subscription model for 90 USD, you have to enter 30 USD (i.d. 90 USD/3 months) here. However, cash flow is calculated individually according to your assumptions in the fields billing frequency and billing type.

The one-time set-up fee is optional. This would be for something like a piece of hardware, or a device delivered with the first subscription installment (depending on your business model).

When using input/output taxes consider the percentage of revenue which is liable to VAT and select the appropriate rate. In case you are planning without VAT, GST or Sales Tax, set rate to “zero rate” for each product/service (for more details on input/output tax settings see “VAT / Sales Tax”).

You can also determine bad debts for the specific offering as a percentage of sales within this input section. Sales receipts and input/output taxes (sales taxes, GST, VAT) are automatically adjusted to take account of the bad debt percentage. The cost of bad debts is treated as an expense in the income statements (P&L).

In case you have to consider a revenue share it is necessary to define how much of the total revenue of the given offering should be considered. Enter any revenue share percentage in the event you are hosting for example a SaaS app or other app in an app store or have a marketing alliance that calls for a revenue share. In addition you can choose the basis for revenue share calculation via a drop-down menu, i.e. 1. base product, 2. base product + set-up fee, 3. base + upgrade product, and 4. base + upgrade product + set-up fee.

Billing of revenue share: If you have opted for a revenue share, enter who bills the end-customer (direct billing or by a 3rd party). This determines whether or not gross or net revenue (after any revenue share) is recognized according to international accounting rules. That means, if billed by a 3rd party, the amount reduces revenue. If you are billing directly, the amount is counted as part of the Customer Acquisition Costs (CAC).

Finally, enter a billing frequency (monthly, quarterly, semiannual or annual) and a billing type (in advance or in arrears) for your offering.

Overview of possible input assumptions:

Click image to enlarge

Apart from a lead generation fee you may enter a sales commission expense, which is payable to the sales staff. Percentage input refers to the Total Contract Value (TCV), i.e. revenue per month x contract length. In our example this means EUR 2.70 per new customer (3% of 90 EUR (= TCV) => 30 EUR p.m. x 3 (contract length = quarter))

In addition, it is possible to plan costs for direct labor (of the sales staff). Within Excel-Financial-Model DE the number of new customers is not driven by the number of sales persons (this could be an alternative planning rationale). Instead, the number of new customers per month is part of the relevant offering sheet ("Offering 1" to "Offering 4").

To calculate the number of sales staff necessary and the resulting costs, you have to define on the "Inputs" sheet, how many new customers a single sales executive can acquire per month. You can then decide whether the calculation is fully variable, allowing you to theoretical, partial FTEs (for example, 1,75 sales executives) or in step costs. The latter means that the number is always rounded up to the next whole number. In the example shown above, you need one (full) sales executive for up to 100 new customers. As soon as you enter ("acquire") one more customer per month, you need two sales people, above 200 you need 3, and so on.

Last but not least you can enter an annual salary and (an optional) annual raise percentage.

Overview of possible input assumptions:

Click image to enlarge

Before entering any sales item values, note the units (in column E).

Materials/packaging/goods: Calculate on a monthly recurring basis for each unit sold (unless you have a one-time sales model => not recurring); leave blank if n.a.

Hosting/cloud infrastructure: Includes servers, storage, and third party services used as part of the product (e.g. mail delivery, monitoring, dns, etc.)

Payment processing fees: In case you offer different payment methods or have different service providers, use an average percentage for calculation.

3rd party services: This position refers to services you are reselling/upselling from within your product (For example if customers pay extra (in the form of an upgrade, upsell or in-app purchase transaction)) for some service and you pay a provider part of that amount.

Shipping, postage & repackaging: Note that any input value entered here is calculated each month, independent of contract duration (leave blank if n.a.). This is most appropriate for one-time sales models. In case you want to plan one-time shipping or repackaging costs (for example, if you are sending out hardware with each new subscription), use one of the two additional input rows on the "Offering 1" to "Offering 4" sheets (see comment on above screenshot).

Direct Labor: This section is subdivided into two different blocks:

a.) Direct Labor (in a narrow sense): The number and costs of "Direct Labor Staff"; part of the cost of sales (COS)

b.) Support/Account Management staff. Part of "Marketing & Sales expense" (not COS). Also used for product/service specific CLTV calculations on the "Sales Summ" sheet.

Within the first block, enter the average number of hours of any direct labor (per sales unit + month) and the costs per hour. The salary is calculated automatically based on the working hours per FTE and year. When entering the annual working hours of an FTE keep in mind Sundays (weekends), public holidays, annual vacation, sick leave, etc. A reasonable input value might be e.g. 8 hr/day x 20 days/mth x 12 mths = 1,920 hours per year.

The Support/Account Management block inputs and calculation are similar to the direct labor calculations in the CAC section.

Usually, the number of customers a single support agent can manage can be pretty high as the input value covers a period of one month. Not all customers will need support at all and the rest can be virtually distributed over the month. Assuming a working capacity of about 160 hours per account manager per month, i.e. 320 customers per 30 minutes support time or even 640 customers with an average 15 minutes support slot per month.

Note: This input value (= number of customers 1 support agent can handle) is highly sensitive with regard to the number of Marketing & Sales Staff and especially when calculating product/service specific metrics like gross customer profit and CLTV (see sheet "Sales Summ").

The input section concludes with assumptions regarding input/output taxes on the cost of sales. When using input/output taxes define the percentage of the cost of sales which is liable to VAT and select the appropriate rate. In case you are planning without VAT, GST or Sales Tax, set rate to “zero rate” for each product/service (for more details on input/output tax settings see paragraph taxes => “VAT / Sales Tax”).

For the divisions R&D and G&A:

Fill in employee names or clearly-identifiable descriptions for different positions and enter a base salary (for the first planning year) as well as an appropriate annual raise (optional) for each employee/position (see screenshot). If necessary you can also overwrite formulas in the years following the base year (see tip below).

Note: The base salary always refers to a period of 12 months (= annual salary), without income taxes and social insurance, even if an employee is only expected to work a few months of a year. Do your detailed staff planning (monthly deployment schedule) on the “Human Resources” sheet.

Fill in a percentage (of base salary) for income taxes and social insurance. It is possible to turn on/off the calculation of Social security contributions and taxes for each employee/position on the “Human Resources” sheet.

For each division assumptions for additional staff costs can be entered. Apart from the first row item (“Recruiting Costs (one-time upon hiring)”) descriptions can be changed according to your needs. Note: Pay careful attention to the units (column E) of each row item (e.g. USD/FTE/month vs. % of base salary etc.). If you change a unit, the formulas on the “Human Resources” sheet need to be edited accordingly (recommended only for experienced excel users).

Initial Equity

For the first equity tranche (= initial equity) you have to decide whether the full amount should go into the model in the first month (select “Yes”) or if you prefer a draw down on an as-needed basis. In the latter case the amount entered in “share capital” can be understood as maximum and may not be fully needed (if the total funding requirement is less).

Additional Equity

For the second (optional) equity tranche (= additional equity) you can enter an amount and the date the money will be made available (see above screenshot).

Four different loans (debt tranches) and an additional automatic overdraft facility are included in the funding waterfall model.

Debt 1 to 4

The first loan (debt 1) is a fully automatic annuity. The regular payment interval (interest & principal repayment) can be freely selected (monthly, bi-monthly, quarterly, semi-annual, and annual). This debt can be switched on/off at any time by “one click” and allows for automatic coverage of capital requirements up to the maximum debt limit.

While using this mechanism you see the direct impact of any change in assumptions on the capital requirement (e.g. when running different scenarios).

If debt 1 has been switched on you can enter the following assumptions:

Click image to enlarge

Debt 2 to 4 are identical structured apart from their hierarchy in the funding cascade. On the “Inputs” sheet, you can assign a facility description for each loan and decide whether interest should be calculated automatically. In this case, enter an interest rate. If you prefer to enter interest payments manually, enter them directly on the “Financing” sheet.  You must always enter loan disbursement and repayments for debts 2 to 4 on the “Financing” sheet.

Overdraft Facility / Current Account

For the current account, you can set up a maximum overdraft (set to zero or leave blank if not used) and an interest rate. The overdraft facility comes at the end of the funding cascade. If liquidity is available, the model automatically uses it for highest possible reduction of this “expensive” credit facility.

a.) Taxes on Profit

As tax regimes are country specific and extremely heterogeneous, Excel-Financial-Model has a straightforward implementation of tax calculation, which can be adapted individually where necessary. On the “Inputs” sheet, a tax rate (in percent) and a tax loss carried forward can be entered. The tax rate is applied to net profits before tax of each fiscal year.

Advance tax payments can be entered for each planning year to straightening out cash flow effects of a single annual tax payment. Prepayment dates are quarterly, and you can set their exact timing (in months). In addition, you can define the date (month) for the tax payment in the following year.

It is recommended to enter tax prepayments at the end or the planning process, as the actual tax burden depends on numerous model assumptions and inputs. The row below the input line for advance tax payments helps you choose appropriate amounts for the prepayments, as it shows tax payable according to the model’s calculations.

b.) VAT/GST/Sales Tax

If no sales or input/output tax is applied, simply set rate 1 to rate 3 to 0%.

When using input/output taxes you can change the descriptive term according to the appropriate country specifics. It is possible to choose between “Sales Tax”, “GST” for Goods & Services Tax or “VAT” for Value Added Tax.

Define up to three different local input/output tax rates (besides a predefined zero rate), e.g. a standard rate and one or two reduced rates.

You can set different options for claiming and paying VAT/GST etc. Select an accounting basis for claiming and returning your VAT/GST. The options are the invoice or payments basis.

Invoice basis (aka imputed taxation)

Using the invoice basis, you account for VAT/GST at the end of the taxable period when you issued an invoice to your customers or received an invoice from your supplier. You will pay the amount of VAT/GST shown on the invoices you've given to your customers (whether you've received a payment or not), and claim a credit for the amount of VAT/GST shown on tax invoices you've received from your suppliers (regardless of whether you've paid your supplier or not).

Payments basis (aka actual taxation)

Under the payments basis you account for VAT/GST at the end of the taxable period when you make or receive the payment. This helps you manage your cash flow because you only pay VAT/GST after you've received the payment from your customers, and you only claim VAT/GST for the purchases and expenses you've paid for.

Filing frequency / intervals

You can set the payment day interval when VAT/GST is paid to or refunded by state/tax authority (filing frequency). Select one of the five options: 1. Monthly, 2. Bi-Monthly, 3.Quarterly, 4. Semiannual or 5. Annual.

Finally, choose the rates applied for a.) cost of materials/packaging and goods and b.) all other non-payroll expenses (other operating expenses, overheads etc.). If there are items within these groups with mixed rates or if not for all positions input/output tax is applicable enter a percentage of the amounts which are subject to VAT, GST, Sales Tax etc.

Output tax rates for products/services can be entered directly into the input section of each offering 1-4 (shown in this screenshot).

Enter input tax rates for capital expenditures on the “Capex” sheet. Percentage and tax rate can be set individually for each item.

Assumptions for Customer Lifetime Value Calculations:

You can enter a percentage which defines the extent to which Marketing Expenses are allocated to customer acquisition. The balance of the expense is allocated to Customer Retention (for calculating Customer Lifetime Value).

Furthermore you can enter an annual discount rate, for calculating the Net Present CLTV (because future profits are generally not worth as much in today's money as present profits are). However, when your customer lifetime is short (for example, less than a year), it does not matter if you get the money today or later. You can safely neglect the discount rate in the calculations.

Sources & Uses:

For sources and uses calculation and overview (on summary 01 sheet), the number of months to be calculated/shown, can be entered and changed at any time. In case the number of months entered is longer than the model duration (based on your timing assumptions), you will get a warning/error message.

Interest Receipts:

Here you can enter an interest rate for bank deposits (cash).

Target Stocks / Change in Inventory:

It is possible to switch on/off inventory planning globally.

The integrated simple and straightforward inventory calculation mechanism is based on the input value "days of inventory on hand". Enter the number of days of inventory you expect to carry if your business requires inventory, otherwise leave this field blank (or completely switch off inventory planning). With net sales being the reference value for target inventory (see calculations on the "IFS" sheet, from row 199), 15 days results in an inventory value of half a month sales (15/30), etc.

Maximum decrease in inventory (in % of net sales): This value should match your average ratio of cost of materials to sales (in %). In the case of sales declines, a low rate can prevent too excessive reductions in inventory and therefore cash flow distortions.

P&L and Balance Sheet Items:

Finally Excel-Financial-Model allows for planning further P&L and Balance Sheet items (extraordinary expenses, accruals, changes in advances received, advance payments, etc.) Detailed input options can be found at the bottom of the “Inputs” sheet. The table within the section “Miscellaneous” simply gives an overview of the figures on an annual basis. It is formatted and integrated for (the optional) printing of the “Inputs” sheet.

For existing companies with a financial history, enter all the necessary initial values. Check the control cell “balance check” just below the opening balance input cells, to make sure the opening balance is balanced. If the control cell does not show “OK”, the delta is indicated to help you review the values entered.

Click image to enlarge

For new projects or start-up companies with no initial values leave input fields blank or set to zero.