Analyze or update costs in advanced mode

Various factors can cause inventory costs to change. With the Maintain inventory valuation (MIV) feature, you can detect any issues that might exist for your inventory valuation, current unit costs, and cost of goods sold (COGS) and update these costs.

You can update your inventory costing in advanced mode on the Advanced tab of the Maintain inventory valuation page.

To view a list of the inventory costing updates that have been run, go to Inventory Control > All > More > Maintain inventory valuation. From this list, you can run a costing analysis and update or view the results of a previously completed costing analysis and update.

Before you begin

If your organization was already live on Sage Intacct before implementing the Inventory Control application, ensure the Inventory start date field in the Configure Inventory Control page is set.

The value in this field is used as the default As of date. The As of date is the date that Sage Intacct starts processing transactions to recalculate inventory costs. Processing only the transactions created after the start of Inventory Control makes the recalculated costs more accurate.

About advanced mode

When you run in advanced mode, you select to run in one of these maintenance options:

• Analyze costs only. The costs are recalculated, but not updated. You review the results in CSV files that highlight where the recalculated costs differ from the originally recorded costs. Analyzing without updating can give you insight into the scope of your costing issues and the impact that updating costs will have.
• Analyze and update recalculated costs. The costs are recalculated and updated in the same run. You control how the costing is updated in the following places.
  • The inventory subledger and internal cost tables. The costs are always updated in open periods, but you can also select to have them updated in closed periods, too.
  • COGS entries in the GL. You control whether the COGS entries are updated in open periods.

When you run with either option, the results are written to CSV files. All the transactions from either the Inventory start date on the Configure Inventory Control page or the As of date on the Maintain inventory valuation page, whichever date is latest, are processed. Thus, thousands of transactions could potentially be evaluated.

Analyze inventory costs only

When you analyze inventory costs, the costs are recalculated based on transaction-date order. The results that are written to CSV files highlight where the recalculated costs differ from the originally recorded costs.

Use this information to determine the impact of updating the costs and whether you need to make any adjustments before running the process to update the costs.

1. Go to Inventory Control > All > More and select Add (circle) next to Maintain inventory valuation. The Maintain inventory valuation page appears.
2. Select the Advanced tab.
3. Enter the information about the costs that you want to analyze. At a minimum, specify:
   • Name. Defaults to the current date and time. The name, which you can override, identifies the recalculation so you can find and download the CSV files that are generated.
   • The items and cost methods to include. By default, all cost methods are selected, which is recommended.
   • Analyze costs only.
   For the most comprehensive cost recalculations, use these options:

   • As of date. Use a date that goes back to when your organization first started using Sage Intacct or the Inventory Control application, whichever date is later. This way all relevant transactions are resequenced and included in the cost recalculations, making the recalculated costs more accurate.
   • Update costs in inventory subledger and internal cost tables: In open periods only.

   To see these options, select Analyze and update recalculated costs. Then, select Analyze costs only again.

   Costs are recalculated for transactions in closed periods only if you select In open and closed periods.

   See the Field descriptions for details about each field.

4. Select Run now at the top of the page.

   A job to recalculate the costs based on transaction-date order and to generate the CSV files with the results is run. The name of the page changes to Inventory Valuation Maintenance Results.

5. Select the Advanced tab and scroll down to the Summary of results section to see how many costing errors were found (the summary of the results also appears on the page with the list of maintenance runs). For more details download and review the CSV files.
6. Determine if you need to make any adjustments, such as changing misconfigured transaction definitions, before you run the update process.
   To get more diagnostic details in the COGS report, select All transactions and run the analyze again. All the transactions, including those without costing issues, are listed in the report, grouped by incoming purchases and outgoing sales. Use this information to assess what's contributing to costing differences, including issues with your order entry, purchasing, and inventory transaction definitions.

   If you need help with assessing issues and making adjustments before moving on to updating the costs, remember you can always contact your implementation partner or Sage Intacct Customer Support.

Update inventory costs

When you update inventory costs, the costs are recalculated based on transaction-date order and updated where they differ from the originally recorded costs. The results are written to the same CSV files that are generated when you analyze costs. Use the information to verify that the costs are resolved the way you expected.

1. Go to Inventory Control > All > More and select Add (circle) next to Maintain inventory valuation. The Maintain inventory valuation page appears.
2. Select the Advanced tab.
3. Enter information about the costs that you want to update. At a minimum, specify:
   • Name. Defaults to the current date and time. The name, which you can override, identifies the update so you can find and download the CSV files that are generated.
   • The items and cost methods to include. Defaults to all cost methods being selected, which is recommended.
   • Analyze and update recalculated costs.
     For the most comprehensive and complete costing updates, use these options:

     • As of date. Use a date that goes back to when your organization first started using Sage Intacct or the Inventory Control application, whichever date is later. This way all relevant transactions are resequenced and included in the cost recalculations, making the recalculated costs more accurate.
     • Update costs in inventory subledger and internal cost tables. Select In open periods only. The As of date is overridden to be the first day of the oldest open period unless the current As of date is later. Costing will be updated only for transactions in open periods.
       You can recalculate and update costs for transactions that are in both open and closed periods, by selecting In open and closed periods. The subledger and internal cost tables will be updated for transactions in both open and closed periods. However, because the GL cannot be updated in closed periods, the inventory subledger may not balance to the GL at a previous point in time for historical reporting.
     • Update GL with recalculated COGS in open periods. Select to have the GL updated. When costing changes in purchases where both the purchase and the sales of any of the purchased goods are in open periods, then both the inventory subledger and the COGS GL entries for the transactions are updated. This leaves the subledger and GL better aligned.

   See the Field descriptions for details about each field.

4. Select Run now at the top of the page.

   A job to recalculate the costs based on transaction-date order and to generate the CSV files with the results is run. While the internal cost tracking tables, the inventory subledger, and COGS journal entries in the general ledger (based on the options you specify) are updated, none of the underlying transactions are changed.

   The name of the page changes to Inventory Valuation Maintenance Results.

5. Select the Advanced tab and find the Summary of results section to see how many costing errors were found and fixed. (The summary of the results also appears on the page with the list of maintenance runs.) For more details, you can Download and review the CSV files.
6. Reconcile the inventory subledger and GL.

   • If you updated the inventory subledger in open and closed periods and the GL in open periods, the inventory subledger and GL might not balance.

     For example, if you entered actual landed costs for purchased goods that have been sold in a period that's now closed, the COGS GL entries for the sales transactions couldn't be updated, leaving a variance between the subledger and GL.

     Make it a practice to use the COGS adjustments for prior periods feature to quickly reconcile the GL with the inventory subledger. The feature identifies the adjustments that are needed to the GL, which you can make with a click of a button. A good time to review and post system-recommended adjustments is just before you close a fiscal period after you've updated your inventory costing with the recommended options. See Best practices.
   • We recalculate costs only for transactions that affect Quantity & Value or Value and can affect any transaction that posts COGS to the GL. So, if you’ve made any manual journal entries to adjust your Inventory Asset account or made other changes to your inventory, you might need to reverse or edit them after updating the costs to make the inventory subledger and GL balance.

Review the recalculated cost results

CSV files contain the detailed results of both analyzing and updating inventory costs. The files highlight not only where the recalculated costs differ from the originally recorded costs, but also where other unexpected results exist.

1. Go to Inventory Control >All> More > Maintain inventory valuation.
   
2. On the list of inventory valuation runs, find the run date you want to view.

3. Select More actions > View at the end of the row.

4. On the Inventory valuation maintenance results page, select the Advanced tab.
5. In the Maintenance results sections, select Download to download the CSV files.
6. Open and review the CSV files.

1. Go to Inventory Control > All > More > Maintain inventory valuation.
   
2. Select View next to the maintenance run you want to display.
3. On the Inventory valuation maintenance results page, select the Advanced tab.
4. In the Maintenance results sections, select Download to download the CSV files.
5. Open and review the CSV files.

Learn more about the contents of the CSV files.

Best practices

Use the following recommended practices for running inventory valuation maintenance:

• First-time use. When you run maintain inventory valuation for the first time, you'll generally want to analyze and then update all of your historical inventory costing. A good time to do this is when you're ready to close a period after all the transactions have been entered.
• Closed periods. Make sure that your books are closed for any periods that you don’t want the GL updated.
• Recommended options. For the most complete and comprehensive costing updates, use the following settings for these options:
  • As of date. Use a date that goes back to when your organization first started using Sage Intacct or the Inventory Control application, whichever date is later. This way all relevant transactions are resequenced and included in the cost recalculations, making the recalculated costs more accurate.
  • Update costs in inventory subledger and internal cost tables. Select In open periods only. The As of date is overridden to be the first day of the oldest open period unless the current As of date is later. Costing will be updated only for transactions in open periods.
    You can recalculate and update costs for transactions that are in both open and closed periods, by selecting In open and closed periods. The subledger and internal cost tables will be updated for transactions in both open and closed periods. However, because the GL cannot be updated in closed periods, the inventory subledger may not balance to the GL at a previous point in time for historical reporting.
  • Update GL with recalculated COGS in open periods. Select to have the GL updated. When costing changes in purchases where both the purchase and the sales of any of the purchased goods are in open periods, then both the inventory subledger and the COGS GL entries for the transactions are updated. This leaves the subledger and GL better aligned.

  With these options, Sage Intacct updates the subledger, and, if the sale of an item is in an open period, also updates the COGS entry in the GL. If the sale of an item is in a closed period, the GL COGS entry can’t be updated, and a variance is left between the inventory subledger and GL.

• Ongoing use. After you've manually brought your historical data up to date and are satisfied with your existing costs and workflow, update costs regularly. Make updating costs a regular part of your normal operations by scheduling them to be automatically updated on a daily basis.
  To make updating costs a regular part of your normal operations, select the Automatically run updates nightly checkbox in the Configure Inventory Control page. Setting this option is the preferred way to keep your inventory costing updated instead of manually scheduling maintain inventory valuation to run nightly.
• Prior period COGS adjustments. When you update costing with the recommended options, variances left between the subledger and GL are not uncommon for companies that routinely close their books. Use prior period COGS adjustments to quickly adjust the GL to the inventory subledger. Learn more about COGS adjustments for prior periods.

Field descriptions

• Maintain inventory valuation page - Advanced tab
• CSV files

Maintain inventory valuation page - Advanced tab

The following table describes each field on the Advanced tab in the Maintain inventory valuation page. 

Advanced tab field descriptions

Field	Description
Name	The name for the maintenance run (or schedule). The default is the current date and time. The name helps you find and download the generated CSV files that contain the results of analyzing or updating the costs (or find a schedule).
Description	A text field for any other details you want to give about the maintenance run.
Email address	The email address to send notification of when the process to analyze or update the costs has completed and the CSV files with the results are available. If you’re setting up maintain inventory valuation to run on a schedule, an email address is needed

Filters section

Filters section field descriptions

Field	Description
Item group	Only include items in a specific item group.
From item, To item	Only include a specific item or range of items.
Warehouse	Only include items in a specific warehouse.
Cost method	The cost method of the items to include: Average cost: Uses the weighted average of all units for sale to determine the value of COGS and ending inventory. Standard cost: Uses the planned or expected costs. This method is used in situations where purchase costs remain fairly stable over time. FIFO (first-in, first-out): Assumes the first item in to your inventory is sold first. LIFO (first-in, first-out): Assumes the last item in to your inventory is sold first.

Maintenance options section

Maintenance options section field descriptions

Field	Description
Maintenance type: Analyze costs only and Analyze costs and update recalculated costs	Best practice: Make sure that your books are closed for any periods that you do not want the GL updated. Specify what type of run: Analyze costs only: Recalculates the inventory costs for transactions that occurred on or after the As of date. Identifies where the recalculated costs differ from the originally recorded costs, and generates reports with the results, without updating any costs. Analyze costs and update recalculated costs: Recalculates inventory costs for transactions that occurred on or after the As of date. Identifies where the recalculated costs differ from the originally recorded costs, updates the costs where the recalculated cost differs from the originally recorded cost, and generates reports with the results. Costs are updated in the inventory subledger, the internal cost tables, and COGS entries in the GL according to how the Update costs in inventory subledger and internal cost tables and Update GL with recalculated COGS in open periods options are set. The Update costs in inventory subledger and internal cost tables option affects the As of date that's used. If In open periods only is selected, which is the default, the As of date is the date that's entered or the beginning date of the oldest open period, whichever is later. This means that only the transactions that are in the currently open periods are processed.
COGS report option: Costing error transactions and All transactions	Specifies which transactions to include in the COGS report: Costing error transactions only: Include only the transactions with costing errors. All transactions: Include all the transactions, grouped by incoming purchases and outgoing sales. You can use the purchasing transactions that led up to the sales transactions where the recalculated COGS differs from the original COGS to diagnose the costing errors. Understanding how the costs have been set helps you evaluate potential issues with the workflow. Intacct might not be able to save a CSV file that has a large number of rows (around 2 million transactions).
As of	This field is applicable to both Analyze costs only and Analyze and update recalculated costs. Sage Intacct recalculates and updates costs for transactions that occurred on or after the As of date. (Costs are updated only if the update recalculated costs option is selected.)  The date defaults to the beginning date of the oldest open period. If In open periods only is selected and the As of date is in a closed period, Sage Intacct uses the beginning date of the oldest open period as the As of date. This means that only the transactions that are in the current open periods are processed. In open periods only is the default. Do not see this field? If you are analyzing costs only, select Analyze and update recalculated costs. Then, select Analyze costs only.
Update costs in inventory subledger and internal cost tables: In open periods only and In open and closed periods	This field is applicable to both Analyze costs only and Analyze and update recalculated costs. In open periods only: Only the transactions that are in an open period are processed. If the As of date is in a closed period, Sage Intacct uses the beginning date of the oldest open period as the As of date. This is the default. In open and closed periods: Transactions in both open and closed periods are processed. To include transactions in closed periods, ensure the As of date is set appropriately. Best practice: Use In open periods only. Do not see this field? If you are analyzing costs only, select Analyze and update recalculated costs. Then, select Analyze costs only.
Update GL with recalculated COGS in open periods (occurring after As of date)	This field is applicable to Analyze and update recalculated costs only. Specifies whether to update the GL COGS entries in the GL with the recalculated COGS. The GL can only be updated in open periods. The default is not to update the GL. Best practice: Have the GL updated. When costing changes in purchases where both the purchase and the sales of any of the purchased goods are in open periods, then both the inventory subledger and the COGS GL entries for the transactions are updated. This leaves the subledger and GL better aligned.

Schedule section

Appears only in the Inventory valuation maintenance results page.

Schedule section field descriptions

Field	Description
Run maintenance on a schedule	Run inventory valuation maintenance on the specified schedule with the specified filters and options.
Repeats and Every	The frequency at which the schedule occurs. Select a time period from Repeats (Daily, Weekly, Monthly, Yearly) and then enter the interval for the selected time period in the Every field. Example: To update your costs every day, select Daily for Repeats and enter 1 for Every.
Repeat by day of week	For repeats Weekly, the day of the week the schedule will run: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday Example: To update your costs every other week on Friday: Select Weekly from Repeats. Enter 2 in the Every field. Select Friday from Repeat by date of week.
Repeat by	For repeats Monthly, the day of the month the schedule will run: Day of the month: Use the day of the month from the Start date field. For example, if the date in the field is on the 15th day of the month (06/15/20xx), the schedule occurs on the 15th day of the month. Day of the week; Use the day and week of the month from the Start date field. For example, if the start date is on Friday of the 3rd week of the month (06/16/20xx), the schedule occurs on Friday of the 3rd week of the month. End of the month: Use the last day of the month. Example: To update your costs at the end of each quarter: Select Monthly from Repeats. Enter 3 in the Every field. Select an appropriate Start date (like a date in January). Select End of the Month from Repeat by.
Start date	When to start the schedule. The Start date drives not only the start of the schedule but also the repeat by entries for Daily, Monthly, and Yearly.
Ends	When to stop the schedule: Never. The schedule runs perpetually. After _ occurrences. Enter the number of times the schedule should run. On. Enter or use the calendar tool to specify a date for when to stop the schedule.

Maintenance results section

Appears only in the Inventory valuation maintenance results page.

Maintenance results section field descriptions

Field	Description
Inventory activity	Link to download the Valuation Activity report.
Cost of goods sold	Link to download the Cost of Goods Sold (COGS) in Transactions report.

Summary results section

Appears only in the Inventory valuation maintenance results page.

Summary results section field descriptions

Field	Description
Cost errors	Number of times costing differences were found.
Negative inventory warnings	Number of times inventory went negative.

Audit details section

Appears only in the Inventory valuation maintenance results page.

Audit details section field descriptions

Field	Description
When created	The date and time Sage Intacct completed the process to analyze or update costs. (State=Analyzed or Updated)
Created by	The user ID of the user who ran the process to analyze or update costs.

CSV files

The following tables describe the columns in each of the CSV files:

• Inventory activity
• Cost of goods sold (COGS) in transactions

Inventory activity

The following table describes the contents of the Valuation Data report: 

Column heading	Description
Item	The ID of the inventory item.
Warehouse	The ID of the warehouse for the inventory item.
Txn_Date	The effective date of the cost that's used in the Valuation Report for the As of Date for the report. The date reflects when a transaction affected the cost of the item.
Unit_Cost	The existing unit cost of the item, based on transactions being processed in the order they were entered.
Recalc_Cost	The recalculated unit cost of the item, based on transactions being processed in transaction-date order. When transactions have the same date, the accumulated receipts are processed before the accumulated sales.
COGS_Qty	Similar to Recalc_Qty, except that only transactions with the following inventory effects are processed: Quantity & Value, Value A difference between Recalc_Qty and COGS_Qty indicates possible configuration issues with your transaction definitions.
Recalc_Qty	The recalculated quantity on hand for the item, based on transactions being processed in transaction-date order. When transactions have the same date, the accumulated receipts are processed before the accumulated sales. Only transactions with the following inventory effects are processed: Quantity & Value, Quantity A difference between Recalc_Qty and COGS_Qty indicates possible configuration issues with your transaction definitions.
Qty_Purchased	The quantity purchased in all transactions on the transaction date.
Qty_Sold	The quantity sold in all transactions on the transaction date.
State	The recalculated unit cost as compared to the existing cost (Recalc_Cost versus Unit_Cost): Same. The costs are the same. No updates are needed. Different. The recalculated cost is different. The Inventory Valuation report will not be accurate until the cost is updated. Fixed. The recalculated cost was different, and the internal history of the valuation costs was updated with the recalculated cost. Neg Qty on MM/DD/YYYY. The last cost of an item is used when its inventory is negative. Will be deleted. The record will be deleted when an update is run because the transactions that came after were deleted or their transaction dates were changed.
Full_Value	The total cost of the quantity for the item, as calculated by multiplying Recalc_Cost by Recalc_Qty.
Value_for_COGS	The total cost of the quantity for the item, as calculated by multiplying Recalc_Cost by COGS_Qty.
QOH	The existing quantity on hand for the item, based on transactions being processed in the order they were entered.
Txns_Found	Whether transactions were found for the transaction date: Yes. Transactions were found. No txns since MM/DD/YYYY. The last date that transactions were found for the item. Transactions had existed for the date but have either been deleted or their transaction dates have been changed.
Existing_Rec	Whether a record for the transactions for the transaction date exist: Yes. A record exists. No. A record does not exist. When the costs are updated, Intacct creates a record and includes it in the updated cost calculations.
Method	Cost method for the item: Average cost Standard cost FIFO LIFO

Cost of goods sold (COGS) in transactions

The following table describes the contents of the COGS Data report: 

Column heading	Description
Txn_date	The date of the transaction.
Document	The transaction that changed the COGS. When Show all transactions is selected, all of the transactions are listed and not just those with costing issues. With all the transactions listed, grouped by incoming purchases and outgoing sales, you can use the purchasing transactions that led up to the sales transactions where the recalculated COGS differs from the original COGS to diagnose the costing errors. Understanding how the unit cost has been set helps you assess potential issues with the workflow.
Item	The ID of the inventory item.
Warehouse	The ID of the warehouse for the inventory item.
Qty_on_Txn	The quantity of the item for the transaction.
COGS_Qty_on_Txn	The quantity of the item for the transaction that affected the COGS. This value can be different from Qty_on_Txn because the calculation omits transactions that have a quantity only effect on inventory.
QOH	The quantity of the item on hand.
COGS_QOH	The quantity of the item on hand that affects the COGS. This value can be different from QOH because the calculation ignores transactions that have a quantity only effect on inventory.
Cost_EA_Posted	The cost of each item, based on transactions being processed in the order they were entered.
Cost_EA_Recalculated	The recalculated cost of each item, based on transactions being processed in transaction-date order.
COGS_Posted	The COGS that was posted to the GL for the transaction, based on transactions being processed in the order they were entered.
COGS_Recalculated	The recalculated COGS, based on transactions being processed in the order they were entered.
Cost_Method	The cost method for the item: Average cost Standard cost FIFO LIFO
State	The recalculated COGS as compared to the existing COGS (COGS_Recalculated versus COGS_Posted) and the COGS_QOH as compared to the QOH: Different. The recalculated COGS or COGS_QOH is different. The inventory value and COGS in your financial statements will not be accurate until the COGS is updated. Fixed. The recalculated COGS or COGS QOH was different, and Intacct changed the subledger and updated the COGS journal entries with the recalculated COGS. Neg Qty on MM/DD/YYYY. The last cost of an item is used when its inventory is negative.
State	The recalculated COGS as compared to the existing COGS (COGS_Recalculated versus COGS_Posted) and the COGS_QOH as compared to the QOH: Different. The recalculated COGS or COGS_QOH is different. The inventory value and COGS in your financial statements will not be accurate until the COGS is updated. Fixed. The recalculated COGS or COGS QOH was different, and Intacct changed the subledger and updated the COGS journal entries with the recalculated COGS. Neg Qty on MM/DD/YYYY. The last cost of an item is used when its inventory is negative.
Transaction	The transaction definition, which you can use to determine whether the transactions affect COGS. Transaction name. The name of the transaction Upd Inv (Update Inventory). Q for quantity only; V for value only; T for quantity and value SPI (Sales/Purchase/Internal). INC/DEC (Incoming versus Outgoing transactions). I for increase in quantity; D for decrease in quantity Upd GL (Update GL). A for AP or AR; G for GL; No for no update
Notes	Additional information about the transaction.
COGS_rounding_error	The rounding error in the GL for any fractional numbers.
COGS_change	The change to be made to the GL to fix the difference for the transaction.
COGS_change_no_apportioned	The change to be made to the GL to fix the difference for the transaction without the apportioned subtotal amount included.
Price_change	For purchases, the change in price to improve accuracy (typically for greater precision for currency conversions).
Apportioned_ea	The amount of the apportioned subtotals for each item.
Current_GL_amount	The current COGS in the GL.