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.

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

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

## Recommended Entrepreneurship Books

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.

Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.

How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.