How To Prepare Store Ledger Account Using Simple & Weighted Average Methods In Ms Excel

The Simple Average Method (SAM) and Weighted Average Method (WAM) are the two most popular methods of pricing material goods in stock control analysis. MS Excel can be used to simplify the whole process. The two methods involves taking the average price of the stock with small difference as you will see below.

This is the part 2 of chapter 4 of the Microsoft Excel Tutorial Series. In this part, I will explicitly explain the Simple Average Method (SAM) and Weighted Average Method (WAM) and how to design their store ledger account in MS Excel 2013 and 2016. I will also give you some practice exercises. Try to do them on your own.

In the previous part (Part 1) of this tutorial chapter, I explained the FIFO and LIFO methods of pricing material goods and how to design their store ledger account in MS Excel.

In this part, I assumed that you are conversant with MS Excel’s Worksheet operation. If not visit chapter 1 – Part 1 and revise Excel worksheet operations before going further into this chapter or see the Excel Full Tutorial Course Content.

This course is mainly for those in accounting field both workers and students.

SIMPLE AVERAGE METHOD (SAM)

In this method, the price used to issue out goods is gotten by first summing the different receipt prices of the materials and then dividing the total price by the number of receipt price used. It is on an average price of the batch irrespective of the quantity involved.

MERITS OF SIMPLE AVERAGE METHOD (SAM)

1. It is easy to operate

2. Fluctuation in price can be ironed out using this method

DEMERITS OF SIMPLE AVERAGE METHOD (SAM)

1. Average may be misleading and distortion in prices of some goods may occur.

2. During inflation, this method will give prices that are closer to later prices which are likely to be higher. Therefore, stocks and profits tend to be overstated.

PRACTICAL/BUSINESS ILLUSTRATION OF SIMPLE AVERAGE METHOD (SAM)

Let me give you another practical illustration of store ledger account with simple average method.

PRACTICAL QUESTION ON SIMPLE AVERAGE METHOD:

Using the information provided below for Star Fast food for the month of January 2017, prepare a store ledger account using the Simple Average Method (SAM) of pricing material goods.

Information:

01/01/2017 – Opening balance: 100 units at $39.00 per unit.

01/05/2017 – Received 200 units at $30 .00 per unit.

01/10/2017 – Received 250 units at $50.00 per unit.

01/12/2017 – Issued 400 units.

01/20/2017 – Received 300 units at 58.00 per unit.

01/25/2017 – Issued 200 units.

01/30/2017 – Issued 200 units.

SOLUTION TO THE SAM QUESTION

The Simple Average Method (SAM) uses the approach of FIFO (First In First Out). Therefore for the first issue on 12th January, which was 400 units, you first take the opening stock which was 100 units at $39 per unit.

Next, you take the first stock receipt on 5th January which was 200 units at $30 per unit. 

To complete the first issue unit to 400 units, you deduct 100 units from the second stock receipt on 10th January which was 250 units at $50. So the first issue will now sum up to 400 units.

Next, you add the various prices together ($39 + $30 + $50 = $119). Since you used three different prices, you divide total of the three used prices by 3 ($119 / 3 = $39.6667). It will give you $39.6667. This price is the price you will use to issue out the 400 units of goods.

Remember that the 250 units stock on 10th January has a balance of 150 units. So you have to make use of it first when issuing out the second batch of goods.

Prepare your store ledger account for Simple Average Method in Microsoft Excel in 3 sections which include: RECEIPTS, ISSUES and BALANCE as shown in the Excel worksheet below.

The opening stock for February 2017 would be 50 units at $30.6664 per unit.

CALCULATION TIPS:

Receipt Amount = Receipt Qty * Receipt Price

Issue Cost = Issue Amount / Issue Qty

Issue Amount = Issue Qty * Issue Price

Balance Price = Balance Amount / Balance Qty

WEIGHTED AVERAGE METHOD (WAM)

Under this method, issues are prized at a weighted average price which is obtained by dividing the amount value of the stock in hand by the number of units of stock in hand. Issues will continue to be priced at that price until another batch of goods is received. Then a new weighted average price is calculated.

The implication of the Weighted Average Method (WAM) is that a new weighted average price is calculated any time a new batch of stock is received.

MERITS OF WEIGHTED AVERAGE METHOD (WAM)

1. It is not cumbersome as in FIFO and LIFO methods.

2. It presents a fair indication of stock value.

3. It evens out fluctuation in the price of material goods.

DEMERITS OF WEIGHTED AVERAGE METHOD (WAM)

1. Issued prices are usually approximated to a number of decimal places, which results to inconsistency in accounting.

2. During inflation, stock and price increase.

3. Issues of production may not reflect current economic value.

PRACTICAL/BUSINESS ILLUSTRATION OF WEIGHTED AVERAGE METHOD (WAM)

This time I will give you another practical illustration of store ledger account using weighted average method.

PRACTICAL QUESTION ON WEIGHTED AVERAGE METHOD:

Using the information provided below for Dangote Groups for the month of January 2017, prepare a store ledger account using the Weighted Average Method (WAM) of pricing material goods.

Information:

01/01/2017 – Opening balance: 100 units at $39.00 per unit.

01/05/2017 – Received 200 units at $30 .00 per unit.

01/10/2017 – Received 250 units at $50.00 per unit.

01/12/2017 – Issued 400 units.

01/20/2017 – Received 300 units at 58.00 per unit.

01/25/2017 – Issued 200 units.

01/30/2017 – Issued 200 units.

SOLUTION TO THE WAM QUESTION

The stock in hand before the first 400 units issue is 550 units (i.e. 100 units + 200 units + 250 units = 550 units). So according to this method, you divide the total amount in hand by the total number of units of stock in hand, which gives you a weighted price of $40.7273. 

You will now use this price to issue out the stocks until another batch of goods are received.

Prepare your store ledger account for Weighted Average Method in Microsoft Excel as shown in in the worksheet below.

The opening stock for February 2017 would be 50 units at $30.6664 per unit.

PRACTICAL EXERCISE FOR STORE LEDGER ACCOUNTING

Try to practice this exercise on your own. It will help you master store ledger accounting with MS Excel

QUESTION:

The table below shows the receipts and issues of Joe-Links Services for the month of February 2017:

As at 1st February 2017, the opening stock was 250 units at $20 per unit. Prepare a store ledger account using:

• FIFO

• LIFO

• Simple Average Method

• Weighted Average Methods of pricing material goods.

This is the end of chapter 4.

In the next chapter (Chapter 5), I will explain how to calculate depreciation using different Excel's built in formulas. 

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.