# How to Make Stock Balance Sheet in Excel (with Quick Steps)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to make a stock balance sheet in Excel, you’ve come to the right place. There is one way to make a stock balance sheet in Excel. This article will discuss every step of this method to make a stock balance sheet in Excel. Let’s follow the complete guide to learn all of this.

## Overview of Stock Balance Sheet

In a stock balance sheet, inventory is ordered, stored, tracked, and controlled. Each of the items used to produce a business’ products or services is listed on the stock balance sheet. This includes all the items in an organization’s inventory.

Here, Stock In means the condition when new quantities of each product will be added to the stock after opening the stock. The Stock Out means the condition when quantities of each product will be sold from the stock. The Balance Stock means the condition of how many quantities of each product remain after selling out.

Here, to calculate the Total Price, we use the following formula:

Total Price = Unit Price* Quantity

We also use the following formula to calculate quantity in the Balance Stock condition:

Quantity in Balance Stock = (Quantity in Opening Stock+ Quantity in Stock In) – Quantity in Stock Out

## Advantages of Keeping Stock Balance Sheet

The advantages of keeping a stock balance sheet are as follows:

• You can track the progress of a company pretty easily and faster with the help of this sheet and make many big decisions.
• You can get several financial results and progress from this sheet.
• Improves inventory order accuracy.
• Reduces costs and saves time.

## How to Make Stock Balance Sheet in Excel: Step-by-Step Procedure

In the following section, we will use one effective and tricky method to make a stock balance sheet in Excel. To create a more understandable stock balance sheet, it is necessary to make a basic outline and calculations with formulas and calculate the final stock balance. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

### Step 1: Create Primary Outline

To create a stock balance sheet, we have to follow some specified rules. At first, we want to make a dataset. To do this we have to follow the following rules.

• Firstly, write ‘Stock Balance Sheet’ in some merged cells at a larger font size, That will make the heading more attractive. Then, type your required Headline fields for your data. Click here to see a screenshot that illustrates what the fields look like. ### Step 2: Input Opening Stock Data

Now, after completing the heading part, you have to create an opening stock dataset. To do this, you have to follow the following procedures.

• First of all, in the following image, we can see the basic outlines of the stock balance data and its related dataset.
• Here, we have Serial No, Item, Quantity, and Unit Price columns in the following dataset.
• Next, in the Item column, we enter each product name.
• Then, in the Unit Price column, we type each product’s unit price which is an essential part of calculating the Total Price for the Balance Stock. ### Step 3: Input Data of Stock In

Now, we want to create a Stock In dataset. To do this, let’s walk through the following steps.

• First of all, you have to enter the Serial No and Date column as shown below. • Now, we want to convert the products into the drop-down list in the Item column. To do this, go to the Data tab and select Data Validation. • Next, when the Data Validation window appears, choose List from the Allow box and select your required range of data in the Source box.
• Then, click on OK. • As a consequence, you will get the following drop-down list in the Item column. • Now, we will enter each product name from the drop-down list in the Item column as shown below. • The next step is to enter how many new quantities of each product will be added to the stock.
• As a consequence, we will get the Quantity column under the Stock In. • Next, to calculate the Total Price of each product, we have to use the following formula in cell J6.

`=E6*I6`

This formula will return us the total price for each product.

• Then, press Enter. • Next, drag down the Fill handle icon.
• As a consequence, you will get the Total Price under the Stock In as shown below. Read More: How to Tally a Balance Sheet in Excel

### Step 4: Input Data of Stock Out

Now, we want to create a Stock Out Dataset. To do this, let’s walk through the following steps.

• First of all, you have to enter the Date column as shown below. • Now, we want to convert the products into the drop-down list in the Item column. To do this, go to the Data tab and select Data Validation. • Next, when the Data Validation window appears, choose List from the Allow box and select your required range of data in the Source box.
• Then, click on OK. • As a consequence, you will get the following drop-down list in the Item column. • Now, we will enter each product name from the drop-down list in the Item column as shown below. • The next step is to enter how many quantities of each product will be sold from the stock.
• As a consequence, we will get the Quantity column under the Stock Out. • Next, to calculate the Total Price of each product, we have to use the following formula in cell R6.

`=M6*E6`

• Then, press Enter. • Next, drag down the Fill handle icon.
• As a consequence, you will get the Total Price under the Stock Out as shown below. ### Step 5: Calculate Balance Stock

Now, we want to create a Balance Stock data.  To do this, you have to follow the following steps. Here we will use the SUMIF function to find out the quantity under the Balance Stock condition.

• First of all, you have to enter the Item column as shown below.
• In the Item column, you have to enter each product name. • Next, to calculate the Quantity of each product, we have to use the following formula in cell Q6.

`=SUMIF(\$C\$6:\$C\$15,P6,\$D\$6:\$D\$15)+SUMIF(\$H\$6:\$H\$15,P6,\$I\$6:\$I\$15)-SUMIF(\$L\$6:\$L\$15,P6,\$M\$6:\$M\$15)`

This formula will return the quantity value in the Balance Stock condition.

• Then, press Enter. • Next, drag down the Fill handle icon.
• As a consequence, you will get the Quantity under the Balance Stock as shown below. • Next, to calculate the Total Price of each product, we have to use the following formula in cell R6.

`=Q6*E6`

• Then, press Enter. • Next, drag down the Fill handle icon.
• As a consequence, you will get the Total Price under the Stock Out as shown below. Following the above process step by step, we will finally be able to make a stock balance sheet.

🔎 How Does the Formula Work?

• SUMIF(\$C\$6:\$C\$15,P6,\$D\$6:\$D\$15)

Here, \$C\$6:\$C\$15 is the range where we will check our criteria. P6 means Product A is the condition or criteria. We check if the Item is Product A or not. Lastly, \$D\$6:\$D\$15 is the sum range where we will perform the sum operation of the selected rows. That function will finally return us 150.

• SUMIF(\$C\$6:\$C\$15,P6,\$D\$6:\$D\$15)+SUMIF(\$H\$6:\$H\$15,P6,\$I\$6:\$I\$15)

Here, Plus sign (+) is used for OR operation. The SUMIF(\$H\$6:\$H\$15,P6,\$I\$6:\$I\$15) will sum up Product A’s quantity and return us 100. The whole formula will sum up Product A’s Opening Stock and Stock In quantities and return us 250.

• SUMIF(\$C\$6:\$C\$15,P6,\$D\$6:\$D\$15)+SUMIF(\$H\$6:\$H\$15,P6,\$I\$6:\$I\$15)-SUMIF(\$L\$6:\$L\$15,P6,\$M\$6:\$M\$15)

This formula adds the quantity of Product A’s in Opening Stock and Stock In, subtracts the quantity of Stock Out, and returns 170.

Read More: How to Make Trial Balance in Excel

## 💬 Things to Remember

✎ When you use the SUMIF function carefully give all the required parentheses. Then, you should also make the range and [sum_range] absolute cell reference, otherwise, you will not get the proper value.

✎ Next, you have to adjust row height after following each method.

## Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to make a stock balance sheet in Excel. If you have any queries or recommendations, please share them in the comments section below. Keep learning new methods and keep growing!

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

1. Reply I’ve followed and read carefully, I’ve not done accounts,but I’ve used your formula and I can proudly say it’s of great help.Can I prepare it in an empty Excel sheet so that if I put the figure it will automatically balance?

• Reply Thank you for your question. Yes, you can prepare it on an empty Excel sheet so that it will balance automatically when the figure is entered. Advanced Excel Exercises with Solutions PDF  