HOW TO PREPARE STORE LEDGER ACCOUNT/STOCK CONTROL IN MS EXCEL – FIFO & LIFO

A store ledger account is an account prepared to evaluate the quantity of goods bought and the quantity of goods sold. From the date of purchase of goods to the date the goods were issued out, a lot of things may have happened. Some of these incidence include: inflation, deflation, etc. when issuing out goods from the store, problems arise as to whether the original purchase price or the immediate current price of the goods should be used. This has led to different methods of pricing stock issues. If you have been preparing your store ledger account, I will show you how you can automate it in Microsoft Excel 2013 and 2016.
FIFO and LIFO method of issuing out goods - MS Excel

Store Ledger is an accounting tool, used for accounting analysis.
This is chapter 4 of the MS Excel Tutorial Series. This chapter has two parts and this is the part 1. 
In this part, I will explain how to use FIFO and LIFO method to issue out goods in stock and how to prepare their store ledger account using MS Excel. In Part 2, I will explain Simple Average method (SAM) and Weighted Average Method (WAM).

In this chapter, 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.

METHODS OF ISSUING AND PRICING STOCK GOODS

There are four common methods of pricing stock issues. They include:
1. FIFO Method
2. FIFO Method
3. Simple Average Method
4. Weighted Average Method

Other Methods of Issuing Out Goods Include:

1. Based Stock Method
2. Replacement Method
3. Standard Price Method
4. Actual Price Method

I am going to emphasize only on the four common methods.

FIFO METHOD

FIFO means First In First Out. This method emphasizes a situation whereby the first batch of goods received will be the first set to be issued out.

MERITS OF FIFO METHOD

1. FIFO method is a good representation of stock keeping since the oldest stock will be issued out first.
2. It is an actual cost system especially when no inflation or deflation period is encountered.
3. Unrealized profits does not arise since it is an actual cost system.

DEMERITS OF FIFO METHOD

1. It is cumbersome to calculate because of the number of calculations involved.
2. During the period of inflation, product’s costs are over estimated. But during the period of deflation, product’s costs are under estimated.

PRACTICAL/BUSINESS ILLUSTRATION OF FIFO METHOD

All these while, I have been dealing on the theoretical aspect of store ledger account. Now, let me now show you the real life application/illustration.

FIFO PRACTICAL QUESTION:

From the following information of material DNL of Mr.Bigg’s Fast-food shown below, prepare a store ledger account for the month of December 2016 using FIFO method of pricing material goods.

Information:

12/01/2016 – Opening balance: 100 units at $39.00 per unit.
12/05/2016 – Received 200 units at $30 .00 per unit.
12/10/2016 – Received 250 units at $50.00 per unit.
12/12/2016 – Issued 400 units.
12/20/2016 – Received 300 units at 58.00 per unit.
12/25/2016 – Issued 200 units.
12/30/2016 – Issued 200 units.

SOLUTION TO THE FIFO QUESTION

For the first issue on 12th December, you first take the opening stock receipt which was 100 units at $39.00 per unit (100 * $39 = $3900). 

Next, you take the first receipt stock on 5th December which was 200 unit at $30.00 per unit (200 * $30 = $6000).

Since you still need 100 units to complete the first 400 units issue, deduct 100 units at $50.00 (100 * $50 = 5000) from the second receipt stock on 10th December.

Then adding up the amounts will give you a total amount of $14900 as shown below.

Follow this pattern when issuing out the subsequent stocks. But remember that the receipt stock on 10th December remains 150 units. So when issuing out the second batch, you will first take it. 

Prepare your stock control/store ledger account in MS Excel in 3 sections which include: RECEIPTS, ISSUES and BALANCE as shown below.
FIFO Store Ledger Account in Excel Spreadsheet
The opening stock for January 2017 was 50 units at $58.00 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

LIFO METHOD

LIFO means Last In First Out. It emphasizes a situation whereby the last batch of goods received are issued out first.

MERITS OF LIFO METHOD

1. It is simple to operate than FIFO method.
2. The profit disclosed by LIFO method seems to be stable and can serve as a better guide to the management.

DEMERITS OF LIFO

1. LIFO method is cumbersome at times to operate due to the number of calculations involved, especially if the receipt stocks batches come in small quantities while stocks are issued out in large quantities. 
2. Stock valuation at the end of the year seems to be out within the current price level.
3. This is not a fair method especially when used to compare job cost.

PRACTICAL/BUSINESS ILLUSTRATION OF LIFO METHOD

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

LIFO PRACTICAL QUESTION:

From the following information of material DNL of Vegan Fast-food shown below, prepare a store ledger account for the month of December 2015 using LIFO method of pricing material goods.

Information:

12/01/2015 – Opening balance: 100 units at $39.00 per unit.
12/05/2015 – Received 200 units at $30 .00 per unit.
12/10/2015 – Received 250 units at $50.00 per unit.
12/12/2015 – Issued 400 units.
12/20/2016 – Received 300 units at 58.00 per unit.
12/25/2015 – Issued 200 units.
12/30/2015 – Issued 200 units.

SOLUTION TO THE LIFO QUESTION

As the name implies (Last In First Out), you will issue out the last stock receipt first. The last batch of stock was received on 20th December. 

So for the first batch of stock issue take the last stock receipt which was 300 units at $58 per unit (300 * $50 = $17400). 

To complete the quantity for the first issue to 400 unit, deduct 100 units from the 10th December 250 units at $30 per unit receipt stock (100 * $30 = $3000).

Adding the two amount will give you a total of $20400 as shown below.

Follow this pattern when issuing out the subsequent stocks. But remember that the receipt stock on 10th December remains 150 units. So when issuing out the second batch, you will first take it. 

Prepare your stock control/store ledger account in MS Excel in 3 sections which include: RECEIPTS, ISSUES and BALANCE as shown below.
The opening stock for January 2016 was 50 units at $79.00 per unit.
Try to build more practical exercises of FIFO and LIFO method on your own

This is the end of Part 1. 

Recommended MS Excel Textbook

Click Here to know more about the book.

In Part 2, I will explain the Simple Average Method and the Weighted Average Method (WAM) and how to design their store ledger in MS Excel. I will give you some practical exercises at the end of Part 2 to test your knowledge.

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.

Share this

Related Posts

Previous
Next Post »

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!

Like Our Page Today