June 6, 2019 Budgeting en_US A budget worksheet is the road map to the success of your company. Lear how to use the budget worksheet to navigate your finances. https://quickbooks.intuit.com/cas/dam/IMAGE/A4Weuzt1L/fd630bfb26b3553f86a283c751bab5bf.jpg https://quickbooks.intuit.com/r/budgeting/what-is-a-budget-worksheet How to create and use a budget worksheet
Budgeting

How to create and use a budget worksheet

By Ken Boyd June 6, 2019

Every business owner must create an annual budget in order to plan for costs, forecast revenue, and estimate company profit. To be most effective, you need to create a detailed budget using a set of budget worksheets. This process forces you to consider all of your costs, sales prices, and profit projections in detail, so you’ll end up with a more accurate budget. Accurate budgeting can help you pay down your company credit card and start an emergency fund to protect your company from unforeseen expenses.

If it suits your needs, you can also create a monthly budget worksheet rather than an annual one. Simply substitute monthly income and monthly expenses for the annual income and expenses during calculations.

Use these tips to create budget worksheets and to operate your business more effectively.

Working backward

Many companies start the budgeting process by forecasting total sales and then working backward to determine costs and profit levels.

Assume, for example, that Sally operates Riverbend Furniture, a manufacturer of wood furniture products for the residential market.

Sally creates a budget to produce and sell five types of wood furniture. She then combines the five budgets into a company-wide budget. To keep things simple, consider Riverbend’s budget process for a chest of drawers.

Based on prior year sales and Sally’s years of industry experience, she believes that, during the next calendar year, Riverbend can sell 500 chests of drawers at a retail price of $700.

Her total budgeted revenue is:

(500 units) x ($700 unit price) = $350,000

Once Sally calculates total revenue and total expenses, she can compute the total cost required to produce the revenue.

Cost per unit

You need to budget for the total costs of each product or service that you sell and that budget should be documented on a worksheet.
Here is Riverbend’s budget worksheet for the chest of drawers:

Riverbend Furniture Budgeted Chest of Drawers Cost Per Unit2018 Fiscal Year
Direct costs Per Unit Cost Total Costs Based on Unit Sales
Wood $370
Metal Fixtures 50
Plastic 15
Labor costs 90
Indirect costs
Repair and maintenance- Machinery 7
Factory costs- Utilities 2
Administrative costs- Home office salaries 9
Marketing, advertising 7
Total $550 $275,000

The budget includes direct costs, which can be easily traced to the product, such as material and labor costs. Every product budget also includes an allocation for indirect costs or costs that can’t be directly traced to the product, such as a company vehicle or car payment, vehicle or car maintenance, and vehicle or car insurance, property taxes, business insurance premiums etc. Don’t forget about indirect costs related to employees like Social Security and Medicare taxes, health care, life insurance, etc.
In this case, each chest of drawers produced is allocated $7 for repair and maintenance costs, incurred to operate machinery. Indirect costs are allocated based on an activity level, such as units sold, labor hours incurred, or machine hours.

Based on the sales forecast of 500 units, the total budgeted costs equal $275,000.

Computing profit

Next, Sally can take the revenue and expense totals, and post them into a budgeted income statement for the chest of drawers product.

The income statement is created using the formula:

Revenue – expenses = net income or profit

Riverbend Furniture

Budgeted Chest of Drawers Income Statement

For Period Ending 2018

Revenue $350,000 Expenses $275,000Net Income $75,000

Profit margin is an important tool that Sally can use to compare the profitability of different products that she manufactures.
The profit margin formula is:

Net income ÷ sales = profit margin

Revenue includes sales and other sources of income, such as selling a piece of equipment for a gain. For this budget, assume that all revenue is generated through product sales.

Here is the profit margin for the chest of drawers:

($75,000 net income) ÷ ($350,000 sales) = 21.4% profit margin.

In other words, Riverbend generates a profit of approximately 21 cents for every dollar in chest of drawers sales.

Sally can compare the profitability of all her products then consider increasing the product’s price or reducing expenses if she wants to increase her profit margin. This is the value that a business owner can find in the budget process. A detailed budget allows you to identify potential improvements in your business.

Other worksheets

Here are some other important budget worksheets that Riverbend should create:

  • Cash flow forecast: There may be a big difference between your revenue budget and the dates that you receive customer payments in cash. Sally needs to create a cash flow forecast that estimates when customers will pay for sales and when Riverbend must pay its expenses. This worksheet lists the beginning balance in cash each month, posts estimated cash inflows and outflows, and tallies the ending balance for each month.
  • Balance sheet: Riverbend will also create a balance sheet for the entire company. This budget worksheet is normally created after the company-wide income statement is finalized. The balance sheet is created using the formula:

Assets = liabilities + equity

When Sally projects $30,000 in cash collections during the month of March, for example, the cash account (which is an asset) will increase. All of the budgeted income statement and cash flow activity will impact the balance sheet budget.

  • Capital expenditures: Capital is defined as money invested to purchase assets. Riverbend must plan for expensive asset purchases in future years. If Sally knows that she must replace a $50,000 piece of machinery in two years, she must plan to retain some of her business profits to pay for the purchase, or borrow money to purchase the new equipment. This plan should be a budget worksheet.

Fortunately, you can find free budget worksheets online that make this process easier. It’s also possible to make your own out of an Excel spreadsheet or Google Sheet. Also, note that each budget worksheet discussed here is linked, and all of these worksheets combined help you create a comprehensive budget.

Variance analysis

Once Sally creates all of her budget worksheets for each product, she needs to compare her actual business results to her budget each month. This task, called variance analysis, is the process of finding out why actual results differ from the annual or monthly budget, and the answers have tremendous value.

If wood material costs are higher than budgeted for the chest of drawers, Sally needs to do some digging to find out why actual costs were higher than planned. The reason for the discrepancy may be that Riverbend paid more for a particular amount of lumber than budgeted or that workers may be using more wood than planned.

In either case, Sally may be able to make changes to reduce total monthly expenses. She can try to find another supplier who offers a cheaper price or do some training with her staff to reduce the amount of wood material used in the production process. This is another key value of budgeting: the ability to make proactive changes to improve profitability.

Payoff

Many business owners are hesitant to invest the time and effort required to create budget worksheets, but the process will pay off. Once you have a budget in place, you can monitor your results each month with the help of your budget worksheet and make changes to improve profits. Make the effort — you’ve got this!