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.

Create Primary Outline


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.

Input Opening Stock Data

Read More: How to Make Projected Balance Sheet in Excel


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.

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.


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.


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!


Related Articles


<< Go Back To How to Make Balance Sheet in Excel |Excel For Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

4 Comments
  1. 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?

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

  2. I’ve never done this before and I love the fact that everything was so simple to understand.

    So, does this mean now the Balance Stock becomes The Opening Stock?

    • Reply Mahfuza Anika Era
      Mahfuza Anika Era Feb 27, 2024 at 11:44 AM

      Hi Gabolete Fane,

      Glad this was helpful to you. “Balance stock” can technically become “opening stock”.
      The balance stock refers to the inventory at the end of the period while the opening stock is the inventory at the beginning of a period. So, if your next period starts right after your current period, the balance stock becomes the opening stock. It depends on the time periods you are considering.

      Regards
      Niloy
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo