Wholesale Cash Flow Model: How to use this free tool

How to use the wholesale cash flow model

You can use this model in Excel, Google Sheets, or any other spreadsheet program. The model is completely open and unlocked, so you can edit or change calculations and add in new channels to suit your business.

Keep in mind that all inputs – numbers you can change yourself – are in blue text with a grey shaded background, and all numbers that result from automatic calculations appear in black.

You can use this cash flow tool to assist with a number of goals, such as:

• Forecasting future sales and cash flow
• Comparing the performance of various sales channels
• Informing marketing budget decisions based on revenues by channel
• Examining returns on sales

Take a look at our step-by-step guide on how to use this free tool to manage your cash flow.

Enter your model settings

Start by entering your basic information and data labels in the ‘Get Started’ tab. All information you enter in this tab will be reflected in the ‘Orders’ tab and vice versa. The model structure is flexible so that you can enter granular information about individual orders, or the total number of orders per customer or per month.

On the ‘Get Started’ tab, you can enter:

Your preferred currency and forecasting start date

Note: The first forecast date is populated by the first order you input on the ‘Orders’ tab, as we will see later on.

Your sales pipeline information

You can input and edit any the following:

• Name of Stage – the stage in the sales pipeline that any order falls. You can keep the default five stages or edit to suit your business.
• Likelihood – the percentage chance you believe an order is likely to close at each stage of the sales pipeline.
• First Time or Repeat Customer – whether a customer is new or repeat. You can edit these labels if preferred.
• Category of Customer or Order – you can choose to categorize customers or orders to provide clearer segmentation in reporting. In this example, we have chosen two labels – ‘Referral’ and ‘Bulk’.
• Domestic or International Customer – a customer’s relation to your country, which is also valuable for reporting purposes.

Revenues and cash

Here, you can set details about the transaction fees you pay to your payment processor for your own country and other countries. In this example, we have used TradeGecko's default fees based on merchant location.

Where to account for payment fees

You can also choose where to account for payment fees – as COGS (cost of goods sold) or SG&A (selling, general & administrative expense).

Enter your forecasted or actual orders

Next, you can begin to enter your order details on the ‘Orders’ tab. The model will then sum the revenues into various categories (as defined on the ‘Get Started’ tab) on the ‘Summary’ tab.

The ‘Orders’ tab allows you to define:

An order date and a delivery date for each order

Here, you can define different payment schedules around those dates if applicable. The delivery date you enter for each order defines when revenues for that order are recognized.

You can also edit other details for each order, such as:

• Customer name
• Customer or order category
• Order number
• Sales pipeline stage
• Likelihood of closing the sale

Forecasted payment schedule

This allows you to set:

• The percentage of revenues that are paid on a purchase order
• The percentage of revenues that are paid when the order is delivered
• Number of days payable on the remaining cost of the product

The model uses this information to forecast the paid amounts at purchase date, delivery date, and when the remainder is payable.

It also automatically calculates the payment fees associated with each order based on the transaction fees entered on the ‘Get Started’ tab.

Actual payments

If you prefer a more specific way of forecasting cash flow, you can also use the Actual Payments section to manually input payment amounts and dates for each order. Again, payment fees will be calculated based on the transaction fee percentages entered on the ‘Get Started’ tab.

Revenues and costs by month

Your gross margin is calculated by your revenues (recorded on the ‘Orders’ tab) minus your cost of goods sold.

Operating expenses per month

Payment fees are automatically calculated from forecasted sales and payment rates. You can also manually add additional operating expenses.

Revenue details per month

Each month’s revenues are broken down by:

• First time or repeat customer
• Category of customer or order
• Sales pipeline stage
• Domestic or international customer

Cash details per month

Each month’s cash details are broken down by:

• Forecasted payments and payment fees
• Actual payments and payment fees
• Accounts receivable

Total cash payments and payment fees are calculated based on combined forecasted and actual data from the ‘Orders’ tab.

You can also manually add any accounts receivable at the beginning of the first forecast month.

Review the ‘Key Reports’ tab

The ‘Key Reports’ tab takes inputs and calculations from the ‘Get Started,’ ‘Orders’ and ‘Summary’ tabs and provides useful insights presented graphically, including:

Cash burn and runway

Revenue by customer or order category per quarter

Revenue by first time or repeat customer per quarter

Don’t forget that you can easily tailor these reports to suit your business by refining or relabelling the inputs on the ‘Get Started’ tab.