Components Of Operating Budget and Process In Excel
In this article, we will take a hands on drive of learning the components of operating budgets and how we process in excel as well. Excel is the powerful spread sheet software that help every business individual and finance professionals to make budget in it, with very precise and outstanding manner and once it all set, it will also help us to identify different patterns through its dashboard features as well.
There are various components of an operating budget these are used to calculate the operating budget.
- Revenue: It is the sale of different products or services or both in whole year. This is based on Year on Year projection. It is better for us to divide the revenue between unit volume and average price, that can vary monthly to get
better insights. - Variable Cost: These are costs that increase or decrease with revenue. Examples include expenses for raw materials, labor, freight and sales
commissions. - Fixed Cost: Fixed costs are expenses that do not change and must be paid whether sales go up or down. Examples include rent, utilities, equipment leases and insurance.
- Non-Cash Expenses: An operating budget often includes non-cash expenses. The most common non cash expenses include depreciation, amortization, unrealized gains or losses and deferred income taxes.
- Non-Operating Expenses: These are costs that are not directly related to a business’s main activity. The most common non-operating expenses are interest payments, losses on the disposition of assets and costs of currency
exchanges.
Examples in Excel:
Step-by-step calculation:
- Sales Budget: To create a budget, we need to find the source of revenue, it is
called a sales budget. Estimation of unit sales in a year is S. No. 1 is shown in the figure below. Sales Price ₹1,500/- as given in S. No. 2.
We get sales revenue after multiplying S. No. 1 with S. No. 2, as shown in S. No. 3.
We estimate that 75% cash sales will happen in the whole year, and the remaining 25% will be credit sales. as shown in S. No. 4 and 5.
S. No. 1 is number, S. No. 2,3,6,7,8 are currency and S. No. 4,5
are percentages in the above figure. S. No. 6 and 7 are the cash sales and credit sales figures, respectively, while S. No. 8 is the total sales revenue, same as S. No. 3.
- Production Budget: The next step is to estimate the production budget. In a manufacturing unit, we need to know how much stock we must produce during the year as per sales estimates. We must understand how much closing stock we must maintain every month. In our example, we are
maintaining 20% of Sales Unit as shown in S. No. 10 of Production Budget in the figure below.
S. No. 9 is Unit Sales, which is equal to S. No. 1 of Sales Budget,
as shown in the above figure.
S. No. 10 is the desired closing stock, which we want to maintain as discussed earlier, it will be 20% every month.
S. No. 11 is the sum of S. No. 9 and 10, while S. No. 12 is the opening stock of
the month, which is equal to the closing stock of the previous month.
S. No 9 to 13 are numbers.
- Direct Material Budget: There are two direct materials required for our product, so we will two have direct material budgets.
- DM 1:
S. No. 14 is equal to S. No. 13 of production budget.
S. No. 15 is the estimate figure that is required to produce product of S. No.14 per Unit.
S. No. 16 is the total quantity needed, while S. No. 17 is the desired closing stock 20% Estimation of S. No. 16, as shown in the above figure. S. No.18 is the stock that needs to be maintained, which is sum of S. No. 16
and S. No. 17. S. No. 19 is the opening stock, which is equal to the closing of the previous
month, that is, Apr 22 closing stock is equal to May 22 opening stock, May 22 closing stock is equal to Jun 22 opening stock, as so on.
S. No. 20 is the sum of S. No. 18 and 19 as we will get the figure of total direct material 1 that required for the month.
S. No. 21 is the estimated amount for rate per unit. In our example, it is kg.
S. No. 22 is the total cost for DM 1 purchase, which is equal to S. No. 20 multiplied by S. No. 21.
S. No. 14 to 20 are numbers, while S. No. 21 and 22 are in currency format.
- The calculation for the second direct material is the same as that for DM 1.
S. No. 23 is equal to S. No. 13 of the production budget.
S. No. 24 is the estimate figure that is required to produce the product of S.
No. 23 per unit.
S. No. 25 is the total quantity needed, while S. No. 26 the desired closing stock 20%, Estimation of S. No. 25, as shown in the above figure.
S. No. 27 is the stock that needs to be maintained, which is the sum of S. No. 25 and S. No. 26.
S. No. 28 is the opening stock, which is equal to the closing stock of the previous month, that is, Apr 22 closing stock is equal to May 22 opening stock, May 22 closing stock is Jun 22 opening stock and so on.
S. No. 29 is the sum of S. No. 18 and 19 as we will get the figure of total direct material 2 that required for the month.
S. No. 30 is the estimated amount for rate per unit. In our example, it is kg.
S. No. 31 is the total cost for DM 1 purchase, which is equal to S. No. 29 multiply by S. No. 30
S. No. 23 to 29 are numbers, while S. No. 30 and 31 are in currency format.
- Direct Labor Budget:
The next step is to calculate Direct Labor. Let’s figure out it as following,
S. No. 32 is equal to S. No. 13 of production budget, as shown in
the figure below.
S. No. 33 and S. No. 35 are the estimation of direct labor hours per unit and direct labor cost per hour, respectively.
S. No. 34 is the total number of hours required, and S. No. 36 is the total
direct labor cost.
S. No. 32 to 24 are numbers, while S. No. 35 and 36 are in currency format.
- Manufacturing Overheads Budget:
S. No. 37 is equal to S. No. 13 of the production budget.
After that, we calculate variable overheads.
S. No. 38, 39 and 40 are the estimation of indirect material, indirect labor and utilities, as shown in the figure below. S. No. 41 is the total of all variable costs.
S. No. 42, 43,44 are the estimation of various fixed costs.
S. No. 45 are the total of all fixed costs.
S. No. 46 is depreciation of Manufacturing equipment as shown in S. No. 47 of the above figure.
S. No. 48 is the total of total variable cost, fixed cost and depreciation.
S. No. 37 is number and all other are currency format.
- Cost of Goods Sold: Now we will get the cost of goods sold per unit.
S. No. 49 is equal to the total units produced as per S. No. 13 of the production
budget.
S. No. 50 and 51 are the total of DM 1 and DM 2 after subtracting the closing stock of March 23 as the closing stock is not used for the same year.
Let’s understand the calculation,
Total of DM 1 as per S. No. 22 = 1,18,99,320/-
Closing stock of March 23 as per S. No. 17 = 4,600
Rate as per S. No. 21 = ₹190/-
Direct Material Cost is = 1,18,99,320 – 4600*190 = 1,17,24,520/-, which is equal to cell no. D9 in Figure 7.8 of COGS.
Now, cost per case = Cell D9/Cell D6 = 1,17,24,520/30,584 = 380
So, DM 1 cost per unit/case = 380 for that year.
The same is for DM 2 S. No. 51.
S. No. 52 are direct labor per case.
S. No. 53 is the sum of cost per case of DM 1, DM 2 and DL, which are S. No. 50,51 and 52, respectively.
S. No. 54, 55 and 56 are various variable overheads, and S. No. 57 is the total
of all variables.
S. No. 58,59 and 60 are various fixed overheads and S. No. 61 is the total of all fixed overheads. S. No. 62 is depreciation and S. No. 63 is cost per case in depreciation.
S. No. 64 is the total of total cost per case.
S. No. 49 is in number format, and all others are in currency format.
- Operating Budget:
Our next step is to calculate operating budget on estimation basis.
S. No. 65 is equal to S. No. 1 of the sales budget.
First, we will estimate the variable and fixed operating expenses, as
shown in the figure below. S. No. 66 and 67 are variable cost; S. No. 69, 70, 71 and 72 are fixed costs and S. No. 68 and 73 are the total of both respectively.
S. No. 74 is the depreciation of S. No. 75 office equipment as shown
in the above figure.
S. No. 76 is the total of all operating costs. S. No. 65 is in number format and all others are in currency format.
- Capital Expenditure Budget
Our next step is to identify Capital Expenditure, as shown in the figure below.
S. No. 77 is equal to S. No. 75 of operating expenses budget, as we purchase office equipment as per our requirement and spend money as required.
S. No. 78 is equal to S. No. 46 of the manufacturing overheads budget, as we
purchase fixed assets as required for the company.
S. No. 79 is the total of all assets, as shown in the above figure.
All figures are in currency format.
- Collection and Payment:
As sales happen, we will collect cash against it and pay for the expenses as needed.
As shown in the above figure, S. No. 80 is equal to the cash we collect from sales as per S. No. 6 -Cash Sales.
S. No. 81 is the credit sales that will be received after one month of sale, that is, April 22 credit sales will be received in May 22, and May 22 sales amount
will be received on June 22. The figure of March 23 will be displayed in the budgeted balance sheet, which we will do later.
S. No. 82 is the total of S. No. 80 and 81.
All figures are in currency format.
As we can see the above figure, S. No. 83 is the cash payment of Direct Material. Remember that we consider a 1-month credit period for payment of Direct Material, so we will not pay anything in April 22,
the April 22 purchases will be paid for in May 22 and so on. And March 23
purchases will go to the Budget Balance Sheet in Accounts Payable. It is equal to DM 1 S. No. 22 + DM 2 S. No. 31.
S. No. 84 is the cash payment of Direct Labor, which is equal to S. No. 36.
S. No. 85 is Cash Payment of Fixed Cost and Variable cost of manufacturing overheads, which is equal to S. No. 41 + S. No. 45.
S. No. 86 is cash payment of Fixed Cost and Variable Cost of Operating Expenses, which is equal to S. No. 68 + S. No. 73.
S. No. 87 is the payment of fixed assets, which is S. No. 79.
S. No. 88 is the estimation of the dividend we pay on a quarterly basis to stakeholders.
S. No. 89 We must keep it blank for now, this will come from the income
statement, which we will calculate in step 11, we are currently on step 9. It will be equal to S. No. 109 of the income statement. We will calculate 30% tax on EBT.
S. No. 90 is the total of all payments, from S. No. 83 to 89.
All figures are in currency format.
- Combined Cash:
Now we have Collection and Payment figures, so we will combine both.
S. No. 91 is the Opening Cash Balance for the month, which is equal to the
closing cash balance of the previous month.
We will not have the Opening balance in Apr 22, as shown in the figure below.
Cell D6. S. No. 91 is equal to Last Month Closing Balance of S. No. 100.
S. No. 92 is the estimation of the initial capital introduced in the business.
S. No. 93 is equal to S. No. 82.
S. No. 94 is the total of S. No. 91, 92 and 93.
S. No. 95 is equal to S. No. 90.
S No. 96 is difference between S. No. 94 and 95.
Sometime we need financing(debt/equity) to keep when the cash balance
go dip or in negative. Suppose we maintain minimum cash balance of ₹2,00,000/- every month, as shown in the figure below, S. No. 101.
We need new borrowing as S. No. 97. Let us understand the calculation of how much we need and how we will repay the debt.
The following is the formula for Apr 22, =IF(D11-D15-D16>$D$20,0,$D$20-D11+D15+D16)
Where D11 is Apr 22 Ending Cash before Financing (S. No. 96).
D15 is debt repayment of the previous month (S. No. 98). Apr 22 will not have any repayment. S. No. 98 is equal to S. No. 97, which is payable next month, which means Apr 22 borrowings are payable in May 22, and May 22
borrowings are payable in Jun 22. For March 23, this will go in the budget balance sheet under current liabilities.
D16 is the interest paid on the debt of the last month, along with debt (S. No.99). In our example, we consider 1% pm of the debt amount.
D20 is the Minimum Cash Balance requirement as S. No. 101.
Let’s keep no. instead on cell no. =IF(392169-0-0>200000,0,200000-392169+0+0) =IF(392169>200000,0,-292169)
We can see that the if condition comes true, so the answer will be 0.
Let’s take the same for Sep 22, where we have all figures in S. No. 96,98 and 99.
The Formula for Sep 22 is as follows:
=IF(I11-I15-I16>$D$20,0,$D$20-I11+I15+I16)
=IF(-276745-208740-2087>200000,0,200000-(-276745)+208740+2087)
=IF(-487572>200000,0,687572)
Here, the if condition is false, so the answer will be 678572. So, the borrowing required for Sep 22 is ₹6,78,572/-
In Apr 22, May 22, Jul 22 and Nov 22, we don’t need borrowings as we have
enough cash available for cash flow.
All figures are in currency format.
- Income Statement:
We have completed our budgeting part, now we need to make an income statement and a balance sheet.
In this step, we will work on the income statement to know how much profit we earn in the entire year.
S. No. 102 is sales revenue, which is equal to S. No. 3.
S. No. 103 is COGS, which is equal to the total units’ sales multiplied by COGS per unit S. No. 6 * S. No. 64.
S. No. 104 is gross profit, which is the difference between sales revenue and COGS (102-103).
S. No. 105 is operating expenses, which is equal to 76.
S. No. 106 is the operating profit, difference of gross profit and operating expenses, as shown in the figure below.
S. No. 107 is the interest paid during the year, which is equal to S. No. 99 of combined cash.
S. No. 108 is EBT, which is equal to operating income and interest expenses (106-107).
S. No. 109 is tax against income; we estimated 30% of EBT, 30% of 108.
S. No. 110 is net income, which is the difference between EBT and Tax (108-109).
We might earn ₹33,26,477/- in the year.
All figures are in currency format.
- Budgeted Balance Sheet: We have reached our last milestone to complete the budgeting process. The purpose of making a budgeted balance sheet is to know how much we owe at the end of the year/period.
In the figure below, shows the budgeted balance sheet of our budget.
S. No. 111 is the cash balance from combined cash, closing of S. No. 99.
S. No. 112 is accounts receivable, which is the closing of March credit sales of
S. No. 7.
S. No. 113 is the total of raw material of DM 1 and DM 2 March closing stock multiplied by their rates. March Closing Stock S. No. 17* Rate per unit S. No.
21 for DM1 + March Closing Stock S. No. 26* Rate per unit S. No. 30.
S. No. 114 is equal to March Closing Stock of production S. No. 10 multiplied by COGS per unit S. No. 64 of COGS.
All the preceding four are current assets and S. No. 115 is the total of S. No.
111 to 114.
S. No. 116 is the fixed assets, which are equal to total Capital Expenditure less Operating Expenses, Depreciation and Manufacturing Overheads
Depreciation, Total of S. No. 79 – Total of S. No. 46 – Total of S. No. 74. S. No. 117 is the total of current and fixed assets.
S. No. 118 is accounts payable, which is equal to March pending of DM 1 and
DM 2.
Total of Balances of March 23 of S. No. 22 and S. No. 31)
S. No. 119 is the closing balance of March of new borrowings S. No. 97.
S. No. 120 is the closing total of all current liabilities, which is equal to the sum of S. No. 118 and 119.
S. No. 121 is the capital invested by stakeholders, which is equal to the total of S. No. 91.
S. No. 122 is the retained earnings, which is equal to net profit less total dividend paid during the year Total of S. No. 110 – Total of S. No. 88.
S. No. 123 is total equity, which is equal to sum of S. No. 121 and 122, Common Stock + Retained Earnings.
S. No. 124 is total of equity and liabilities, which is equal to S. No. 120 + 123.
All figures in currency format.
With this, we have completed our budget preparation.
Conclusion
Finally, we will understanding the fundamentals of corporate budgeting, components and along with that we also take a hands on drive of creating budget using microsoft excel and in this guide we know very well, how important it is to know the financial health as well as our sales progress and after result of a budget we figure out better decisions for our company or business that elevate us to success.
