Step 1: Create a Primary Outline
Create a dataset like as shown below.
Step 2: Enter Opening Stock Data
- In the Item column, enter the name of the product.
- In the Unit Price column, enter the unit price.
Read More: How to Make Projected Balance Sheet in Excel
Step 3: Input Data of Stock In
- To create a Stock In, enter the Serial No and Date columns as shown below.
- To convert the products into a drop-down list in the Item column, go to the Data tab and select Data Validation.
- In Allow, choose List.
- In Source, select your data range.
- Click OK.
- This is the output.
- Enter the product name from the drop-down list in the Item column.
- Enter the quantity of each product that will be added to the stock.
- To calculate the Total Price of each product, use the following formula in J6.
=E6*I6
- Press Enter.
- Drag down the Fill handle.
- This is the output.
Read More: How to Tally a Balance Sheet in Excel
Step 4: Input Data of Stock Out
To create a Stock Out dataset, enter the Date column as shown below.
- To convert the products into a drop-down list in the Item column, go to the Data tab and select Data Validation.
- In Allow, choose List.
- In Source, select your data range.
- Click OK.
- This is the output.
- Enter each product name from the drop-down list in the Item column as shown below.
- Enter the quantity that will be sold.
- This is the output.
- To calculate the Total Price of each product, use the following formula in R6.
=M6*E6
- Press Enter.
- Drag down the Fill handle.
- This is the output.
Step 5: Calculate Balance Stock
The SUMIF function will be used to find out the quantity under the Balance Stock condition.
- Enter the Item column.
- In the Item column, enter the product name.
- To calculate the Quantity of each product, use the following formula in 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)
- Press Enter.
- Drag down the Fill handle.
- This is the output.
- To calculate the Total Price of each product, use the following formula in R6.
=Q6*E6
- Press Enter.
- Drag down the Fill handle.
- This is the output.
Formula Breakdown
- SUMIF($C$6:$C$15,P6,$D$6:$D$15)
Here, $C$6:$C$15 is the range in which you will check your criteria. P6 means Product A and is the condition. $D$6:$D$15 is the sum range of the selected rows. The function will return 150.
- SUMIF($C$6:$C$15,P6,$D$6:$D$15)+SUMIF($H$6:$H$15,P6,$I$6:$I$15)
The plus sign (+) is used for the OR operation. SUMIF($H$6:$H$15,P6,$I$6:$I$15) will sum the quantity of Product A and return 100. The whole formula will sum the Opening Stock of Product A and the Stock In quantities and return 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 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
In the SUMIF function enter all parentheses. Make the range and [sum_range] an absolute cell reference, and adjust row height after each method.
Download Practice Workbook
Download this practice workbook to exercise.
Related Articles
- How to Prepare Balance Sheet from Trial Balance in Excel
- How to Calculate Running Balance Using Excel Formula
- How to Keep a Running Balance in Excel
- Debit Credit Balance Sheet with Excel Formula
- Calculate Debit Credit Running Balance Using Excel Formula
- How to Make Profit and Loss Account and Balance Sheet in Excel
<< Go Back To How to Make Balance Sheet in Excel |Excel For Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
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?
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