How to Build Financial Budgets with Operational Models

This is part 2 of a 3 part series on combining financial & operational models to link key business drivers to budgets & forecasts.  Click here to review part 1.

So, how should you go about building financial budgets with live linkage to operational models?

• Shed disconnected, error prone and broken-linked spreadsheet models popularly referred to as 'Excel hell'
• Leverage technology to:
• Eliminate manual processes used to 'link' operational data
• Enable variance analysis at both the financial and operational model level
• Leverage manager and professional/analyst resources by having them focus on higher-value work (i.e. rather than compiling data)

Please browse this  autodemo for an overview of Prophix CPM, a solution that can facilitate sophisticated operational modelling.

Why Financial Budgets Need Operational Models

This is part 1 of a 3 part series on combining financial & operational models to link key business drivers to budgets & forecasts.

So, why do financial budgets need operational models?

• Increase ability to anticipate and react to change in today’s uncertain environment where past ≠ future
• Traditional budgeting’s focus is often misplaced (i.e. focused on salaries & wages vs attracting/retaining customers and human resources)
• Key drivers link operational tactics to the expected financial results of executing on strategy, giving insight behind the budgeted numbers and facilitating variance analysis

Please browse this autodemo for an overview of Prophix CPM, a solution that can facilitate sophisticated operational modelling.

Looking for an FRx, MR, or Forecaster alternative or replacement?

For the record, I am a huge supporter of Microsoft's CPM solutions, which currently include FRx, MR and Forecaster.  While these solutions generally serve most CPM needs, there are certain CPM requirements that cannot be addressed well by these solutions.

Prophix CPM is a scalable enterprise level CPM solution that can handle 7 of these more challenging requirements:

1. Combine financial & operational models to link key business drivers to budgets & forecasts
2. Facilitate complex planning and forecasting scenarios
3. Improve reporting speed
4. Facilitate a robust workflow to help manage the budgeting cycle
5. Automate the dissemination of reports and budgeting templates
6. Fully automate and schedule administrative functions (i.e. loading of data, etc)
7. Reduce administration to facilitate more frequent forecasting and timely intervention

If you're looking for a replacement for any or all of these solutions, Prophix CPM should make the short-list. Contact me or view the autodemo for more information.

TimeXtender simplifies the BI backend!

Tell me, just how many executives, business analysts and other BI end users are excited by the deep technical knowledge and time it takes to properly develop and maintain the backend of a BI solution, namely a data warehouse, the extract, transform and load (ETL) processes and finally the cubes themselves?  But without a good backend for your BI solution, even the best dashboarding and BI/analytics solution available (i.e. TARGIT BI) will not be able to provide the information that your organization needs to for analysis and effective performance management.

TimeXtender simplifies the administration of the BI backend so that your organization can realize the value of its chosen analytics/visualization solution.

This is also part of the reason Jet Reports partnered with TimeXtender in their Jet Enterprise offering.  Read the interview with Joe Little, President of Jet Reports, here.

TARGIT BI - A proven BI solution that works!

TARGIT BI has been recently recognized in a BARC research study as the most widespread BI solution used due to ease of use and product functionality.  For a summary of the results of this study, please click here.

MR 2012 Expected Release Date & Functionality

MR 2012 (previously referred to as MR2 FP2) is currently scheduled to be released in the late March/early April 2012 time frame. 

MR 2012's new functionality centers around the theme of 'distribution' and is expected to offer the following:

• Microsoft Dynamics NAV integration
• A richer report viewing experience, including:
• Drill back to Dynamics ERP
• Show accounts without dimension codes on reports
• Comments
• Instant Messenger integration
• Report output flexibility, including:
• Export to XPS
• MS SharePoint integration with alerts (There is not expected to be an MR Viewer for MS SharePoint, but rather a listing of reports that will launch the MR Viewer and view the report from the secure MR Report Library.)
• Report Scheduling (Note that command link scheduling is available now)

A listing of MR versions and release dates can be found here.

Prophix CPM

Prophix CPM is a fully integrated performance management solution that empowers organizations with budgeting, planning, forecasting, reporting, consolidations and personnel planning capabilities.  
 

Prophix CPM’s robust analysis functionality empowers end-user investigation with ad-hoc ‘drag and drop’ capabilities, automatic variance calculations and full drill-down to details on measures within the business model.

Prophix CPM has the ability to combine financial and operational business models, meaning that you can leverage Prophix to manage performance across your entire organization and roll these operation budgets into your financial model.  An example would be to create a project/job costing model that facilitates budgeting project costs at the operational level.  This model would then facilitate budget to actual reporting for projects and automatically update the financial budget/forecast when changes are made to any project’s budget/forecast.

Prophix CPM facilitates the automation and scheduling of administrative functions, from importing actual data to distributing Excel templates for end user contribution to distributing financial reports via email or Microsoft SharePoint.

Prophix CPM is ERP agnostic meaning that it can be used with any ERP system including any Microsoft Dynamics ERP.  It also fully integrates with TARGIT BI.

For more information contact me and/or view the Prophix CPM Product Tour.

MR Translations - FRx vs MR

Management Reporter's financial translations feature is completely different from FRx's functionality, which is a welcome improvement!

The highlights of FRx's functionality was that you had to:

1. Re-enter your average, spot and historical rates in FRx's DAX tables (since it couldn't pull this information from your Dynamics ERP), and
2. Have a number of settings correct in FRx (i.e. company currency, report catalogue translations feature, translations activated in the column and the column syntax correct). 

The beauty of the translations feature in MR is that it leverages the Dynamics ERP exchange rate information.  In Dynamics GP, it specifically references a default exchange table.  So, in most cases, a client can have two exchange tables (i.e. monthly average, and spot/historical) used for multiple purposes including: calculating exchange at time of transaction, revaluing foreign currency transactions and financial translations. Next, you have to set the translation setting for each account in your ERP.

Once this is complete, there is only one step to perform in Management Reporter to produce a translated financial statement, namely, ensure that you have a column with the Currency Source set to the translated currency.  In the sample below, the functional currency is USD and the translated currency is CAD:



MR Column Translating USD to CAD

This is what the final report looks like:

Sample MR Translated Report

I must say, this functionality is a great improvement and much more efficient than what was provided in FRx.  Enjoy!

Management Reporter Security & Licensing

Management Reporter's security model allows for four pre-defined security roles, namely: Administrator, Designer, Generator & Viewer.  These roles roughly correspond to their FRx counterpart; however, note that much of the rest of the way security works is very different than FRx.  For example, you cannot secure a specific report or building block to a specific group or user.

The chart below gives an overview of the MR security roles:

Now, MR licensing adds another twist.  Full Microsoft Dynamics GP licenses entitle an organization to an MR Designer license. This license allows the MR user to be given Administrator/Designer rights, although these licenses could be assigned as Generator/Viewers.

The Light Office user entitles an organization to an MR Viewer license which allows MR users to be granted Generator/Viewer rights.

So, if you require additional MR users, please contact your Microsoft Partner to review your needs; however, the following options may be available:

- a full Microsoft Dynamics GP license, which would give you an additional MR Designer license
- a separate MR Designer license (this would not give you any other Microsoft Dynamics GP users)
- a Light Office user.

Management Reporter Version Numbers & Release Dates

I was so inspired by a recent posting on FRx version numbers and release dates by Noah Moseley and Penny Watts posted here, that I thought I'd share the same for MR:

• MR2 RTM Release - 2.0.1662.37 (May 2010)
• MR2 SP1 - 2.0.1663.3 (September 2010)
• MR2 FP1 - 2.0.1664.19 (December 2010)
• MR2 SP2 - 2.0.1700.31 (June 2011)
• MR2012 (formerly known as MR2 FP2) - 2.1.1026.37 (March 2012)
• MR2012 RU1 (Rollup 1) - 2.1.10.28.0 (Spring 2012)
• MR2012 RU2 (Rollup 2) - 2.1.1029.30 (July 2012)
• MR2012 RU2 Re-release - 2.1.2030.2 (September 2012)
• MR2012 RU3 (Rollup 3) - 2.1.1031.40 (November 2012)
• MR2012 RU4 (Rollup 4) - 2.1.1033.30 (January 2013)
• MR2012 RU5 (Rollup 5) - 2.1.1037.12 (April 2013)
• MR2012 RU5 (Rollup 5 with hot fix) - 2.1.1037.15 (May 2013)

Top 7 Reasons to Choose TARGIT Business Intelligence for Microsoft Dynamics ERP/CRM

The following Top 7 Reasons to choose TARGIT Business Intelligence for Microsoft Dynamics ERP/CRM were presented at BDO's annual user conference  on May 12, 2011:



1) Easy to Use
2) Highly Interactive
3) Enables ‘Power Users’
4) Unique CALM/OODA Philosophy
5) Consolidate Reporting across Multiple Data Sources
6) Enterprise Level Solution
7) Better Performance
 
The following are a few key aspects from what I presented.
 
My take on how BI has evolved and how the level of insight has increased over time
 

 
An introduction to the Performance Management Pyramid or my explanation of what it takes for an organization to successfully implement and utilize a CPM solution
 

 
An introduction to TARGIT's unique CALM or OODA philosophy, which is embedded into their entire solution
 

 
A sample TARGIT dashboard
 

 
Please contact me for more information on how TARGIT might revolutionize BI in your organization and/or visit http://www.targit.com/

Dilbert and Why to Not Budget using Excel

For many smaller organizations with simple budgeting/forecasting needs, Excel works just fine.  However, as Dilbert points out below, there are certainly drawbacks to using Excel:

Source: http://dilbert.com/strip/2011-06-02

For organizations with more complicating needs, the value of a using a dedicated CPM solution for budgeting/forecasting and financial/management reporting are myriad, including:
- Simplify and better control the budgeting process
- Reduced administration of the budgeting cycle
- Increased collaboration
- Real time version control
- Mitigate speadsheet ‘mis-calculations’ and ‘overrides’
- Better manage security
- Historical tracking/audit of changes
- Integrated financial/management reporting solution to facilitate variance reporting
- Simply reforecasting administration

How to change your password in Management Reporter

If you have used Management Reporter 2 since the RTM release you may be aware that it was not easy to change your password once saved in MR.  It actually required a registry hack!

Well, MR now lets you change your password within MR itself and save it as well!  This means that you can log on to MR directly to your default company without having to enter any passwords at all!

To change your password in MR:

1. Select Company > Companies
2. Select 'Credentials'
3. Change your password
4. Click the option to 'save' your password (Optional)
5. Click 'OK'

How to Migrate from FRx to MR (Management Reporter)

The steps below are based on a combination of guidance provided by Microsoft, as well as, my own experience. 

An interesting statistic is that in a recent organizations migration from FRx to MR, 80% of the reports migrated properly with very little manual intervention.  A further 10% required moderate intervention and the last 10% required re-design due to the fact that row linking was used in FRx. 

Pre-Migration Clean Up in FRx

• Backup your Sysdata folder
• Backup your Specification Set(s)
• Decide which FRx reports and associated building blocks you wish to migrate to MR
• Prepare your FRx data for migration
• Remove/rename any building blocks with 'spaces' at the beginning of its ID
• Delete any unneeded reports and building blocks
• Delete any companies not in use, including the sample companies 
• Associate any building blocks not being used with a Report Catalogue (otherwise, it will not be migrated to MR)
• Ensure that any rows currently used with a row linking design in FRx are attached to a Report Catalogue, as they may not convert properly based on my experience.  (Note: Any reports with Reporting Trees that are referencing multiple rows will not be migrated properly as row linking is not currently supported.  Having said that, it is possible to reference multiple dimensions using the row links in a single row format, but I have seen some issues with this design migrating properly.) 
• Remove any .G32 files not required 
• Compact the FRx database
• Set each FRx company as default
• Ensure all GP2010 companies have the same segment names.  This step is only required if you have a multi-company installation and you have the same or similar segments/dimensions in each company's chart of accounts.  FRx' segment approach did not look at the actual names of the segment (i.e. Account, Department) but rather its position/length.  MR does look at this information and a typo or segment identified as 'Segment 1' could botch your entire migration!

Run MR Migration Wizard

• The documentation on this is readily available and quite good.

Review & Clean Up Reports in MR
 Remember, running the MR wizard is generally a one-time event, as each time you run it, it will remove all the MR building blocks.



Cautions:

• Rather than muddying your production FRx environment, I would recommend a separate FRx installation, preferably pointing at your GP2010 data as this comes in handy for validating that reports are working properly.
• Remember to review the current MR readme information for information on what does not migrate (i.e. security) as well as to understand the functionality differences between FRx/MR (i.e. row linking, account sets)

Good luck!

Management Reporter (MR) Feature Pack Update

Microsoft Management Reporter Feature Pack 2 can currently be expected in the latter half of 2011, which means it will be here in time for Christmas!

Many of the new features will be related to a distribution theme, such as:


• Sharepoint integration (Note: This likely will not be an MR Viewer experience within Sharepoint, but rather a listing of reports in Sharepoint that users can launch the MR Viewer on and view with the MR Reports Library where they are securely stored.)


• Emailing


• Scheduling (Note: Command link scheduling is available now.)


• Show accounts without dimension codes on reports (Note: Currently account segments also display the full dimension names, which many people have wanted changed.)


• Some type of Drill down to GP


Obviously the above is not a full list and it is subject to change.  Please contact your Microsoft Dynamics Partner for more information.

 

Forecaster 'Missing Link' Found!

With Microsoft's recent release of the Forecaster Data Transfer Tool, a 'missing link' in the Microsoft CPM solution has been delivered.  

This is excellent news and means that we no longer have to rely on a manual Excel based method to feed Forecaster data into Dynamics GP.  Instead, this process can now be automated. 

It also allows Management Reporter to report on Forecaster data via Dynamics GP versus directly as can be done in FRx.

Best of all, this tool is free! 

For more information, visit the Microsoft Dynamics CPM blog or CustomerSource.

Note: The tool is compliant with GP2010.

SmartView for Dynamics GP

When it comes to how to get your information out of Microsoft Dynamics GP, there are so many alternatives!

One distinction that I formally made between SmartLists (whether the out-of-the-box SmartLists or the custom SmartList Builder lists) and Reports (whether SRS Reports or Crystal Reports) is that reports typically have grouping and totalling of data to facilitate analysis and decision making.  Well, that distinction between 'lists' and 'reports' has been blown away with the introduction of SmartView for Dynamics GP!  This is a 'must-see' and 'must-have' tool for any and all GP users.

SmartView is like GP’s SmartLists; however, it is a whole lot better! SmartView is a fast, flexible and easy to use Excel-like tool for analysis that works with all existing SmartLists, including those built using SmartList Builder.

SmartView features include:
• Faster retrieval of data than the SmartLists they are based on.


• Unlimited & Advanced Filters to isolate just the data that you want to see.


• Grouping & Subtotals to dynamically group your data by dragging and dropping.

The Evolution of SmartLists to SmartListBuider to SmartView looks something like this:

Click here to watch a SmartView demo: SmartView Demo  (Make sure you select the Windows Media File version.)

For more information on SmartView for Dynamics GP contact your partner.