In the previous article, I described the steps to build a PowerPivot data model to analyze Microsoft Dynamics GP Sales data. In this article, I will explain the steps I used to build a data model that can be used for financial analysis using General Ledger data in Dynamics GP. (See the related article Upgrade PowerPivot Data Model to SSAS Tabular).
The following screenshot shows the final PowerPivot data model in the diagram view.
The first steps in building this data model is to retrieve opening and closing balances and net change for each account and period in General Ledger. The query to retrieve this data can be found in the article SQL to Create GL Account Balances Table for Dynamics GP. The other tables include Accounts, Calendar and Period. The Calendar and Period tables are explained in the previous article. The Accounts table uses the Accounts view in Dynamics GP database.
Once we have the above tables created, we can create the calculated fields required for financial analysis. First, I created the base fields used in calculating various financial ratios. The following table shows the base fields, DAX formulas and some additional information.
Note that the DAX formulas are specific to the sample company (TWO) Chart of Accounts and Account Categories. You may have to modify these formulas to work with your company’s Chart of Accounts and Account Categories.
Calculated Fields
Calculated Field
|
DAX Formula
|
Additional information
|
ClosingBalance
|
CALCULATE(SUM([Ending Balance]), LASTDATE(Calendar[Date]))
|
Closing Balance for the last date based on selected dates
|
Activity
|
SUM([Net Change])
|
Total Net Change based on selected dates
|
ActivityYTD
|
[Activity](DATESYTD(Calendar[Date]))
|
Total YTD Net Change based on selected dates
|
ActivityQTD
|
[Activity](DATESQTD(Calendar[Date]))
|
Total QTD Net Change based on selected dates
|
ActivityPrevYearQTD
|
[Activity](DATEADD(DATESQTD(Calendar[Date]),-1,YEAR))
|
Total QTD Net Change last year based on selected dates
|
ActivityPrevYearYTD
|
[Activity](DATEADD(DATESYTD(Calendar[Date]),-1,YEAR))
|
Total YTD Net Change last year based on selected dates
|
ActivityPrevYear
|
[Activity](DATEADD(Calendar[Date],-1,YEAR))
|
Total Net Change last year based on selected dates
|
ActivityMTD
|
[Activity](DATESMTD(Calendar[Date]))
|
Total MTD Net Change based on selected dates
|
ActivityPrevYearMTD
|
[Activity](DATEADD(DATESMTD(Calendar[Date]),-1,YEAR))
|
Total MTD Net Change last year based on selected dates
|
PeriodActivity
|
IF(HASONEVALUE(Period[Period]),
SWITCH(VALUES(Period[Period]),
"Current", [Activity],
"MTD", [Activity](DATESMTD(Calendar[Date])),
"QTD", [Activity](DATESQTD(Calendar[Date])),
"YTD", [Activity](DATESYTD(Calendar[Date])),
"PrevYear", [Activity](DATEADD(Calendar[Date],-1,YEAR)),
"PrevYearMTD", [Activity](DATEADD(DATESMTD(Calendar[Date]),-1,YEAR)),
"PrevYearQTD", [Activity](DATEADD(DATESQTD(Calendar[Date]),-1,YEAR)),
"PrevYearYTD", [Activity](DATEADD(DATESYTD(Calendar[Date]),-1,YEAR)),
BLANK()))
|
Total Net Change for selected dates and period (Current, MTD, QTD, etc.). This field can be dragged to the Pivot Table or report along with dates and period to dynamically calculate the Net Change amount.
|
CurrentAssets
|
CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Accounts Receivable" ||
RELATED('Accounts'[Account Category]) = "Cash"||
RELATED('Accounts'[Account Category]) = "Inventory"||
RELATED('Accounts'[Account Category]) = "Prepaid Expenses"||
RELATED('Accounts'[Account Category]) = "Work in Process"||
RELATED('Accounts'[Account Category]) = "Notes Receivable"||
RELATED('Accounts'[Account Category]) = "Short-Term Investments"))
|
Total Current Assets for the last date based on selected dates
|
CurrentLiabilities
|
CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Accounts Payable" ||
RELATED('Accounts'[Account Category]) = "Dividends Payable"||
RELATED('Accounts'[Account Category]) = "Interest Payable"||
RELATED('Accounts'[Account Category]) = "Notes Payable"||
RELATED('Accounts'[Account Category]) = "Other Current Liabilities"||
RELATED('Accounts'[Account Category]) = "Taxes Payable"))*-1
|
Total Current Liabilities for the last date based on selected dates
|
CurrentAssetsBeg
|
CALCULATE(SUM([Beginning Balance]), FIRSTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Accounts Receivable" ||
RELATED('Accounts'[Account Category]) = "Cash"||
RELATED('Accounts'[Account Category]) = "Inventory"||
RELATED('Accounts'[Account Category]) = "Prepaid Expenses"||
RELATED('Accounts'[Account Category]) = "Work in Process"||
RELATED('Accounts'[Account Category]) = "Notes Receivable"||
RELATED('Accounts'[Account Category]) = "Short-Term Investments"))
|
Total Current Assets for the first date based on selected dates
|
AvgCurrentAssets
|
([CurrentAssetsBeg]+[CurrentAssets])/2
|
Average of beginning and ending Current Assets based on selected dates
|
Inventory
|
CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Inventory"))
|
Ending Inventory balance based on selected dates
|
InventoryBeg
|
CALCULATE(SUM([Beginning Balance]), FIRSTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Inventory"))
|
Beginning Inventory balance based on selected dates
|
AvgInventory
|
([InventoryBeg]+[Inventory])/2
|
Average of beginning and ending Inventory
|
AccountsReceivable
|
CALCULATE(SUM([Ending Balance]), LASTDATE(Calendar[Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Accounts Receivable"))
|
Ending A/R
|
AccountsReceivableBeg
|
CALCULATE(SUM([Beginning Balance]), FIRSTDATE(Calendar[Date]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Accounts Receivable"))
|
Beginning A/R
|
AvgReceivable
|
([AccountsReceivableBeg]+[AccountsReceivable])/2
|
Average of beginning and ending A/R
|
NetSales
|
CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Sales" ||
RELATED('Accounts'[Account Category]) = "Sales Returns and Discounts"))*-1
|
Net Sales based on selected dates
|
COGS
|
CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Cost of Goods Sold" ))
|
Cost of Goods Sold based on selected dates
|
TotalLiabilities
|
CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Accounts Payable" ||
RELATED('Accounts'[Account Category]) = "Dividends Payable"||
RELATED('Accounts'[Account Category]) = "Interest Payable"||
RELATED('Accounts'[Account Category]) = "Notes Payable"||
RELATED('Accounts'[Account Category]) = "Other Current Liabilities"||
RELATED('Accounts'[Account Category]) = "Taxes Payable"||
RELATED('Accounts'[Account Category]) = "Long-Term Debt"))*-1
|
Ending Total Liabilities
|
TotalEquity
|
CALCULATE(SUM([Ending Balance]), LASTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Common Stock" ||
RELATED('Accounts'[Account Category]) = "Retained Earnings"))*-1
|
Ending Total Stockholders’ Equity
|
TotalLiabilitiesBeg
|
CALCULATE(SUM([Beginning Balance]), FIRSTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Accounts Payable" ||
RELATED('Accounts'[Account Category]) = "Dividends Payable"||
RELATED('Accounts'[Account Category]) = "Interest Payable"||
RELATED('Accounts'[Account Category]) = "Notes Payable"||
RELATED('Accounts'[Account Category]) = "Other Current Liabilities"||
RELATED('Accounts'[Account Category]) = "Taxes Payable"||
RELATED('Accounts'[Account Category]) = "Long-Term Debt"))*-1
|
Beginning Total Liabilities
|
TotalEquityBeg
|
CALCULATE(SUM([Beginning Balance]), FIRSTDATE(AccountBalances[Ending Date]), FILTER('AccountBalances', RELATED('Accounts'[Account Category]) = "Common Stock" ||
RELATED('Accounts'[Account Category]) = "Retained Earnings"))*-1
|
Beginning Total Stockholders’ Equity
|
NetIncome
|
CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED(Accounts[Financial Statement]) = "Profit and Loss" ))*-1
|
Net Income based on selected dates
|
InterestExpense
|
CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED(Accounts[Account Description]) = "Interest Expense" ))
|
Interest Expense (Note: I used the Account description for this purpose because the sample COA uses Other Expenses category for this account)
|
TaxExpense
|
CALCULATE(SUM([Net Change]), FILTER('AccountBalances',RELATED('Accounts'[Account Category]) = "Tax Expense"))
|
Tax Expense
|
GrossProfit
|
[NetSales]-[COGS]
|
Gross Profit based on selected dates
|
AvgTotalAssets
|
([TotalLiabilities]+[TotalLiabilitiesBeg]+[TotalEquity]+[TotalEquityBeg])/2
|
Average Total Assets for the selected dates
|
AvgTotalEquity
|
([TotalEquity]+[TotalEquityBeg])/2
|
Average Total Equity for the selected dates
|
NumberOfDays
|
1.0*(LASTDATE(Calendar[Date])-FIRSTDATE(Calendar[Date]))+1
|
Number of Days in the selected date range. This field is used in Days calculation in the ratios below.
|
Now that all the base fields are defined, we can create the calculated fields for financial ratios. The following tables show the financial ratios and the DAX formulas. They are divided into the following categories: Liquidity, Solvency and Profitability.
Liquidity Ratios
Calculated Field
|
DAX Formula
|
Additional information
|
CurrentRatio
|
[CurrentAssets]/[CurrentLiabilities]
|
Current Ratio
|
QuickRatio
|
([CurrentAssets]-[Inventory])/[CurrentLiabilities]
|
Quick Ratio or Acid-test Ratio
|
InventoryTurnover
|
[COGS]/[AvgInventory]
|
Inventory Turnover
|
AvgDaysInInventory
|
[NumberOfDays]/[InventoryTurnover]
|
Average Days in Inventory
|
ReceivableTurnover
|
[NetSales]/[AvgReceivable]
|
Receivable Turnover
|
AvgCollectionPeriodDays
|
[NumberOfDays]/[ReceivableTurnover]
|
Average Collection Period in Days
|
Solvency Ratios
Calculated Field
|
DAX Formula
|
Additional information
|
DebtToEquityRatio
|
[TotalLiabilities]/[TotalEquity]
|
Debt to Equity ratio
|
TimesInterestEarned
|
([NetIncome]+[InterestExpense]+[TaxExpense])/[InterestExpense]
|
Times Interest Earned Ratio
|
Profitability Ratios
Calculated Field
|
DAX Formula
|
Additional information
|
GrossProfitRatio
|
[GrossProfit]/[NetSales]
|
Gross Profit Ratio
|
ProfitMargin
|
[NetIncome]/[NetSales]
|
Profit Margin
|
ReturnOnAssets
|
[NetIncome]/[AvgTotalAssets]
|
Return on Assets
|
AssetTurnover
|
[NetSales]/[AvgTotalAssets]
|
Asset Turnover
|
ReturnOnEquity
|
[NetIncome]/[AvgTotalEquity]
|
Return on Equity
|
The following screenshot shows the results of these ratios from the sample company (TWO) data.
These ratios will be automatically recalculated when you refresh the PowerPivot data model to reflect updated GL data. You can enhance this data model by creating KPIs based on industry benchmarks and historical data for your company.
