Cash Forecast

 

 

The cash forecast report is an essential report for managing the business, and what I consider the most important financial/accounting report. While Profit/Loss statements help you understand what happened, the Cash Forecast tells you what the future will look like.

 

When cash is tight or profitability percent is low, this becomes an essential tool for making long-term and short-term decisions. For example, if you can see that cash will be low in six months, you have time to adjust.

 

The cash forecast should be updated monthly so that it is an evolving view of the future based on the most accurate current data. The simplest way to do this is to use the last day of each month as the date of record.

 

 

Structure

The Cash forecast is divided into three sections: Revenue, Expenses and Cash available.

 

Revenue is the largest portion of the report and takes the most time to update each month. The Cash Available section is a summary and calculation of revenue and expenses.

 

It is not important to match expenses with the corresponding revenue for purposes of this report. However, a breakdown by department and/or brand can be helpful when you need to identify delayed or advance expenses.

 

The report is structured by month, with one column for the next 15 months.

 

The far left column of the report includes the products and issues for revenue and expenses. To the right of that, there is a “Received revenue” column. This is for convenience to help track total conference and digital sales. To the right of that, there is a column for Past due amounts (sales invoiced but not paid). The total of the past due amounts is then divided up in the Cash Available section with estimates for when the cash will arrive. It is currently set at 66% in the next month, 20% in month two, and 14% in month three. However, you may adjust these percentages based on when the invoices were due.

 

Next from left to right are the 15 columns by month. Each month you will delete the last month and add a month on the far right.

 

At the right of the months, there is a Total column of the 12- and 15-month period. To the right of that, there is a column for the budget amount. This column is for convenience when comparing the “total expected revenue” with the budgeted revenue to ensure that “total expected revenue” is fully accounted for.

 

To the right of Budget, there is a column to track revised projections. This is for convenience so that you can retain the original budget amount and track revised projections for future issues, as supplied by the Sales Manager, Marketing or President.

 

To the right of that, there is a column for the billing date for magazine issues and conference event dates. This is for convenience when you are estimating cash arrival. 

 

The far right column is for notes. You should explain any changes you have made and why, so that in future months you are not confused by your estimates. You may also use Excel’s notes function to add notes to a single field.

 

 

 

Revenue Section

 

 

We include a line item (or a row) for each product that generates revenue, and break into multiple rows for magazines — with a row for each issue. Revenue is totaled at the bottom of this section to show Expected incoming cash per month.

 

Magazine: One row per issue; Revenue per month is based on calculation of when revenue is received

 

Newsletters: One row per product; Revenue amount per month is taken directly from the budget

 

Conferences: One row per conference; Revenue per month is based on calculation of when revenue is received

 

Digital/Web: One row per product; Revenue amount per month is taken directly from the budget

 

Misc.: One row per product, if any expected revenue; Revenue amount per month is taken directly from the budget

 

 

Monthly update:

 

Step 1: Update revenue projections

Each month, you will collect the total accounts receivable for magazines, digital and conferences as of the date of the report, broken down by product and issue.

 

• For magazine issues, you then modify each row for published issues so that future projected revenue matches the account receivable. This would be modified further if some ads had not yet been invoiced (But this is a rarely happens).

 

For example, if you collected $5,000 of Golf Inc. Jan/Feb issue in January, but had projected to collect $10,000, you will move the missing $5,000 to a later month, such as February.

 

For future magazine issues (issues not yet invoiced), you will get revenue projections from the Sales Manager. If issues will soon be invoiced, this could be a final dollar amount. For issues further out, there may be no modifications. The Sales Manager will only provide a modification for future issues when he knows his team will exceed a goal or fall significantly short.

 

For example, if you are revising the report as of Jan. 31, and the March/April issue is performing better than expected, the Sales Manager may ask you to project an increase over the budget.

 

• Conferences are invoiced when we receive an order. As such, you may have some sponsors that have paid, some that have been invoiced but not paid, and some that are projections for future sales.

 

Use the “Received revenue” column to track sponsorship revenue already received for future conferences. This will make it easy to see that your total conference revenue is matching with the budget.  This should match total amount received for each conference, a report provided by accounting.

 

Modify the total expected revenue by month so that, when combined with “Received conference revenue,” it matches the budget or modified figure provided by Sales Manager.  The expected revenue by month will include both invoiced and projected sales. You will use the invoiced amount to estimate when cash will arrive.

 

• Newsletters and Miscellaneous revenue is tracked on a monthly basis based on the budget. However, you may increase or decrease the monthly amount based on projections provided by the Sales Manager. Please note: if you adjust revenue, you need to adjust commission expenses to match.

 

• Digital/Web: Digital/web revenue is projected in the budget based on goal deadlines, which are either quarterly or per semester. However, revenue is received monthly as billed. You should use the payment received formula for digital to estimate when cash will arrive, and track received cash in the “Received cash” column. This will make it easy to see that your total digital revenue is matching the budget.  This should match total amount received, a report provided by accounting.

 

Step 2: Add new month and revise totals

After you update the current month actuals and projections, you will add a new month on the far right, and add revenue projections for that month. Since this is usually revenue not yet projected, you will use the prior year’s projections so that your total in any given 12-month period still matches the budget or revised projections.

 

After you add a new month, you need to adjust your totals in the 12- and 15-month Total columns so that they include the next 12 or 15 months and exclude the month that just closed.

 

 

Reports needed for this section

 

From accounting:

Accounts receivable by product and issue

Total revenue received per future conference

Total revenue received for digital for time periods not completed

Report showing pre-payments for products and/or issues not otherwise invoiced

 

From sales manager:

Any projection changes for future magazine issues and conferences

Any projection changes for future newsletter sales per month

 

 

Expenses & Cash Available sections

 

The Expense section is easier to produce and does not change as much. We pull the data directly from the budget. The budget should be set so that most items become due in the month they are budgeted for.

 

Conferences are the primary exception. All conference expenses are placed the in the budget year of the event. But some expenses happen prior to the event, primarily deposits. You will add the deposit amount in the month it is due, in the row titled “adjustment.” You will also subtract the same amount from the month of the event, also in the “adjustment” row.

 

When a new budget is complete, the cash forecast can be set for the next 12 months based on this. The additional three months will repeat the same months in the prior year.

 

Monthly update for expenses:

 

Step 1: You will add in any past due amounts to the past due column.

 

Step 2: You will mark any other adjustments, such as conference deposits or expenses that were delayed from the prior month. At times, management will cancel certain budget amounts. You will add this into “adjustments” as well, with detailed notes.

 

Step 3: You can hide the month that just closed or delete it. You will then redo the summary calculations so that this month is not included. You will also revise the payment schedule for past due amounts.

 

Step 4: You will add in the actual cash balance in the bank for the Cash End row, which repeats as the Cash Start row in the current month column.

 

 

Reports needed for this section:

 

From accounting:

•Accounts payable and past due, including credit card balances that were projected to be paid prior to the month.

•Total cash in all bank accounts

• List of delayed expenses that exceed $2,000. A delayed expense is one that we had projected would be paid prior to the report date, but was held up for some reason.

 

From Management

•Any cancelled future expenses

•Any changes to payroll

 

 

Final Step:

When you have updated the revenue and expense sections, and retotaled the Total columns, and updated the Cash Start and Cash end, provide the spreadsheet to someone else to confirm that your revenue and expense 12-month totals either match the budget or you have explained why they do not match.

 

Sometimes there is a change in publication dates that will either increase or decrease revenue for an issue or conference in each 12-month period.

 

After you have confirmed that there are no errors, and that all figures either match the budget (or revised projections) or are explained in notes, then you compare to the prior months report. If the “Cash End” in your new report is significantly off from the prior month’s report Cash end for the same month, then you need to do a reconciliation.

 

Reconciliation

To reconcile the current and past Cash Forecast, make a list of the changes you made to revenue and expenses.

 

For example, you would document that an issue’s revenue was revised from $40,000 to $60,000.

 

You would document that an expected large expense was not incurred.

 

If these changes explain the discrepancy from month to month, then you are done and ready to present to management. You will provide the Cash Forecast report and the corresponding notes of what changed to Management.

 

If the changes do not explain the discrepancy, then you need to compare actual cash received in the prior month compared to what was projected. You may find that newsletter or other revenue was off. You also need to compare actual expenses to what was projected. You may find expense anomalies as well that explain what happened. All of these notes should be added to your report and provided to management so that they can easily identify what changes occurred.

 

 

Final Note:

 

You should never present the report to management if you are not confident that it is accurate. Management, especially the Board, does not have time to review this report to identify errors or when there are potential errors. They rely on the accuracy of the report to make important business decisions.

 

 

 

Preferred schedule for

Profit-Loss Statements

 

 

Profit Loss Reports

Crittenden Newsletters                       July 15

Golf Inc.                                               Aug. 15

National Jurist                                     Sept. 15

Crittenden Newsletters                       Oct. 15

Golf Inc. Summit                                 Oct. 15

Crittenden Finance Conference          Nov. 15

Golf Inc.                                               Dec. 15

National Jurist                                     Dec. 15

Crittenden Newsletters                       Jan. 15

Golf Inc.                                               Feb. 15

Crittenden Multifamily Conference    March 15

Crittenden Newsletters                       April 15

National Jurist                                     April 15

Crittenden Medical Conference          May 15

Golf Inc.                                               June 15