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

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding.


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 take many big decisions.
  • You can get several financial results and progress from this sheet.
  • Improves inventory order accuracy.
  • Reduces costs and saves time.

Step-by-Step Procedure to Make Stock Balance Sheet in Excel

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.

Create Primary Outline

Read More: Balance Sheet Format in Excel with Formulas (Create with Easy Steps)


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 to calculate the Total Price for the Balance Stock.

Input Opening Stock Data

Read More: How to Make Balance Sheet in Excel (2 Useful Examples)


Similar Readings


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.

How to Make Stock Balance Sheet in Excel

  • 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.

How to Make Stock Balance Sheet in Excel

  • 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.

How to Make Stock Balance Sheet in Excel

  • 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.

How to Make Stock Balance Sheet in Excel

  • 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.

Input Data of Stock Out

  • 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.

How to Make Stock Balance Sheet in Excel

  • 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.

How to Make Stock Balance Sheet in Excel

  • 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.

How to Make Stock Balance Sheet in Excel

  • 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.

How to Make Stock Balance Sheet in Excel

  • 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.

How to Make Stock Balance Sheet in Excel


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.

Calculate Balance Stock

  • 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.

How to Make Stock Balance Sheet in Excel

  • 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.

How to Make Stock Balance Sheet in Excel

  • Next, drag down the Fill handle icon.
  • As a consequence, you will get the Total Price under the Stock Out as shown below.

How to Make Stock Balance Sheet in Excel

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.


💬 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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo