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?
Complete the form below and click Submit.
Upon email confirmation, the workbook will open in a new tab.
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%.
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.
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?
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.
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.
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.
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
Just as with sales and direct material purchases, input the Grand total from the Direct Labor Budget within the operating budget.
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.
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.
You know the drill by now.
This is the Grand total from your Overhead Budget within the operating budget.
Here, you’ll see a breakdown of expected cash outflows for overhead based on the % payment of current and prior month OH accrual.
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.
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.
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.
Total cash payments for DM + Total 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.
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 from sales pull from the same field (Total) on the Cash Collections Schedule.
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.
Cash collections from sales + Total cash disbursements.
Beginning cash balance + Surplus (deficit) of collections over disbursements.
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.
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.
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.
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.
Nothing need be entered here this is all automatically calculated based on your Excess (shortfall) of cash to desired balance.
This will include the repayment of any existing loans and any Additional borrowings needed to shore up cash balances.
Additional borrowings + Repayments
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.
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.
This is the amortization of any existing long-term financing and Additional borrowings entered previously.
Additional borrowings + Repayments
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.
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 balance – Net short-term financing – Net 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.
If the Excess (shortfall) of cash to desired balance – Net short-term financing – Net 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.
Deposits + Withdrawals
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.
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.
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.
The Beginning and Ending cash balance (Cash budget)
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.
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.
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.
Cash + Short-term investments + Accounts receivable + Raw materials inventory + Finished goods inventory.
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.
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.
Land + Property, plant, and equipment.
Total current assets + Total long-term assets.
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.
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).
Includes any existing Short-term financing (Cash Budget) + any Additional borrowings (Cash Budget) needed throughout the year to meet the Desired ending cash balance.
Accounts payable + Accrued expenses + Notes payable.
Includes any existing Long-term financing (Cash Budget) + any Additional borrowings (Cash Budget) incurred throughout the year.
Total current liabilities + long-term liabilities.
Total assets + Total liabilities.
Total equity + Total liabilities.
This must equal Total assets.
Total cash collections from sales (Cash collection schedule).
Sum of total cash payments for DM/DL/OH/op expenses (Cash Disbursements Schedule).
Total interest paid on Short/Long-term financing.
Sum of Cash payments for estimated tax (Cash budget).
Cash collected from customers + Cash paid to employees and suppliers + Interest paid + Income taxes paid.
Sum of Net investments (Cash Budget).
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.
Net investments + Purchase/sale of plant assets.
The difference between the ending and beginning Notes payable from the Pro Forma Balance Sheet.
The difference between the ending and beginning Long-term liabilities from the Pro Forma Balance Sheet.
Short-term debt + Long-term debt.
Cash from operating activities + Cash from investing activities + Cash from financing activities.
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.
Total current assets ÷ Total current liabilities.
This ratio reflects your company’s ability to pay its current liabilities.
Total current assets – Total current liabilities.
The excess of Total current assets over Total current liabilities.
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.
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.
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.
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.
365 ÷ Inventory turnover.
This is how many days worth of sales you could theoretically fulfill with your average level of inventory.
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.
Total liabilities ÷ Total assets
This is the percentage of assets financed by borrowed funds.
Total liabilities ÷ Total equity
This is simply a comparison between the two methods of financing.
Net profit ÷ Total equity
Net profit ÷ Total assets.
Net sales ÷ average Total assets.
This ratio demonstrates how effectively assets were used to generate sales.
Cash from operating activities ÷ Net profit.
This shows you the relationship between how much cash was actually received and net profit.
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!