Stay in Business With This Cash Budget Example + Spreadsheet


Along with the capital budget and the operating budget, the financial budget rounds out the trinity of annual business budgeting.

The financial budget is a forecast of cash flows for the coming year which culminates in a pro forma balance sheet and cash flow statement.

If you’re like most businesses, you probably don’t make every sale in cash, many are made on terms that allow your customers to pay you at a later date. There’s a (hopefully short) amount of time between when the sale is made and when the money ends up in your bank account.

Likewise, there’s probably a certain amount of time between when you incur an expense and when money leaves your bank account.

We all know that, in order to stay in business, you have to make more in sales than you incur in costs. Beyond that, the timing of cash flows in and out can make or break a business. Not having the cash to pay vendors or employees can cause a vicious cycle that can be tough to dig out of. Even if sales and profit are otherwise adequate.

That’s why a financial budget is important. Even though your forecasts almost certainly won’t be 100% accurate, it’s beneficial to anticipate your cash flows and proactively address any potential problems.

Looking for more spreadsheet templates?

Financial budget workbook download

Complete the form below and click Submit.
Upon email confirmation, the workbook will open in a new tab.

Cash Collections Schedule

Click to enlarge

% collection of current, prior, 2nd prior month sales, and % uncollectible sales

This is where you enter the timing of your cash inflows.

Of course, these percentages are going to be different from month to month, so an average is fine. You might have to go back and look at your accounts receivable if you don’t know these percentages off hand.

All of these percentages should total 100%.

Projected sales

Here is where you’ll enter all of your forecasted Gross sales from your Sales budget which is within your operating budget.

You’ll also need to enter the three months prior to the beginning of your budgeting period. This information is needed since it might take a few months for your company to collect on those sales.

The percentages you entered previously regarding the timing of sales will dictate when you expect to collect on your sales.

For example, if you expect to collect 30% of your current month sales then 30% of the Projected sales amount will show up as cash in the From current month sales field. Likewise, if you expect the collect 40% of the previous month sales, then 40% of a particular month’s Projected sales will show up as cash in the following month. and so on.

Of course, whatever you enter as the % uncollectible sales amount will never show up as cash. This amount should match the Percentage of sales assumed to be uncollectible from the Revenue budget within the operating budget.

Cash disbursements schedule

Click to enlarge

Direct materials

% payment of current and prior month DM purchases

Similar to what you did on the Cash Collections Schedule you’ll need to enter the expected timing of the cash outflows for direct materials. What percentage of your direct materials accounts payable do you expect to pay in the current month? And how much do you expect to pay in the following month? These totals should equal 100% because you don’t plan to not pay your suppliers right?

Direct material purchases

This also pulls directly from your operating budget. Specifically from the Direct Materials Budget. Copy the monthly amounts in the Grand Total field of the Direct Materials Budget.

Cash payments for current and prior month purchases

Based on what you entered above as the % payment for current and prior month DM purchases, the following fields will populate appropriately.

For instance, if you entered 40% as the % payment of current month DM purchases then you will see 40% of a given month’s direct material purchases shown as a cash outflow. Likewise, if you entered 60% as the % payment of prior month DM purchases then you will see the remainder of the cash owed to suppliers flow out the following month.

Total cash payments for DM

This is simply the sum of the Cash payments for current month purchases and Cash payments for prior month purchases. Notice that the Total cash payments for DM are different than the amount of Direct material purchases. Of course, this is because we want to know the difference between the timing of cash flows versus the accrual of expenses.

Direct labor

% payment of current and prior month DL accrual

By now you probably understand what these percentages are trying to do for you. If you want to keep your employees, direct labor probably has a higher percentage of accrued expenses paid in the current month than direct material purchases from suppliers or payments for overhead. Nevertheless, enter whatever percentages you think are appropriate

Direct labor accrued

Just as with sales and direct material purchases, input the Grand total from the Direct Labor Budget within the operating budget.

Cash payments for current and prior month DL accrued

This is where your direct labor accrued gets broken up into cash payments between the current month and the following month based on the percentages entered previously.

Total cash payments for DL

Of course, this is the expected cash outflow for direct labor for every month during the budgeting period. Depending on the amount of direct labor accrued for a given month and the % payment of current and prior month DL accrual, the amount of cash outflow could be greater or lesser than the Direct labor accrued.

Overhead

% payment of current and prior month OH accrual

You know the drill by now.

Overhead accrued

This is the Grand total from your Overhead Budget within the operating budget.

For current and prior month overhead accrued

Here, you’ll see a breakdown of expected cash outflows for overhead based on the % payment of current and prior month OH accrual.

Operating expenses

Click to enlarge

The Total cash payments for op expenses will be manually entered and will be equal to the Total from the SG&A Budget(s) within the operating budget. All operating expenses are assumed to be paid in the month incurred.

Equipment purchases

The Total cash payments for equip purchases is where you will enter expenditures for equipment purchased throughout the year. This will not typically be a monthly expense but rather large payments made sporadically throughout the year.

If you completed a capital budget for this year’s planned projects that’s a good place to start for an idea of when capital outflows might occur.

Income taxes

Since this is not an income statement we don’t have earnings before taxes to reference in order to estimate the amount of taxes owed. Rather, we’ll have to use sales as our basis for taxation. Obviously, taxes aren’t actually based on sales but rather the income from those sales. But this is the best we have to work with.

Reference your Pro Forma Income Statement in the operating budget to get an idea of what percentage of sales you expect to pay in taxes. Enter that percentage in the Estimated tax % of net sales field.

Since taxes are paid quarterly and we’re trying our best to estimate when big cash expenditures will leave the company, the expected amount for quarterly tax payments will automatically be populated in March June September and December. There are other blanks for you to fill in for other circumstances or when additional taxes might be owed.

Grand total

Total cash payments for DMTotal cash payments for DL + Total cash payments for OH + Total cash payments for op expenses + Total cash payments for a equip purchases + Total cash payments for estimated tax.

Cash Budget

Click to enlarge
Beginning cash balance

Here you will enter the expected beginning cash balance for the first month of the coming year. Every subsequent month will be populated with the Ending cash balance from the previous month.

Cash collections

Cash collections from sales pull from the same field (Total) on the Cash Collections Schedule.

Cash disbursements

Cash payments for DM, DL, OH, op expenses, equip purchases, and estimated tax

All of these amounts are pulled from their respective Total cash payments fields on the Cash Disbursement Schedule.

Total cash disbursements is the sum of all of these cash payments for a given month.

Surplus (deficit) of collections over disbursements

Cash collections from sales + Total cash disbursements.

Trial ending cash balance

Beginning cash balance + Surplus (deficit) of collections over disbursements.

Desired ending cash balance

Here you will enter how much you hope to have in cash at the end of every month. This can be as much or as little as you want. However, depending on your total cash collections and disbursements for the month you might have an excess of cash or a shortfall in cash.

Excess (shortfall) of cash to desired balance

Trial ending cash balance – Desired ending cash balance

Here you’ll see the difference between what you hope to have in cash at the end of the month and what you actually forecast.

Short-term financing

Click to enlarge

Forecasted shortfalls in cash can be overcome in a couple of different ways. First of all, you can lower your Desired ending cash balance. However, that might only exasperate your problems by making every subsequent month have a shortfall of cash.

Another solution is to shore up cash shortfalls with Short-term financing.

Loan terms

This is where you get the opportunity to enter information about any existing short-term loans. All of the fields are pretty self-explanatory.

Type in the Interest rate for borrowed funds, Term (in months) for borrowed funds, Original amount borrowed, and the Original date obtained. Keep in mind that this is for short-term loans, not long-term. This means loans that were meant to be repaid within one year. Any loans with a term longer than one year will be entered below in the Long-term financing section.

*These same terms will be used for any additional short-term financing that is needed throughout the budgeting period.

Additional borrowings

Nothing need be entered here this is all automatically calculated based on your Excess (shortfall) of cash to desired balance.

Repayments

This will include the repayment of any existing loans and any Additional borrowings needed to shore up cash balances.

Net short-term financing

Additional borrowings + Repayments

Long-term financing

Just like you did in the Short-term financing section, enter the Interest rate for borrowed funds, Term (in years) for borrowed funds, Original amount borrowed, and the Original date obtained. Remember, this is for any long-term loans that will require payments being made during the forecasted year.

*These same terms will be used for any additional long-term financing that is needed throughout the budgeting period.

Additional borrowings

Here you can enter any additional amount of long-term borrowings you expect to need. The payments due from these borrowings will be included below in Repayments.

Repayments

This is the amortization of any existing long-term financing and Additional borrowings entered previously.

Net long-term financing

Additional borrowings + Repayments

Investments

Income rate for invested funds

Here, you’ll enter your expected annual return on money kept in investments. Keep in mind that these would typically be short-term, highly liquid, and low-risk assets. So, the interest rate will likely be relatively low. You won’t be earning 20% per year on this money.

Deposits

For the purposes of this analysis it’s assumed that any excess cash you have at the end of the month above and beyond your Desired ending cash balance will be put into short-term, liquid investments so that that money you can earn at least a nominal return while still being available, should you need it.

If Excess (shortfall) of cash to desired balanceNet short-term financingNet long-term financing is a positive number then that amount will be shown here as a negative. It’s showing as a negative because it’s money leaving the cash balance and going into an investment account. Obviously, that money is still an asset of the organization.

Withdrawals

If the Excess (shortfall) of cash to desired balanceNet short-term financingNet long-term financing is a negative number then that shortfall needs to be made up somehow.

This worksheet will first look for an available balance in the investment account. It will take out enough money to cover the shortfall or, if that’s not enough, the entire balance of the investment account will be withdrawn.

If there’s not enough in the investment account to cover the shortfall then short-term financing will be used to cover the remainder.

Net Investments

DepositsWithdrawals

Ending cash balance

Trial ending cash balance + Net short-term financing + Net long-term financing + Net Investments.

You’ll also notice that the Ending cash balance equals the Desired ending cash balance from the Cash disbursement section.

Executive summary

Pro forma balance sheet

The Pro forma balance sheet will show the expected balances for both the beginning of the budget period and the end of the budget period.

Assumed depreciation

Here you’ll enter the total amount of depreciation your company expects to incur over the budget period. This was not addressed anywhere else in the workbook so it must be entered manually.

This amount would likely come from a separate depreciation schedule for all assets.

Cash

The Beginning and Ending cash balance (Cash budget)

Short-term investments

This is the ending amount of Net Investments (Cash budget) including any accumulated investment income. There is assumed to be no Short-term investments at the beginning of the budgeting period.

Accounts receivable

This includes sales amounts from the Cash collections schedule – the amounts that have already been collected. What you sold – what you collected, of course, leaves what is left in Accounts receivable.

Raw materials and Finished goods inventory

Both of these amounts are manually entered for the beginning and end of the budget period.

Both amounts can be found within the operating budget. Raw materials inventory comes from the Direct Materials Budget.

Finished goods inventory comes from Production Budget and Ending FG Inventory Budget.

Total current assets

Cash + Short-term investments + Accounts receivable + Raw materials inventory + Finished goods inventory.

Land

Land won’t be accounted for in any other workbook you complete. Also, Land won’t depreciate. Land will only change in value if you buy it or sell it.

Land is included in your pro forma balance sheet because it’s an asset. So enter your expected value for Land at the beginning of the budget period and the end of the budget period.

Property, plant, and equipment

The amount of PP&E for the beginning of the budget period will have to be entered manually. However, the ending amount will include what you entered for the beginning + Total cash payments for equip purchases (Cash Disbursement Schedule) + Assumed depreciation.

Total long-term assets

Land + Property, plant, and equipment.

Total assets

Total current assets + Total long-term assets.

Accounts payable

At the beginning of the budget period, the amount is equal to Cash payments for prior month purchases (Cash Disbursements Schedule) for the first month..

For the end of the budget period, the amount is equal to Direct material purchases (Cash Disbursements Schedule) for the last month + Cash payments for current month purchases (Cash Disbursements Schedule) for the last month.

Accrued expenses

For the beginning of the budget period, the amount is equal to Cash payments for prior month DL accrued (Cash Disbursements Schedule) for the first month + For prior month overhead accrued (Cash Disbursements Schedule) for the first month.

As for the end of the budget period, the amount is equal to (Direct labor accrued [Cash Disbursements Schedule] for the last month – Cash payments for current month DL accrued [Cash Disbursements Schedule] for the last month) + (Overhead accrued [Cash Disbursements Schedule] for the last month – For current month overhead accrued [Cash Disbursements Schedule] for the last month).

Notes payable

Includes any existing Short-term financing (Cash Budget) + any Additional borrowings (Cash Budget) needed throughout the year to meet the Desired ending cash balance.

Total current liabilities

Accounts payable + Accrued expenses + Notes payable.

Long-term liabilities

Includes any existing Long-term financing (Cash Budget) + any Additional borrowings (Cash Budget) incurred throughout the year.

Total liabilities

Total current liabilities + long-term liabilities.

Total equity

Total assets + Total liabilities.

Total liabilities and equity

Total equity + Total liabilities.

This must equal Total assets.

Pro Forma Cash Flow Statement

Operating cash flow
Cash collected from customers

Total cash collections from sales (Cash collection schedule).

Cash paid to employees and suppliers

Sum of total cash payments for DM/DL/OH/op expenses (Cash Disbursements Schedule).

Interest paid

Total interest paid on Short/Long-term financing.

Income taxes paid

Sum of Cash payments for estimated tax (Cash budget).

Cash from operating activities

Cash collected from customers + Cash paid to employees and suppliersInterest paid + Income taxes paid.

Investing cash flow
Net Investments

Sum of Net investments (Cash Budget).

Purchase/sale of plant assets

Sum of Cash payments for equip purchases (Cash Budget). For simplicity’s sake, no equipment sales were assumed to have taken place in the budget period.

Cash from investing activities

Net investmentsPurchase/sale of plant assets.

Financing cash flow
Short-term debt

The difference between the ending and beginning Notes payable from the Pro Forma Balance Sheet.

Long-term debt

The difference between the ending and beginning Long-term liabilities from the Pro Forma Balance Sheet.

Cash from financing activities

Short-term debt + Long-term debt.

Net change in cash

Cash from operating activities + Cash from investing activities + Cash from financing activities.

Pro Forma Income Statement

Of course, you already created a pro forma income statement when you completed your operating budget. Simply take the information from that one and copy it over here.

The Pro Forma Income Statement is included is so that additional ratios can be calculated.

Ratios

Current ratio

Total current assets ÷ Total current liabilities.

This ratio reflects your company’s ability to pay its current liabilities.

Working capital

Total current assets – Total current liabilities.

The excess of Total current assets over Total current liabilities.

Receivables turnover

Sales made on credit ÷ average Accounts receivable.

This demonstrates your company’s ability to manage its accounts receivable.

The higher this number is – the better. High Receivables turnover means that credit sales were relatively high and average accounts receivable was kept relatively low.

Days sales outstanding

365 ÷ Receivables turnover.

This is the average number of days that the typical sale was in Accounts receivable. The lower this number is, the better.  Because that would mean you’re receiving your cash quicker.

Day’s purchases unpaid

End-of-year Accounts payable ÷ average daily purchases.

This ratio demonstrates your company’s ability to settle its accounts payable in a timely manner.

While you certainly need to pay your bills, keep in mind that if this number is considerably lower than your Days sales outstanding, it means that you are paying money out quicker than you’re taking it in.

Inventory turnover

Cost of goods sold ÷ average inventory.

Inventory turnover reflects how well a company can manage its inventory levels. The lower your average inventory, the higher inventory turnover is going to be.

Days inventory on hand

365 ÷ Inventory turnover.

This is how many days worth of sales you could theoretically fulfill with your average level of inventory.

Operating cycle

Days inventory on hand + Days sales outstanding.

This ratio demonstrates the amount of time it takes for your company to convert resources into cash. Obviously, a shorter operating cycle is better.

Debt-to-assets

Total liabilities ÷ Total assets

This is the percentage of assets financed by borrowed funds.

Debt to equity

Total liabilities ÷ Total equity

This is simply a comparison between the two methods of financing.

Return on equity

Net profit ÷ Total equity

Return on assets

Net profit ÷ Total assets.

Asset turnover

Net sales ÷ average Total assets.

This ratio demonstrates how effectively assets were used to generate sales.

Operating cash flow to income

Cash from operating activities ÷ Net profit.

This shows you the relationship between how much cash was actually received and net profit.

Cash Collections disbursements and balances chart

Click to enlarge

This chart is pretty straightforward and it simply shows, for every month, how much cash was collected how much cash left the organization. Additionally, the resulting effect on the cash balance is also shown.

Join the conversation on Twitter!