How to use the Excel budget template
1. Enter prior year information
Input the year-end balance sheet and income statement into your Excel template.
Equity is the difference between assets and liabilities. Equity is the true value of your business. The $10,000 net income in the income statement increases equity in the year-end balance sheet.
2. Forecast future sales and costs
Next, we’ll make some assumptions for the upcoming year. In this example, Widget Inc.’s budget accounts for changes in sales, cost of sales, employee payroll costs, and other expenses.
Some accounting transactions, however, are unusual and cannot be included in a budget. For example, Widget Inc. had a $5,000 loss on the sale of equipment in the prior year.
Widget Inc.’s primary business is manufacturing and selling widgets, and the sale of equipment is not part of its annual budget. Accountants refer to these transactions as non-operating income and losses because day-to-day business operations don’t generate them.
3. Create a budget income statement
Widget Inc. produces a budget income statement using the assumptions in step 2. Note that the Widget Inc. budget has a line item for gain or loss on sale, other revenue. While the company may have an actual transaction in the category, this line has a zero budget.
4. Create a budgeted balance sheet using assumptions
The budgeted balance sheet includes assumptions that address each of the line items in the report. The budget provides percentage increases or decreases from the prior year, and two categories have no change.
5. Create a budgeted cash roll-forward for January
A cash roll-forward report lists your beginning cash balance, expected cash inflow and outflows, and your ending balance in cash for each month of the year.
The balance sheet and the income statement are financial statements, but the cash roll-forward is an informal, internal report. The statement of cash flows is generated as part of your financial statements, but a cash roll-forward is more useful for budgeting purposes.
Business owners should create cash roll-forwards last because they need a cash projection for each month. Step 5 presents the cash roll-forward for January. You’ll see that the beginning of January cash balance ($10,000) is the year-end cash balance in the prior year balance sheet.
The majority of cash collections are from sales, and the bulk of cash payments are for inventory. When a business sells inventory, the dollar amount transfers to the cost of sales, which is the largest expense in the budget. So it makes sense that the biggest cash outflow is for inventory.
Widget Inc. assumes that cash received for sales and the cash paid for inventory are evenly distributed over 12 months. In reality, the payments will be uneven, but the cash inflows and outflows can be difficult to predict by month.
The budget also includes miscellaneous receivables collected in cash and other cash payments, such as payroll costs.
6. Budgeted cash roll-forward for February, other months
The beginning cash balance for February ($10,500) is the ending cash balance for January, and this connection applies to each month of the year. The February cash budget uses some of the same assumptions for sales and inventory purchases. The ending cash balance for February is also the March beginning cash balance.
Widget Inc. continues this same cash roll-forward process until the company has a cash budget for each month.