Master planning

Dynamics AX master planning (MP) covers the short-term calculation of material and capacity requirements. Matching supply and demand to get net requirements of a product.

Similar to forecast scheduling batch job, here we run a master scheduling batch job that creates net requirements for an item and corresponding planned orders.

Static and Dynamic master plans

On a higher level, you can also devise a strategy of having two kinds of master plans, static and dynamic.

Master planning parameters (Master planning> Setup> Parameters) has two options for current static master plan and current dynamic master plan. Master scheduling batch job usually runs every night calculating net requirements for all items. The result of that calculation is available in the static master plan. The static plan is the master plan to be used in purchase and production management for scheduling orders. It is the default plan when accessing the planned orders forms. On the other hand, the sales department needs a plan to run simulations in order to check possible delivery dates in sales orders. Therefore, sales requires running a master scheduling simulation locally, which is for the individual item only. The result of that simulation is available in the dynamic master plan.

Three options:

1. For a basic setup, you need only static plan setup. We don't specify the dynamic plan.

2. Or use the same plan for both static and dynamic. If you enter the same master plan for the static and the dynamic master plan in the master planning parameters, you run a one master plan strategy. Planned orders of current master scheduling simulations in sales update the static plan, which is used in purchasing and production. Depending on your company's requirements, this could be useful.

3. Last option would be to use separate plans for both. In order to apply a two master plan strategy, you need to enter a different master plan for the static and the dynamic plan. When running the batch job for static master scheduling in the night, it usually copies the static plan into the dynamic plan in order to base simulations on the current static plan. In this case, simulations in sales will start applying a common data basis with purchase and production management in the morning. Simulation throughout the day do not change planned orders in the static plan, thereby avoiding problems in purchasing and production caused by planned orders changing every moment.

Important note MP uses table InventSumLogTTS to assist in its calculations. Problem is this table can grow big quickly. MS and LCS diagnostics recommend to keep this table clean. MP batch job can do this cleaning for you but it requires that your dynamic plan is set. Even if you dont use dynamic plans, it is better if you set it same as your static plan. This way each run of MP job, will delete unwanted records from this table.
See class ReqCalcScheduleItem\InsertItemSched

Master plan

First thing required is setting up master plans (Master planning > Setup > Plans > Master plans). A master plan covers multiple scenarios you want handled in your master planning.

Ticking the 'Include on-hand inventory' and 'Include inv. Transactions' boxes will ensure that any stock on hand and open purchase orders will be included in planned order calculations.

The ‘Include demand forecast’ must also be ticked to ensure that the forecast entered against each item is included in the calculations.

Reduction Principle could be set to None.

Time fences, safety times, action and futures messages will not be set at the plan level. These will be set at the Coverage group level as the Master plan settings override the coverage group.

Futures messages fast tab - used to specify if requirement date on planned order should automatically update to calculated futures date

Action messages fast tab- used to specify if requirement date on planned order should automatically update to action date

Safety margin - add 2,3 days for buffer. Cushion to help you for delays. Its going to add up on top of Coverage group safety margin. Be careful.

Master plan parameters

General tab - Define your static and dynamic plans

Planned orders tab - Find trade agreements. This is to find the vendor for an item when the primary vendor is not specified on an item. Two criteria - minimum lead time or lowest price.

Standard update - Grouping planned orders into individual purchase orders when firming them. By vendor, buyer group etc. I have done customisations to add group by warehouse (InventDimId), maybe in another post.

Time fences

What is time fences? No of days master planning should look out from today to find transaction. It can be applied on Master plan or Coverage group. As said above, Master plan will override Coverage group.

For eg, if Coverage time fence is 100 days, it will look in future 100 days for all receipts and demand to do its calculations.

Coverage is longest, firming is shortest.

Explosion - usually same as coverage. Used in production. It’s the no of days in which if it finds a planned production order it will be exploded into its net requirements. You need 10 of item a, 5 of item b etc

Capacity - used to schedule work orders, resources, resource groups.

Action, Futures - calculation on no of days requirements where you will have action and futures messages.

Freeze - no of days you are not allowed to create new planned orders

Firming - automatically firm an order after a no of days

Calendar setup

Calendars in AX are used to basically set what days I can order products and can be set at different levels Warehouse, Vendor and Item Coverage Group.

The standard logic of master planning is that Item Coverage Group overrides the Vendor which overrides the warehouse (store).

Based on this we can use this logic to set up basic calendars against the warehouse (store) showing that they can order every week day.

Then if required we can set a different calendar against the Vendor if we only order products from this Vendor on different days.

The last option is to set a calendar against individual coverage group which we can then set against an item or group of items.

Using this logic should hopefully limit the number of calendars that we need to configure and should also limit the number of coverage groups that we will need to create.

Example of a warehouse and its calendar property.

Warehouse has a default calendar with working times set; which days they can order. Assumption is for most stores this will be Monday-Friday. 

Working times

Note: if you run master scheduling and calendar days are not open, no Net requirements/Planned orders will be created against the items.

Coverage groups

Coverage groups are the main tool that master planning will use on when and how we want to order products. Essentially it allows us to configure time periods (Daily, Weekly) for how frequently we want to order individual products. The coverage group is set against an individual product (Released products > Plan > Item coverage). There is also a default property 'General coverage group' in parameters form.

There are 4 coverage codes we can define.

Requirement - this group is used to order a product only when we have demand for the product for example we have a script for the drug this will be used for expensive drug items or drug items that we don’t want to stock. Each uncovered demand drives a planned supply. Usually for high value items, you don’t want to buy too much and keep in stock

Period - you can define a coverage period eg 7 days, 30 days. How many days you want to look out from the first piece of demand and group that many days of orders together. For eg an order created on 1st for next 7 days of demand. Good thing is we are grouping 7 days of demand in one order, bad is we have 7 days of on-hand sitting on us (inventory holding costs). It is based on 1st requirement date not calendar date. 7 days does not mean it creates an order every week. It means when it finds demand it creates an order for next 7 days.

One example of a setup would be to define 3 period type coverage groups.

Daily – this group as the name suggests is products that we can order daily, this coverage group is also usually combined with a minimum stock on hand value for the product and this will basically mean we will order the product based on daily usage and the minimum stock level.

Weekly – this group as the name suggests is for products that are ordered on a weekly basis again this is also usually combined with a minimum stock on hand that can then alert the user if usage is higher than normal and the product may need to be ordered in advance.

Monthly – this group is to cover products that are ordered on a monthly basis and also will normally be combined with minimum stock level that can then alert the user if usage is higher than normal and the product may need to be ordered in advance.

Min/Max - controls the size of supply.  AX will order so much to bring you to the max level. Once inventory value falls below min, an order big enough to bring value up to the max is generated.

Manual - Net requirements are not calculated. 

Eg, a daily coverage group

Positive and Negative days (Coverage groups)

Negative days - Imagine we have a sales order for 10 items on 1st and we don’t have stock.

If negative days is 0, AX will say you can't wait for the next purchase order so creates a new planned order.

Negative days is like a window from a sales order(demand) of days when receipts in those days will satisfy this demand.

But if negative days is 20 and an existing purchase order coming in on 17th, master planning pegs that purchase order against the sales order. And does not create any new planned order. No of orders in system are reduced. The purchase order will get an action message created telling that the PO is going to be late and sales order is going to get a futures message saying that the SO is also going to be 17 days late because it has a PO pegged against it. Time to contact your vendor to speed things up!

If negative days did not cover this demand on 1st, was less than 17, a new planned order would be created.

Positive days - Opposite direction. How far back master planning will look for inventory to satisfy this demand (sales order). For ex, Sales order on 31st, positive days 15 days, will go back 15 days to find any receipt. Does not find any so creates a planned order.

Note: for master planning, on hand inventory is a receipt. People set their positive days to a large number like 999 so that on-hand inventory is always seen.

Negative days works on lead times for an item. If the lead time for an item is not covered by a PO, a planned order is created. Now different items might have different lead times. There is a property called Dynamic negative days.

Dynamic negative days

Dyn neg days = LT + Neg days

This gives more flexibility and reduces no of coverage groups as LT is based on the item you are planning.

Action and Future messages (Master plan)

Negative/Positive days are closely tied with action and future messages. An example to see action and future messages in action. One prerequisite is that master plan should have both ticked and set to appropriate no of days. If say Action message is not ticked, you will not see any Action message arrow on the purchase order.

For example, Item '000000014' with a sales order for 10 qty on 1/8/2016 and a purchase order for 10 qty on 17/8/2016. The coverage group used 'MAN' has negative days set as 20. Item has no stock.

Before running master scheduling:

After running master scheduling, the sales order gets a future message and purchase order gets an action message:

Action tab is basically saying that the purchase order should be advanced by 16 days to 1/8/2016 instead of current 17/8/2016:

Futures tab is basically saying that the sales order should be delayed by 16 days to 17/8/2016 instead of current 1/8/2016:

Master scheduling batch job

Finally we run the Master scheduling batch job (Master planning > Periodic > Master scheduling) to calculate the net requirements of finished items.

We can check the results at Released products > Plan tab > Net requirements. There are two planned orders of type master plan created.

Note: As a result of these orders, and just like forecasting, there are no transactions (InventTrans) created against the item.

Approved planned orders

Intermediate step between unprocessed planned order and a firmed purchase order.

A button on top to Approve, changes status to "Approved", means we reviewed the planned order and will firm it soon but not right away. Need some time to check something else. What approving does is, it excludes these orders from getting deleted or regenerated as master planning runs every night. If regeneration happens, your items might get different quantities etc which can be confusing.

You could also Change status to "Completed", which means we reviewed the order, but we dont want to firm it and can be deleted by the master planning batch.

Fulfil minimum

Released product > Plan > Item coverage > General > Fulfil minimum

Fulfil minimum defines when your safety stock requirement date is.

This has been a long post and maybe left more questions unanswered than answered. No doubt master planning subject is not covered much and can use few more posts. Maybe in future posts. 

Demand forecasting

In the next two posts, we will cover demand forecasting and master planning at a basic level.

Forecasting is a long-term estimation, required to estimate and adjust future capacities of item supply and work centers.

Forecast models

First thing needed in setting up forecasting is a forecast model (Inventory management > Setup > Forecast > Forecast models). Forecast models represent different planning scenarios.

Forecast plans

Forecast models refer to forecast plans (Master planning > Setup > Plans > Forecast plans).

You specify the forecast model on a forecast plan. A Forecast plan is the basis for calculating forecasts in forecast scheduling. You may include forecast plans in master scheduling as well. It includes time fences and safety margin. More on time fences in next post.

You may enter the current forecast plan in the master planning parameters (Master planning> Setup> Parameters). The current forecast plan is the default for displaying forecast scheduling results.

Forecasting is based on items but you can also use item allocation key, if you do not enter forecasts per item number. Item allocation keys are groups, containing a percentage for every item included in the key.

You may specify item allocation keys in the form Inventory management> Setup> Forecast> Item allocation keys. After selecting or inserting an item allocation key, you may push the button Lines to enter the item numbers that are assigned to the key together with their percentage of the group total.

Demand forecast

Next you enter Demand forecast. Go to Released products > Plan tab > Demand forecast.

One option is to manually insert lines. Select a forecast model, select the site and warehouse, date, sales quantity and sales price.

Another option could be to use demand forecasting calculated using the new functionality in AX R3. With the R3 release of 2012, a new feature was added that allows your to have the system create your statistical forecasts based on the history in the system and then allow you to adjust the forecasts through Excel before automatically uploading them back into the demand forecast tables.

https://dynamicsaxtipoftheday.com/2014/06/15/configuring-demand-forecasting/

Yet another option could be to write custom logic to estimate forecasts from item's sales history. For example, daily sales data could be obtained by dividing sales qty sold by the no of days that item is sold. Daily sales figure can be multiplied by the no of days in the period we will order, say 1 week. Sales data can be obtained from Inventory transactions (InventTrans or SalesLines). And a batch job to run overnight and fill up your demand forecast table (ForecastSales). This needs some thinking.

In our example, we will insert few lines manually. Create two lines with date separated by a week 1/8/2016 and 8/8/2016, quantities 10 and 6.

Forecast scheduling batch job

Next you run Forecast scheduling batch job (Master planning > Periodic > Forecast scheduling) to calculate the gross requirements of finished items. As a result of forecast scheduling, Dynamics AX creates planned purchase/production orders covering the gross requirements of forecasting. These planned orders refer to the selected forecast plan.

Note: Since forecast scheduling refers to a long-term estimation, it does not include the current inventory as well as current sales and purchase orders in the calculation.

We can check the results at Released products > Plan tab > Gross requirements.

Select the forecast plan you used on top filter. Top grid shows the warehouse and the Coverage group used. (More on coverage groups in the next post on Master planning)

Lower grid shows the lines from demand forecast and a planned order with required quantity against those quantities. If you 'View details' on the planned order number, will open the planned order created, with the demand forecast lines pegged against it.

Note: As a result of these orders, there are no transactions (InventTrans) created against the item. Also its not mandatory to run forecast scheduling batch job. You can directly run master scheduling batch job that uses your demand forecast to create planned orders. Difference is the orders would be shown under a different plan (filter in planned orders list page).

Next post we will cover the basics of master planning.

AX retail pull jobs not uploading

What to do when a pull job is not uploading transactions. For ex, sales transactions from POS to AX head office.

First thing to check is event viewer for any errors. If you don’t see any errors, you can try below method.

A bit of background first.

AX retail push jobs use SQL change tracking to push data from HO to store. But pull jobs use a different way, a replication counter.  Let’s see how it works for sales transactions.

In below example, RetailPOS is the store database.

Table [RetailPOS].[ax].[RETAILTRANSACTIONSALESTRANS] has a field called [REPLICATIONCOUNTERFROMORIGIN] that is an auto-incremented number, every new transactions gets a new number.

There is another table [RetailPOS].[crt].[TABLEREPLICATIONLOG] that stores a list of all tables that need to upload data. This table has a field FILTERMAX, which is a reference to the above field RETAILTRANSACTIONSALESTRANS.REPLICATIONCOUNTERFROMORIGIN

Field FILTERMAX is always up-to-date to indicate the last transaction uploaded.

That means any new transactions created in store DB will get a value in RETAILTRANSACTIONSALESTRANS.REPLICATIONCOUNTERFROMORIGIN greater than the one currently set in FILTERMAX. Every time the pull job runs, it checks REPLICATIONCOUNTERFROMORIGIN in all tables against the FILTERMAX value for those tables and sees if there are any new transactions that need to be uploaded, using the Async client service and sets the FILTERMAX values for the tables to the last uploaded value.

So one thing you could do is to force the store database into thinking that the records haven’t been uploaded to Async Server. If table [RetailPOS].[crt].[TABLEREPLICATIONLOG] was cleared out it would "reset" the store database and it would re-upload all records into a new RPF file.  This should be OK since any duplicate transactions would be ignored by AX.

If you just want to resend one specific table data, you could filter for that table's records using TableName, say ax.RetailTransactionSalesTrans and set FILTERMIN and FILTERMAX to 0.
If you run the P-job now, Async Client will package this table's entire history in a RPF file for upload.
This could be helpful if you are not sure which records are missing from HO.

But if you want to reset all of the store tables, follow these steps:

1. Stop the AsyncClient in the StoreDB machine

2. Make a backup of the store Database

3. Delete the records in the TABLEREPLICATIONLOG table.

4. Start the Async Client

5. Run the P-job

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [ID]

      ,[UPLOADSESSIONID]

      ,[TABLENAME]

      ,[FILTERFIELDNAME]

      ,[FILTERMIN]

      ,[FILTERMAX]

  FROM [RetailPOS].[crt].[TABLEREPLICATIONLOG]

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [BARCODE]

      ,[ITEMCORRECTEDLINE]

      ,[ITEMID]

      ,[PERIODICDISCGROUP]

      ,[PERIODICDISCTYPE]

      ,[PERIODICPERCENTAGEDISCOUNT]

      ,[PRESCRIPTIONID]

      ,[PRICE]

      ,[PRICECHANGE]

      ,[PURCHID]

      ,[QTY]

      ,[REPLICATIONCOUNTERFROMORIGIN]

      ,[STORE]

      ,[TAXAMOUNT]

      ,[TAXGROUP]

      ,[TRANSACTIONCODE]

      ,[TRANSACTIONID]

      ,[CREATEDDATETIME]

      ,[MODIFIEDDATETIME]

      ,[CREATEDTRANSACTIONID]

      ,[MODIFIEDTRANSACTIONID]

      ,[DATAAREAID]

      ,[ROWVERSION]

  FROM [RetailPOS].[ax].[RETAILTRANSACTIONSALESTRANS]

Inventory valuation methods

Different inventory costing/valuation methods in AX especially how sales cost is calculated and adjusted after an inventory close in different methods.  

Inventory Model Groups

(Inventory management> Setup> Inventory> Inventory model groups)

Inventory model groups include settings for the valuation method and for item handling. They are important for inventory valuation and ledger integration.

Selecting an inventory model (FIFO, LIFO, average or standard cost) on the tab Inventory model, you specify the inventory valuation method, which is the way Dynamics AX links issue transactions to receipt transactions in terms of valuation.

If you change the Inventory model or Ledger integration settings on the tab Setup of an inventory model group after posting item transactions, reconciliation of inventory and finance may become very difficult.

Inventory Valuation

The basis of inventory valuation is a simple principle:

Receipt costs - cost amounts are provided by the receipt transaction

Issue costs - cost amounts are calculated according to the valuation model

The cost amount of the issue transaction derives from the receipt transactions, which refer to the issue according to the valuation model (FIFO, LIFO, average). It is not possible to enter the cost price and cost amount in an issue transaction, therefore.

Exceptions to this rule are moving average valuation (for issue transactions keeping the average cost price shown at the time of posting) and the standard cost price valuation, for which Dynamics AX provides two different options:

– Fixed receipt price (This is a checkbox on the  Item model group form)

– Standard cost (This is seen in the Inventory model dropdown)

The option “Fixed receipt price” is available/enabled for valuation methods FIFO, LIFO and weighted average cost. Applying this option fixes the receipt cost price in advance, preventing to change it when recording a transaction.

“Standard cost” provides true standard costs, applying the standard cost price of an item for all issue and receipt transaction. The difference between the methods “Standard cost” and “Fixed receipt price” shows, when changing the standard cost price of an item. Whereas the standard cost method immediately posts an adjustment of inventory value, the fixed receipt price method does not directly post a difference. In this method, the new price only applies to new receipts – existing inventory will issue to the old price until it is consumed completely. As a prerequisite for applying the standard cost method, the multisite functionality needs to be active in the company account concerned.

Valuation Method

Dynamics AX provides following valuation methods – available in the field Inventory model of the inventory model group – to calculate the cost amount of issue transactions:

–      FIFO

–      LIFO

–      LIFO date

–      Weighted average

–      Weighted average date

–      Standard cost

–      Moving average (I have used this for AX Retail)

Valuation of Item receipts

Receipt transactions get their financial value when you post the related financial transaction (invoice). Except for the standard cost method, the different transaction types therefore provide the receipt cost price and amount as follows:

– Purchase order receipts

Amount of the invoice line

– Sales item return

Original value of the returned item; return cost price entered in the return/sales order line, if not assigned to an original sales order

– Other receipts

Cost amount entered in the journal line

Valuation of Item issues

The cost price and amount of item issues always complies with the average cost price when posting the transaction. The valuation method applies when closing the inventory determining the assignment of item receipts to item issues according to the inventory model (FIFO, LIFO or average).

Inventory closing calculates the cost price and amount of an issue transaction based on assigned receipts. The issue price and amount therefore is not final until you have posted the financial transaction (invoice) of all assigned receipts and inventory closing is finished.

As an exception, individual assignment of issue transactions to receipt transactions does not apply to following valuation methods:

-       Standard cost price – This method (standard cost or fixed price) immediately applies to issue and receipt transactions.

-       Moving average – Keeps the posted cost price of issue transactions and does not require inventory closing.

Standard cost price

For items of the inventory model “Standard cost”, inventory closing is not required because all receipts and issues immediately post the standard cost price available in the item price form.

When activating a new standard cost price, Dynamics AX immediately applies an adjustment of inventory value for the current stock, which posts in inventory and in the general ledger. The new standard cost price therefore may apply to issues of that stock right away.

Fixed receipt price

The checkbox “Fixed receipt price” in the inventory model group applies in combination with the valuation methods FIFO, LIFO or average cost. When selecting this option, the cost price entered in the released products form or the item price form specifies a fixed cost price for receipt transactions.

The cost price and cost amount calculates according to the valuation method and therefore always complies with the standard cost price of the item, as long as you do not change the item cost price.

When you change the item cost price, consumption of existing stock in inventory will comply with the old cost price calculating the cost amount of issue transactions according to the valuation method. Therefore, inventory closing is also required for the option “Fixed receipt price”.

Moving average price

For items of the inventory model “Moving average”, receipt transactions are posted with the price provided by the transaction (vendor invoice). When posting an issue transaction, the average cost price at the time of posting applies. This cost price does not change through inventory closing.

If a vendor invoice refers to a purchase receipt, which is not completely on stock any more, for the quantity not on stock, a difference between the physical cost amount and the financial cost amount of the purchase transaction is posted as an adjustment to a price difference account. For the quantity still on stock, posting the purchase invoice posts the total amount – including possible differences – to the financial cost amount.

Following table shows an overview of the different valuation methods available in Dynamics AX:

Inventory models controlling the valuation methods in Dynamics AX

·         FIFO (First In First Out) - Item issues refer to the oldest item receipt still on stock

·         LIFO (Last In First Out) - Item issues refer to the newest item receipt on stock that is available when closing inventory

·         LIFO date - Like LIFO, limiting the assignment of issues to receipts before the particular issue

·         Standard cost - The cost price of item issues and receipts is equal to the active standard cost price of the item

·         Weighted average - The cost price of item issues in a period is the average cost price of all receipts (including the beginning balance) in this period (inventory closing period)

·         Weighted average date – The cost price of item issues is the average cost price calculated separately for each day.

·         Moving average - The cost price of item issues is the average cost price of the inventory quantity at the time of posting the issue.

Below example shows cost price calculation for the different valuation methods. Three receipt transactions with different cost prices and an issue transaction in between.

Posted transactions for comparing valuation methods

Date                 Transaction                  Quantity                       Cost amount

July 1               Receipt                         10                                 100

July 2               Receipt                         10                                 200

July 3               Issue                            10                                 (to be calculated)

July 4               Receipt                         10                                 300

After inventory closing, the cost amount of the issue shows following figure depending on the valuation method:

Valuation of the item issue

Model                                       Amount                        Explanation

FIFO                                         100                                From receipt on July 1

LIFO                                          300                               From receipt on July 4

LIFO date                                  200                               From receipt on July 2

Weighted average                     200                               Average of all receipts

Weighted average date             150                               From receipt on July 1 and July 2

Moving average                        150                               Current average when posting the issue

Inventory Closing and Adjustment

When you post an issue transaction, Dynamics AX always applies the average cost price (except for the standard cost model). In order to calculate the correct cost price and amount according to the valuation method (Inventory model) of the item, you need to close inventory. Only items assigned to inventory model “Standard cost”, or “Moving average” do not apply inventory closing.

You need to close inventory periodically – usually as part of the month closing procedure in finance – in order to show correct item costs in finance and to close inventory transactions. After closing inventory, it is not possible to post inventory transactions in the closed period any more.

Need for inventory closing

The main task of an inventory closing is to calculate the actual cost price of an inventory issue. At the time of inventory issue posting, system does not calculate an exact issue cost price according to the inventory model. Instead, system uses some estimated value for the issue cost price, which looks like a good approximation for the actual cost price. Then, during the inventory closing, the actual issue cost price is calculated with respect to inventory model settings. After that, system calculates the difference between the initial (estimated) issue cost price and the true issue cost price, then system writes this down into inventory transactions and inventory adjustments. Then GL postings for these adjustments are created. Why this approach has been chosen? In general, during posting of an initial inventory issue, system might not have all information required to calculate the true cost price. For example, if negative inventory is turned on, we can post issues before corresponding receipts; In the Weighted Average model, it is impossible to calculate an issue cost price before all the receipts for a period has been posted; For FIFO/LIFO models, the receipts might be posted out of chronological order and so on. 

Changing an inventory model

As long as there are no open transactions for an item, we can change its model group. Changing model group to Standard cost however, is not straightforward and requires a conversion tool to be run.

Inventory recalculation

Recalculates the inventory as of a given date without closing the inventory. This process matches inventory receipts and issues for the transactions that are not financially matched through inventory dimensions in order to determine the real value of the on-hand inventory. Unlike inventory close, it does not settle issues or close transactions. Inventory recalculation can be performed on a subset of inventory items whereas Inventory close must be performed on all inventory items. It is not as accurate as an inventory close and should not be relied on to replace inventory close.

This process is batchable, with options to select items. There is no from date option only an upto date and option to select period. But as the volume of data grows it’s going to get slow. 

Past inventory report

If you want to know the physical inventory on a date in the past, you may select a report in the menu path Inventory management> Reports> Status> Physical inventory.

Examples
In this case, purchase and sales data is imported via external files and processed date wise. Effects on Financial cost amounts, on hand inventory and cost price is noted.

1.       Initial purchase file imported;

a.       19/12/2014 Purchase for a product; Qty = 7; total purchase $ = 17.50 ($2.50 each)

b.      On hand inventory updated = 7; Financial cost amount = $17.50; Cost Price = 2.50

2.       Next purchase file imported:

a.       29/12/2014 Purchase for a product; Qty = 5; total purchase $ = 11.50 ($2.30 each)

b.      On hand inventory updated = 12; Financial cost amount = $29; Cost Price = 2.42

3.       Sales File imported with sale qty of 1 both for 20/12/2014 and 30/12/2014.

a.       On hand inventory updated = 10; Financial cost amount & Cost Price in different cases as below

Moving average

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.42	2.42	26.58	11	2.42
Sale	1	30/01	2.42	2.42	24.16	10	2.42

FIFO

Before recalculation

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.42	2.42	26.58	11	2.42
Sale	1	30/01	2.42	2.42	24.16	10	2.42

After recalculation

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.50	2.50	26.50	11	2.40
Sale	1	30/01	2.50	2.50	24.00	10	2.40

Weighted average by date

Before recalculation

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.42	2.42	26.58	11	2.42
Sale	1	30/01	2.42	2.42	24.16	10	2.42

After recalculation

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.50	2.50	26.50	11	2.41
Sale	1	30/01	2.42	2.42	24.08	10	2.41