Templates

Credit Manager Workbook

DSO by three methods, best possible DSO, average days delinquent, the collection effectiveness index, the cost of a day of DSO, the margin test, credit limits by capacity and need, and the allowance by aging bucket: six tabs, one fictional distributor, no macros.

Enter twelve months of credit sales and the aging by due date. The workbook returns DSO three ways (58.4, 58.6 and 59.5 days on the case), splits it into 37.5 days of best possible DSO and 21.1 days of average days delinquent, computes a December CEI of 59.2%, prices a day of DSO at $36,822 a year, sets a limit at the lower of capacity and need (Kessling: $160,000) and builds a $780,000 allowance from loss rates by bucket. A Checks tab ties every figure to the book.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Credit_Manager_Workbook.xlsx (17 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind the workbook: The Commercial Credit Manager, PDF and paperback, with the full case and the checklists.

What is in the workbook

Inputs
monthly credit sales, the aging by bucket at the period end, receivables at the start of the last month, the carrying rate, the gross margin, the loss rate by bucket and the capacity percentages by risk class.
DSO
DSO by the annual, three-month average and countback methods; best possible DSO; average days delinquent; the collection effectiveness index of the last month.
Cost of credit
the value and carrying cost of a day of DSO, the cash released by a target for average days delinquent, the annualized cost of an early payment discount, and the margin test for one customer.
Limits
for any customer, the capacity limit from tangible net worth and risk class, the need limit from monthly purchases, the approved limit, and the gap to secure.
Reserve
the allowance by bucket from the loss rates, its total and its share of receivables.
Checks
a list of controls that compare the workbook's results with the figures printed in this book, ending in a single cell that reads ALL CHECKS OK when every one passes.

Every input sits on one tab, shaded; every other cell is a formula, with no macros, no links to other files and no circular references. Every ratio is guarded against an empty divisor. Replace the inputs with your own ledger; the Checks tab will then show differences from the book by design.

From The Commercial Credit Manager. To read: best possible DSO and average days delinquent · how to set a customer credit limit · the allowance for doubtful accounts.