+ Reply to Thread
Results 1 to 1 of 1

Inventory Table

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    Lima, Peru
    MS-Off Ver
    Excel 2013
    Posts
    4

    Inventory Table

    Dear All,

    Right now I´m modeling my business and i thought it was a good idea to use the power pivot functions to help me. Everything is working great except for one very essential table in my model. namely the inventories.

    the invertory table has 11 columns with the following information
    row identifier: A unique number for every row
    Period code : a number identifiing the period (for instance 201302 for feb 2013)
    Previous Period: a number identifiing the period before (for instance 201301 for jan 2013)
    Product: a uniqe code for every product
    Start inventory: This should be the end inventory amount of the previous period
    Start Value: This should be the end inventory value of the previous period
    Production amount: Imported from another table
    Production Value: Imported from another table
    Sales Amount: Imported from another table
    Sales Value: Start inventory value + Production Value - Ending inveroty Value
    End inventory amount: Start inventory amount + Production amount - Sales amount
    End inventory Value: (Starting value+Production Value)/(Start amount+ Production amount)* End inventory amount.

    The main issue I face is getting the starting values in every period as the ending value is a function of the starting value excel keeps complaining that it is an circular reference (even though it is not because it is filtered to a different period) the function I use is the following

    =CALCULATE(SUM([Ending_Inventory]),
    all(FAC_INV_Finished_Product),
    FAC_INV_Finished_Product[Period]=earlier(FAC_INV_Finished_Product[PreviousPeriod]),
    FAC_INV_Finished_Product[Product]=EARLIER(FAC_INV_Finished_Product[Product])
    )


    In "normal excel" it possible to do this with sumifs without excel complaining about circular references

    does anybody know how to solve this issue?

    Sorry I posted this in the wrong forum. Can any of the mods please delete this post?
    Last edited by oliviermulder; 08-15-2013 at 09:36 AM. Reason: Sorry I posted this in the wrong forum. Can any of the mods please delete this post?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Inventory Table - Prevent Duplicate Entry
    By StevenChen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2013, 01:35 PM
  2. [SOLVED] Need remaining average inventory price after partial inventory is sold
    By Akano in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 04:12 AM
  3. Inventory Age Formula Needed for FIFO Inventory
    By SWeisser in forum Excel General
    Replies: 0
    Last Post: 07-20-2012, 02:44 PM
  4. Inventory Stock Balancing with Inventory Data
    By bingbangboom in forum Excel General
    Replies: 2
    Last Post: 01-30-2011, 11:06 PM
  5. [SOLVED] Pivot Table for Inventory Database
    By ridgerunner in forum Excel General
    Replies: 2
    Last Post: 03-22-2005, 11:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1