How to Build an Excel Cash Collection Projection Formula
Question details
Build a dynamic weekly cash collection projection using sales data and a defined schedule of collection percentages.

- 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 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).
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.
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%).
List your chronological weekly sales figures down a column, starting from cell B2.
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.
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.

Use SUMPRODUCT for Total Weekly Collections
Directly calculate the incoming cash for a specific week by multiplying past sales with a reverse-ordered collection schedule.
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. Open your sales dataset: Launch WPS Spreadsheet and open your existing financial dataset.
- 2. Define your parameters: Set up a dedicated table for your collection percentages and amortization schedule.
- 3. Apply financial formulas: Use SUMPRODUCT or array formulas to instantly project incoming cash flows for future weeks.
- 4. Save and share securely: Save your model in .xlsx format to seamlessly share it with your accounting team or stakeholders.

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.




