Sales Returns for FIFO

[ShawnMichael]

Hi all !

This is my first post in this forum. I want to create FIFO Sales Return UDF. Before getting down to technical aspects, I'd like to clarify a FIFO example and then the sales returns.


Here's a small inventory purchases table.

Date Product Quantity Cost


01/03/2014
Test-01
68.00
15.00








05/03/2014
Test-01
140.00
15.50


11/03/2014
Test-01
40.00
16.00


16/03/2014
Test-01
78.00
16.50




On 9th March, we sold 94 units. Now FIFO allocates the cost tranche by tranche. So the cost out for these 94 units would be $1,423 [(68 units x $15) + (26 units x $15.50)].

Then on 20th March, we sold 116 units. The FIFO cost would be $1,799 (114 units x $15.50) + (2 units x 16)].

So the total cost out is now $3,222

But now, on sales sheet, if we write -210 units on Qty Sold column, total COGS should be -3,222. The formula is still showing some balance.

The current sheet set up is as follows:

Purchases:
A: Date of Purchase
B: Product
C: Quantity Purchased
D: Cost

Sales:
A: Date of Sale
B: Product
C: Quantity Sold
D: FIFO Cost


In other words, FIFO returns work exactly in an opposite way on FIFO sales. How can we create UDF for this ?

I would need your help in this regards and I would be thankful for that.

Regards,

Michaels

[Bob Phillips]

I would suggest you knock up an example workbook, with data and expected results. I got lost in your explanation.

[ShawnMichael]

Hi XLD !

Thanks for your reply. Please find attached the workbook.

I will explain again.

Here is how FIFO works. Below in a Purchase Table.



01/03/2014
Test-01
68.00
15.00








05/03/2014
Test-01
140.00
15.50


11/03/2014
Test-01
40.00
16.00


16/03/2014
Test-01
78.00
16.50




Now when the company sells 94 items on 9th March 2014, the cost out for those 94 items is calculated this way:

68 units x 15 (from first tranche)
26 units x 15.5 (from second tranche in purchase table)

= $1,423

So the cost, which came in first is the first one to go out, hence FIRST IN FIRST OUT (FIFO).

Now, for the Sales Return, exactly, opposite principle is applied. For example, now we had returns for 60 items.

The UDF would calculate it this way:

26 units x 15.5 (from second tranche)
34 units x 15 (from first tranche from sales table)

= $913

So returns are exactly reversing the FIFO principle, in other words, exact cost reversing.

I hope I have explained it well for your kind consideration.

Thank you for your help.

Regards

[SamT]

The Purchases and Sales databases are permanent records. They only get larger, except at End Of Year purging, (if any.)

You need an inventory database in the middle, which is an active database, ie it shrinks with Sales and grows with Purchases.

If this is to be a standalone Excel system, I would use one UserForm to enter purchases in the Purchases and Inventory Databases, One to record material received in the Inventory database, and another to record sales in the Sales and Inventory Databases.

[ShawnMichael]

Hi SamT !

Thank you for your reply. You have added "Inventory" sheet. Yes, this is to be a standalone Excel system, but I am a novice VBA guy, with no experience in userform. This is a complete FIFO system, valuing cost of goods sold under FIFO, on perpetual basis.

The way you have structured is a good idea, but it will involve a lot of coding I guess.

By the way, I have created a small inventory table in the original workbook, which shows products, qty purchased, sold, costs of goods sold and closing stock value.

The only thing is Sales Returns with FIFO. If you have any idea, kindly let me know.

Thanks :)

Regards

[SamT]

The coding, using UserForms is pretty simple.

UF Purchases has a ComboBox for Products, 3 TextBoxes for Qty, Cost and Date, (Date is auto-filled, but can be manually changed,) CommandButtons for Cancel or Clear, Save, and Exit or Done.

The Initialize Sub looks in the Product Name Rows on Inventory and Purchases and fills two Scripting.Dictionaries with Product Names and Column numbers. It then loads the Keys from the Purchases Dictionary into the ComboBox. This process allows for the changing of products without changing any code. It also means that the columns in Purchases and Inventory do not have to be in the same order.

The Save CommandButton Reads each Dictionary (Items) using the ProductName ComboBox.Value as the Dictionary.Key to find the appropriate column to save to each sheet, (Purchases or Inventory.) In Purchases, it saves the Date, Qty, and Cost to the next available Row, and in Inventory, the Qty and Cost to the On Order Row.

UF Receivables looks identical to UF Purchases and the Initialize sub is the same except that it only uses an Inventory Dictionary. You should add a "Back Order" Row to Inventory under the "On Order" Row and a new Database sheet and Dictionary, "As Received" for a receivables daily Journal which records Date, Qty and Cost for each invoice.

The Save CommandButton reacts the same as on the Purchases UF except that it records the actual QTY and Cost of the received items in the next available cells at the bottom of the Inventory columns and compares Qty_Rcved to Qty_On_Order and modifies Qty_Back_Ordered and Qty_In_Stock as needed.

UF Sales is very similar to the other two, except that it uses Inventory and Sales Dictionaries and the Save button reads from the top of the Inventory lists and deletes any Qty & Cost Cells where Qty = 0.

Other
You will need a common "Add_Product" sub to handle new product columns on each DB sheet when Purchases gets a new line of products.

If you have many products in several categories, you can use a different DB sheet for each category, and use a new Combo box to select the category DB before loading the Dictionaries. There are other alternatives, but this is probably easiest to code for, but it means more manual work if a new category is added.

I would use a "Global_Constants" Module with at least a
Public Enum Row_Numbers
purProductName = 1 'Purchasing is a Daily Journal
invProductName = 1 'Inventory. With multiple Categories, all Inventory sheets should be formatted identically.
invInStock = 2
invOnOrder = 3
invBackOrder = 4
invListTop = 5 'Top of Items List
ijrnlProductName = 1 'Inventory Daily Journal
salProductName = 1 'Sales
sjrnlProductName = 1 'Sales Daily Journal
End EnumYou can use these constants anywhere in your code and if you ever modify your databases, you only have to change these row numbers.

Project Design.
Structure Precedes Function. When designing your DB Sheets, think about the entire business process, from Purchasing to Pricing and proceeding all the way to EOY accounting. Insure that you can find each piece of information needed on some database sheet. Think about the process frontwards, backwards, upside down and inside out. It is so much faster to get it right the first time than to have to rewrite all the code a second time.

Get your DB sheets done as best you can and only then start work on the UserForms. Share your workbook with us and we will do our best to make sure your DBs are complete and we will get your code to work.

[ShawnMichael]

Hi SamT !

Thank you for replying. Yes, I would do all this, after gathering full information and planning the design (including customer posting groups, relevant pricing and discount groups) and share the workbooks here.

For the time being, this simple UDF or Array formula is required for sales return to do exact-cost-reversing...in other words, receive the product back in inventory at the cost it went out. I have added a helper column. If you can help with Array formula, that would be so nice of you.

Here is the table:



Sell Date
Product Name
Qty Sold
FIFO COGS
Avg Tranch Out Cost


09/03/2014
Prod 1
94
1423.00
15.14


20/03/2014
Prod 1
116
1799.00
15.51


29/03/2014
Prod 1
62
1004.00
16.19


29/03/2014
Prod 1
-272
-4226.00





Now I have tried SumProduct, Offset, and round many times, but couldn't come with the right formula. I tried it in Aggregate function but hard luck.

What I am looking for is, if the negative quantity is entered in Cell C4 (current value is -272), the formula in D5 (current value is -4226), it should do reverse sumproduct of Qty Sold and Avg Tranch Out Cost column matching the product.

If we enter -272, D5 should return -4226. How ?

( 62 x 16.19 ) + (116 x 15.51) + (94 x 15.14) = -4226

If we enter -150, it should return:

(62 x 16.19) + (88 x 15.51) = -2369 (rounded) based on product name in column B. So effectively, we are reversing FIFO cost.

I hope I have explained it correctly.

Thank you for your help.

Note: The Avg Tranche Out Cost is a helper column only.


Side Note: I am looking for answers from multiple sources. You see now since you discussed this with me, news ideas about design are popping in mind, for example, adding business intelligence and metrics and come out with a nice sleek spreadsheet design. I will do all and share everything, but the thing is, I have get formulas right.

[SamT]

Are you asking what to do when someone returns 272 of product1? (Row 5 of that table)

Obviously, I don't understand the situation you are describing.

Can you tell me exactly what you mean by "Tranche?" The only definition I can find describes it in terms of securities, risks and liens.

If you are dealing in Securities, rather than Material goods, and you must-to return the properties exactly at your cost to inventory, things might should be considered differently.

In either case, you must have a unique ID such as an invoice number, sales slip number, or Transaction number to return based on original cost.

Consider: you make three sales of a product to different clients and client # 2 wants to return only a portion of the purchase, however client # 1 only bought some of the product in Row #2 and client #2 bought all the remaining of Row # 2 and all of the product in Row # 3, + some of Row # 4. He is returning only enough to replace Row # 2 and some of Row # 3.

Since you don't put your cost on a sales Invoice, you should generate an Internal Transaction slip that shows the Client, Sales Date, and actual costs and Purchase Dates of each product by Qty and should probably also show the actual Sales price of each Qty.

Finally: About the formula or UDF you are requesting:

Don't look at the formula you are trying to create as numbers. Look instead at the labels that return those numbers.

( 62 x 16.19 ) + (116 x 15.51) + (94 x 15.14) = -4226 should be

Result = (Row(09/02/2014).Qty_Sold * Row(09/02/2014).Ave_Tranche_Out_Cost) _
+ (Row(29/03/2014)Qty_Sold * Row(29/03/2014)Ave_Tranche_Out_Cost) _
+ (Row(29/04/2014)Qty_Sold * Row(29/04/2014)Ave_Tranche_Out_Cost) Then it becomes obvious that you must look at the original Sales Slip to retrieve the Transaction Slip to retrieve the Purchase Dates of the items sold. To do that, the Sales Slip and the matching Transaction Slip must have the same (or a cross referenced,) Identification number.

To accomplish this from Excel, without User Forms, you must enter the Returns information into a dedicated Returns Table and run a Macro that will find all the required Data points (the values from those Labels in the formula example above,) and place the correct values into the correct places. A simple UDF won't work because it can only return from a non volatile table into a single cell.

[ShawnMichael]

Thank you for your reply Sam.

Yes, that's a neat idea...to match a returns with sales by way of sales invoice number or another unique number. Given, we have sales invoice number in sales table, and then we have have returns, we enter those returns in sales returns table and cross reference that invoice number, what formula will we use ?

I couldn't figure it out, have been trying for two days almost.