Calculation Issues

How to Build an Excel Cash Collection Projection Formula

Adam DavisAdam Davis Nov 10, 2026 869 views

Question details

Build a dynamic weekly cash collection projection using sales data and a defined schedule of collection percentages.

How to Build a Cash Collection Projection Formula in Excel
Product
Excel
Device & OS
not provided
Scenario
Creating a financial model to automate weekly cash collection distribution without writing manual, staggered row-by-row formulas.
Observed behavior
The user needs a way to calculate total projected collections for each week based on historical sales and a trailing percentage distribution, rather than manually staggering cells.
Before you start

Before building the formula, clearly define your collection percentage schedule. Ensure you know exactly what each percentage represents, how many weeks after a sale the cash is collected, and verify that the percentages realistically reflect your collection process (usually summing up to 100% minus any expected bad debt).

Solution 1Recommended

Create a Dynamic Cash Collection Matrix

Set up a matrix calculation to automatically distribute each week's sales across subsequent weeks according to the collection schedule.

A matrix method visually separates the sales of a specific week into the staggered collection periods. This gives you a clear audit trail of where cash is coming from in any given week.

1
Define the collection schedule

In an isolated range (e.g., cells C1:G1), input your collection percentages for Week 1, Week 2, Week 3, etc. (for instance: 40%, 30%, 20%, 10%).

2
List weekly sales

List your chronological weekly sales figures down a column, starting from cell B2.

3
Apply an absolute reference formula

In cell C2 (the first week of collection for the first sale), type the formula =$B2*C$1. This multiplies the sale by the first week's collection percentage.

4
Stagger and drag the formula

Drag this formula across columns and down rows to complete the matrix. To calculate total collections for a specific calendar week, sum the values diagonally or align the matrix output so each column represents a calendar week.

Create a Dynamic Cash Collection Matrix
Dynamic Array Alternative: If you are using a modern version of Excel, you can use the SEQUENCE and MAKEARRAY functions to spill this entire matrix dynamically based on the length of your sales column.
Advanced Financial Modeling

Automate Cash Collection Projections with WPS Spreadsheet

WPS Spreadsheet offers powerful calculation capabilities, including full support for SUMPRODUCT and dynamic matrix arrays. You can easily build, automate, and share robust cash collection models without manually updating rows every week.

  1. 1. Open your sales dataset: Launch WPS Spreadsheet and open your existing financial dataset.
  2. 2. Define your parameters: Set up a dedicated table for your collection percentages and amortization schedule.
  3. 3. Apply financial formulas: Use SUMPRODUCT or array formulas to instantly project incoming cash flows for future weeks.
  4. 4. Save and share securely: Save your model in .xlsx format to seamlessly share it with your accounting team or stakeholders.
Fully compatible with Microsoft Excel (.xlsx) formulas and financial models.Supports advanced array functions for automated matrix calculations.Lightweight architecture handles large cash flow projection datasets smoothly.Free to download and fully equipped for professional business planning.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my cash collection projection formula not add up to total sales?

This usually happens if your collection percentages do not sum to exactly 100%. If you account for bad debt, the total will naturally be lower. Additionally, ensure you have dragged your formula far enough into future weeks to capture the final trailing collection percentages.

How do I handle cash collections that span across months instead of weeks?

You can adapt the model by changing your time buckets. Aggregate your sales by month instead of by week, and define your collection schedule based on a 30-day, 60-day, and 90-day lag. The underlying matrix or SUMPRODUCT formulas remain exactly the same.

Can I project amortization using the same matrix logic?

Yes. Amortization and depreciation schedules use the same underlying concept. Instead of a cash collection percentage, you would apply a straight-line or declining balance percentage to an initial capital expenditure or prepaid expense to distribute the cost over future periods.