Fifo Inventory Valuation

  • Hi All,


    After searching all over the net for weeks and coming up dry for a working macro that would perform fifo inventory valuation, I turn to the gurus here that have helped in the past. For me, and I would imagine others, this is the holy grail of problems if it can be solved.


    I have a workbook with a series of #'d tabs corresponding to different pieces of inventory tracked independently (I've simplified it significantly and attached it). I would like to use the First-in First-out inventory valuation method to calculate the current cost of inventory. A manual calculation and description of the method and the proper result is included in the sample file.


    I have come across numerous references to http://www.cpa911.com/read_article.asp?ID=46 here, via google, other messageboards, etc; however, when I have tried implementing it, even as described, it never seems to work! (Note: I've included the code and set it up per their instructions in the attached file...of course, unless I misinterpreted their instructions and my problem lies there...) Moreover, I'm not sure how I would implement this particular macro anyways using named regions considering the same macro would have to be run across several sheets. As a result, the named ranges would always pull data from only the one sheet containing the named range required by the macro . This begs the question of whether there a way to create unique named ranges for each sheet that would be interpreted correctly by a single macro running across multiple sheets...


    If this is at all unclear, I would be more than happy to rephrase any/all parts. After all, this problem has been aggravating me for weeks, and I am at the mercy of this forum's gifted coders who may not be well versed in accounting theory :). Even tips of where else to look for more information on getting this to work would be great!


    Any and all help is much appreciated.


    Frustrated,


    Brian

  • Re: Fifo Inventory Valuation


    Roy,


    I like your template, good work.


    In the instructions (both in the workbook and on the Excel-it site), you refer to a sheet named Stock. The example has three sheets named (Explanation, FIFO, and Lists). The FIFO sheet does what is described as being on the sheet named Stock. Just thought you'd like to know.


    Regards,

  • Re: Fifo Inventory Valuation


    Hi Brian,


    I played with the example code you posted but no luck yet, it simply doesn't work. But the good news is here's a working model of your sheet.


    Notes:


    I set the Column numbers as variables in the Subs. If you want to change the Columns around you must change the numbers in the Workbook Sheet_Change Macro and in the Recalc macro to account for the changes. They are clearly marked "CHANGE TO SUIT"


    The Sheet Change Macro uses 3 Column numbers: "Units Received" and "Costs of Goods Received" and "Shipped"


    The Recalc Macro uses 4 Column numbers: the three above plus "FIFO Valuation"


    The Subs are basically automatic - require no named ranges, and work on ALL sheets. The only thing they require is an entry in the first row of data (currently row 3) in the Received Column.


    Errors are to be expected, I suppose. Let me know!


    Edit:


    Presumes either an Rec'd value OR a Shipped value on each row. I didn't code for both as that's not how the example was layed out.

  • Re: Fifo Inventory Valuation


    Hello Again!


    Given the way I will be implementing this, rbrhodes' suggestion seems to work "near" perfectly! So far I haven't come across any errors, but I have come across two possible points of improvement:


    1) Currently in my implementation of your macro, if you change the price received you have to reinput the quantity received in order for the macro to recalculate, unlike the sample you provided. Besides changing the row # and column #'s in both the Workbook and module, I haven't touched your code. Any thoughts?


    2) It also seems that if I try to insert/delete a row or column on a sheet, excel will semi-freeze. By semi-freeze, I mean that I cannot switch been tabs, access any of the menus, or enter any data; however, I can close excel via the system tray, and it will not only ask me if I would like to save, but it actually does save (both outcomes are unlike what I would expect from a truly non-responsive program). Any suggestions? It seems as though the problem stops if I toggle between these:


    Code
    'Allows FIFO macro to operate (just a caller for the macro already created).
    Sub Enabler()
        Call ThisWorkbook.a
    End Sub
    
    
    'Allows insertion/deletion
    Sub Disabler()
        Application.EnableEvents = False
    End Sub
  • Re: Fifo Inventory Valuation


    Hi bryz,


    Re Changing price. I missed one reference in the Workbook Module. By which I mean I presumed start row of 3. There are two lines to add and one to change.


    First Dim's a variable ("InRow") for the first line of data


    Second sets the value of the variable (change to match your first row of data)


    Third (change line) takes out the reference to row 3 as the first line of data and uses the variable instead.


    See below:


  • Re: Fifo Inventory Valuation


    Well, I've been playing hard trying to break it but I can't get to freeze or get any errors at all.


    Perhaps post a small example of your latest version?


    My only note is that the Columns are defined in 2 places and perhaps I should change them to Public and have them defined in one place only so ensure they always match.


    The WB Sub uses 3 cols and Recalc uses those 3 as well. There is no reason why they should cause a crash if not defined correctly but who knows? I'll try that now!


    Edit: Nothing I've tried will get any event to fire. I'll wait for to post your version.


    PS: This will toggle Events On/Off


    Code
    Sub EventsOnOff()
        'Resets to opposite (if True then False, if False then True)
        Application.EnableEvents = Not Application.EnableEvents
    'optional
        MsgBox (Application.EnableEvents)
    End Sub
  • Re: Fifo Inventory Valuation


    Thanks for your comments above. I made your suggested changes but have not experienced a change to correct my "freezing" issue or the issue with price. Attached is a sample of the worksheet I am implementing this on, including other macros that run on the sheet. I have checked that both issues continue to exist despite culling down a much larger workbook to the sample.

  • Re: Fifo Inventory Valuation


    Hi bryz,


    My mistake (presumption again...) I had the Rec'd/Cost/Shipped Columns in a certain order so the Sheet Change Sub was looking for them (as a range) in that order. What that means is neither the first version or the corrections I posted would work on your version. I didn't allow for the Columns to be in any other order.


    This Change Sub looks at each Column instead of presuming they are in a certain order, so as long as the Recd, Costs and Shipped Column numbers are set correctly (as variables) it will find them wherever they are in any order. Much better.


    As for the 'freezing' I manged to get the sheet to freeze by erroring the Change sub. All I could do was click to close Excel. But instead of saving I clicked Cancel in the dialog and I was back in the sheet and operational again. So, what I did was standardize the enable/disable Events and enable/disable Screenupdating in every macro (turning them both off at the beginning and both back on at the end of each and every macro). This is a slight bit of overkill but better safe and all. It _appears_ to have fixed the problem. Let me know.


    Some other issues are noted as comments in the attached example. Mainly that the data validation doesn't work on cell changes caused by formulas so I put a check in the Change sub for negative inventory. Also the Adjust column wasn't allowing negative numbers and the data validation message read "Can be positive _OF_ negative".


    I think that's it for this round...


    Edit:


    Copy all of the subs to (a copy) of your real sheet and test them out again if you will.

  • Re: Fifo Inventory Valuation


    ALMOST there! Seems that everything is now working well with exception to one item:


    Other than the macros you have tweaked, a couple other unrelated macros in the workbook have lost their responsiveness to Application.ScreenUpdating = False. I figured I would try to emulate what you did and do the following to each of them:



    Any thoughts? I added this for to all other macros in the workbook. Moreover, I have since found that if I change the closing ".ScreenUpdating = True" to "= False" on the other macros, Screenupdating is once again removed; however, the issue with inserting/deleting rows/columns returns.

  • Re: Fifo Inventory Valuation


    Hi,


    Not quite sure I understand what you're saying. If a macro turns off the screen update it should turn it back on when done (some versions of Excel do it when the sub exits, some versions don't).


    EnableEvents simply allows Change events (etc) to run or not. So if you are running a macro that will change anything on the sheet and you have Event code then the Macro should probably disable Events until it is finished then Enable them again when done. (There are exceptions to every rule including this one!)


    Inserting/Deleting rows and or Columns should not be affected either way!


    I guess is that if a macro turns off either or both and then calls another macro the second one shouldn't have to deal with the ones that the calling macro has already done.


    The only solution I can think of is get the workbook working correctly. Then put a break in every Event sub and then run each macro to see which ones are firing event subs or other subs and which aren't. Then run each sub and after letting each sub finish see if the Row/column problem has returned. This should lead to where/how the problem is being caused?

  • Re: Fifo Inventory Valuation


    Better way



  • Re: Fifo Inventory Valuation


    Hi,


    Just got back from a vacation - have to refresh my memory, but I believe the issue regarding the "freezing" is still around. Will reply once I'm back up to speed.


    Brian

  • Re: Fifo Inventory Valuation


    Hi Roy,

    I am not sure whether you are still at Excel It, but I just bought your template for FIFO and it shows error on List tab!!!! Please Help!!!! Thx

  • Re: Fifo Inventory Valuation


    Hello pie131,


    Welcome to Ozgrid.


    Please do not make replies in other threads unless you are providing a solution.
    There is contact link on Roy's web site. Use that link to obtain the support you need.
    This forum is for asking specific Excel/VBA questions.


    You may start a new thread, if desired, and ask for help on a specific topic, explaining clearly what your needs are. Threads are limited to a single topic with closely related follow up questions permitted.


    When creating threads, please note:


    Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, your title use as search terms would return relevant results.



    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different



    Do not make any further replies in this thread.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!