Edit

Cost management Power BI content

  • Article

Overview

The Cost management Microsoft Power BI content is intended for inventory accountants or individuals in the organization who are responsible for or interested in the status of inventory or work in progress (WIP), or who are responsible for or interested in analyzing standard cost variances.

This Power BI content provides a categorized format that helps you monitor the performance of inventories and visualize how cost flows through them. You can gain managerial insights such as the turnover ratio, number of days that inventory is on hand, accuracy, and "ABC classification" at your preferred aggregated level (company, item, item group, or site). The information that is made available can also be used as a detailed supplement to the financial statement.

The Power BI content is built on the CostObjectStatementCacheMonthly aggregated measurement, which has the CostObjectStatementCache table as its primary data source. This table is managed by the Data set cache framework. By default, the table is updated every 24 hours, but you can change the update frequency or enable manual updates in the configuration of the data set cache. Manual updates can be run in either the Cost administration workspace or the Cost analysis workspace.

After every update of the CostObjectStatementCache table, the CostObjectStatementCacheMonthly aggregated measurement must updated before data in the Power BI visualizations is updated.

Accessing the Power BI content

The Cost management Power BI content is shown in the Cost administration and Cost analysis workspaces.

The Cost administration workspace contains the following tabs:

  • Overview – This tab shows application data.
  • Inventory accounting status – This tab shows Power BI content.
  • Manufacturing accounting status – This tab shows Power BI content.

The Cost analysis workspace contains the following tabs:

  • Overview – This tab shows application data.
  • Inventory accounting analysis – This tab shows Power BI content.
  • Manufacturing accounting analysis – This tab shows Power BI content.
  • Std. cost variance analysis – This tab shows Power BI content.

Report pages that are included in the Power BI content

The Cost management Power BI content includes a set of report pages that consist of a set of metrics. These metrics are visualized as charts, tiles, and tables.

The following tables provide an overview of the visualizations in the Cost management Power BI content.

Inventory accounting status

Report page Visualization
Inventory overview Beginning balance
Net change
Net change %
Ending balance
Inventory accuracy
Inventory turnover ratio
Days inventory on-hand
Active product in period
Active cost objects in period
Balance by item group
Balance by site
Statement by category
Net change by quarter
Inventory overview by site and item group Inventory accuracy by site
Inventory turnover ratio by site
Inventory ending balance by site
Inventory accuracy by item group
Inventory turnover ratio by item group
Inventory ending balance by site and item group
Inventory statement Inventory statement
Inventory statement by site Inventory statement by site
Inventory statement by product hierarchy Inventory statement
Inventory statement by product hierarchy Inventory statement by site

Manufacturing accounting status

Report page Visualization
WIP overview YTD Beginning balance
Net change
Net change %
Ending balance
WIP turnover ratio
Days WIP on-hand
Active cost object in period
Net change by resource group
Balance by site
Statement by category
Net change by quarter
WIP statement Beginning balance
Ending balance
WIP statement by category
WIP statement by site Beginning balance
Ending balance
WIP statement by category and site
WIP statement by hierarchy Beginning balance
Ending balance
WIP statement by category hierarchy

Inventory accounting analysis

Report page Visualization
Inventory details Top 10 resources by ending balance
Top 10 resources by net change increase
Top 10 resources by net change decrease
Top 10 resources by inventory turnover ratio
Resources by low inventory turnover ratio and ending balance above threshold
Top 10 resources by low accuracy
ABC classification Inventory ending balance
Consumed material
Sold (COGS)
Inventory trends Inventory ending balance
Inventory net change
Inventory turnover ratio
Inventory accuracy

Manufacturing accounting analysis

Report page Visualization
WIP trends WIP ending balance
WIP net change
WIP turnover ratio

Std. cost variance analysis

Report page Visualization
Purchase price variance (Std. cost) YTD Procured balance
Purchase price variance
Purchase price variance ratio
Variance by item group
Variance by site
Purchase price by quarter
Purchase price by quarter and Item group
Top 10 resources by unfavorable purchase price ratio
Top 10 resources by favorable purchase price ratio
Production variance (Std. cost) YTD Manufactured cost
Production variance
Production variance ratio
Variance by item group
Variance by site
Production variance by quarter
Production variance by quarter and variance type
Top 10 resources by unfavorable production variance
Top 10 resources by favorable production variance

Understanding the data model and entities

Data from the application is used to fill the report pages in the Cost management Power BI content. This data is represented as aggregate measurements that are staged in the entity store, which is a Microsoft SQL Server database that is optimized for analytics. For more information, see Power BI integration with Entity store.

The key aggregate measurements of the following objects are used as the basis of the Power BI content.

Object Key aggregate measurements Data source for finance and operations Field
CostObjectStatementCacheMonthly Amount CostObjectStatementCache Amount
CostObjectStatementCacheMonthly Quantity CostObjectStatementCache Qty
CostInventoryAccountingKPIGoal AnnualInventoryTurn CostInventoryAccountingKPIGoal AnnualInventoryTurn
CostInventoryAccountingKPIGoal InventoryAccuracy CostInventoryAccountingKPIGoal InventoryAccuracy

The following table shows the key calculated measurements in the Power BI content.

Measure Calculation
Beginning balance Beginning balance = [Ending balance]-[Net change]
Beginning balance qty. Beginning balance qty. = [Ending balance qty.]-[Net change qty.]
Ending balance Ending balance = (CALCULATE(SUM([Amount]), FILTER(ALL(FiscalCalendar) ,FiscalCalendar[MONTHSTARTDATE] <= MAX(FiscalCalendar[MONTHSTARTDATE]))))
Ending balance qty. Ending balance qty. = CALCULATE(SUM([QTY]), FILTER(ALL(FiscalCalendar),FiscalCalendar[MONTHSTARTDATE] <= MAX(FiscalCalendar[MONTHSTARTDATE])))
Net change Net change = SUM([AMOUNT])
Net change qty. Net change qty. = SUM([QTY])
Inventory turnover ratio by amount Inventory turnover ratio by amount = if(OR([Inventory average balance] <= 0, [Inventory sold or consumed issues] >= 0), 0, ABS([Inventory sold or consumed issues])/[Inventory average balance])
Inventory average balance Inventory average balance = (([Ending balance] + [Beginning balance]) / 2)
Days inventory on-hand Days inventory onhand = 365 / CostObjectStatementEntries[Inventory turnover ratio by amount]
Inventory accuracy Inventory accuracy by amount = IF([Ending balance] <= 0, IF(OR([Inventory counted amount] <> 0, [Ending balance] < 0), 0, 1), MAX(0, ([Ending balance] - ABS([Inventory counted amount]))/[Ending balance]))

The following key dimensions are used as filters to slice the aggregate measurements, so that you can achieve greater granularity and gain deeper analytical insights.

Entity Examples of attributes
Products Product number, Product name, Unit, Item groups
Category hierarchies (Assigned to role Cost management) Category hierarchy, Category level
Legal entities Legal entity names
Fiscal calendars Fiscal calendar, Year, Quarter, Period, Month
Site ID, Name, Address, State, Country/Region