I am trying to keep track of my inventory which has 30+ different models, and I want to use my sales database to deduct from the inventory automatically or at least somewhat easily. Considering i have thousands of purchases and 30+ different models, it doesn’t seem to make good sense for either model or purchase to be the column property.
Even if i use the different models as columns (assuming i do not mind the 30+ columns), the sales purchases are row entries. How do i relate the sales entry and the inventory deduction with something like an invoice number?
I would like to keep a running tab on my available inventory for all models at all times. Even if its only at the summary area at the botton of the table, that’s fine. Does anyone have any idea of how to do that easily and well?
All the materials management implementations that I have worked with had a central materials transaction table, with fields like Material (Model?) nr, Description, Movement Type, qty and unit of measure as a base.
If you want to add financial information to your system, you can go one of two ways.
You could create an additional table for financial information, where you store your financial transactions, eg sales invoices, goods receipts and purchase invoices. Because you have two different tables, you would most likely need to create update transactions using a button that will update both the logistics and the financial tables. Bear in mind that the Coda database (to my knowledge) is not ACID compliant. Further, you would need to implement a check to ensure that the quantities on the two tables are and remain in sync with each other. Including managing the rolling qty balances in both tables for each material.
The alternative would be to create additional columns on your materials transactions table to store the financial information as well. In this method you have only one table to update, which simplifies things quite a bit.
What you do not mention, is whether you need to calculate a value per material. This adds quite a lot of complexity, depending on how accurate you want to be.
You would then build views on the tables to look at sales transactions, purchase transactions and goods movements.
While I prefer the second approach, both approaches will give you the answer(s) that you need. Of course, this is a very generic example, that will be heavily influenced by what you currently have, as well as what your detailed requirements are.
Hi, I work in Chemical Sales and have set up a Demand Planning system. The team input into the Demand Plan in KGS of Finished Product, then we generate Financial Forecasts using the USD/KG selling price and Raw Material Purchases by running the KGS of Finished Product required through a formulation table.
We have a Customer Master List which has all the relevant Customer information and a Chemical Master List which has all the relevant chemical information including Pack Size (Tanker or Drum etc).
When Demand Planning we just do a Look Up to the Customer and the Item Masters. It stops the Demand Plan forecast becoming massive and unwieldy.
And I should say the Demand Plan (with Forecasted information) allows us to understand the minimum stock level (1 month) for all Finished Products (around 400) and Raw Materials (around 800). Conditional Formatting just goes red when something is below one month of the Forecasted Plan. The sales lines per month is around 1,500 Item and Customer combinations. There are some duplicates when a Customer buys the same Item twice in a month, but I run a consolidation on Customer By Item By Period which takes the 1,500 sales lines to less than 500 for the month.