more-arw search

Q&A Forum

Does anyone have experience preparing weekly flash reports?

Flash ReportsI need to be able to provide a weekly flash report to the business owners as well as management, and I'm not exactly sure what types of information should be included. I believe that our ERP software (SAP Business One) has the ability to handle financial dashboards, but we don't have anyone in house that can design/build the dashboard. With that in mind, my initial thought is to prepare a flash report in Excel but that will involve manually extracting the data from SAP. I have a fairly good idea of what needs to be included in the report, but I would greatly appreciate any suggestions from other finance professionals.

Answers

Topic Expert
Wayne Spivak
Title: President & CFO
Company: SBAConsulting.com
LinkedIn Profile
(President & CFO, SBAConsulting.com) |

It all depends on how sophisticated the business owners are, but just include:

Your main KPI's
A very limited P/L statement (and maybe comparative/budget)
Working capital and movement
Current Ratio
Top and bottom revenue generators (products, services, salespeople)

Problem A/R children.

This could be done in a page.

Sarah Jackson
Title: Associate Editor
Company: Proformative
(Associate Editor, Proformative) |

Barry, you might be interested in this free report here at Proformative:

"Transform Your Financial Reporting"
https://www.proformative.com/whitepapers/transform-your-financial-reporting

Best of luck... Sarah

Dawn Hall
Title: Chief Financial Officer
Company: Bionix Development Corp
(Chief Financial Officer, Bionix Development Corp) |

I agree with everything Wayne suggested with perhaps the addition of a forecast that projects how actual performance will be compared to budget/goal for the month and year. You could also add a comparison to the previous year.

Barry Wallace
Title: Superintendent
Company: Searles Valley Minerals
LinkedIn Profile
(Superintendent, Searles Valley Minerals) |

Thank you all for your feedback. I was aware of the cockpit and dashboard feature in SAP, but as you mentioned it does take some initial setup. I'll take a look at it again and see if I can get some assistance from our IT support. Thanks again to you all.

Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

In case the BI solutions don't meet your needs, Excel probably could. With Excel, you could combine data from any available sources; you're not limited merely to Business One data. Also, you'll be able to include calculations in your reports that the BI solutions probably can't.

I believe that Business One has a relationship with PARIS Technologies, which can import B1 data into PARIS's PowerOLAP product. With PowerOLAP as an intermediary, you can enter formulas in Excel that return B1 data to your spreadsheet. This would allow you to set up an Excel dashboard that's live-linked to your B1 data.

(PowerOLAP also offers a good budgeting and forecasting solution, which can be tightly integrated with Excel, and also with Business One, as I remember.)

Second, I assume that B1 has a way to export your Trial Balance or other financial data as a CSV file, which you could open in Excel and then copy and paste to an Excel Table. (Excel 2007 introduced Tables.) This would allow you to use worksheet functions like SUMIFS and SUMPRODUCT to summarize data for presentation in your Excel dashboard.

With regard to the contents of your dashboard, in my experience you don't need to worry much about making your first version perfect. Giving managers *any* one-page report that's quick and easy to understand will be a delight. Then, once they understand that it's not difficult to change the measures you give them, they'll tell you what information they want to see.

Charley Kyd
ExcelUser.com

Barry Wallace
Title: Superintendent
Company: Searles Valley Minerals
LinkedIn Profile
(Superintendent, Searles Valley Minerals) |

Charley, thanks so much for the information and advice. I will take a look at PowerOLAP, although spending more money on software is probably not an option at this point. B1 does have an option to export the Trial Balance into Excel and I may play around with that some. However, I'm not the Excel expert that I want to be so manipulating data is sometimes a challenge for me, especially when it comes to using some of the advanced features. I think it's time to visit your website again.

Thanks again,

Barry Wallace

Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

Barry, I'm working on a training program for managing these kinds of issues in Excel. It would be helpful if I could get samples of Trial Balance exports generated by various ERP systems, including B1. The problem, of course, is that Trial Balances have confidential information. If you would like to write me privately, perhaps we can find a way around this issue.

scott Moulson
Title: Managing Partner
Company: Team Moulson Inc.
(Managing Partner, Team Moulson Inc.) |

Have worked with companies who track revenue, collections, certain costs against benchmarks. You should survey senior management and your users. There is a company, Indellient Inc. who can build (customize) this for you relatively quickly so you don't waste time spinning your wheels.

Scott Moulson
Team Moulson Inc.

Ben Williams
Title: CFO
Company: Weiler Corporation
(CFO, Weiler Corporation) |

I have used PowerOLAP in the past and would generally not recommend it. PowerOLAP is an older technology, and you can do better with some of the newer tools that are out there. Try SQL Server Analysis Services (SSAS) or PALO. SSAS did a nice job for us in getting info out of SAP B1. You can then use the data from SSAS very easily in either Excel pivot tables or formulas.

Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

Ben,

Analysis Services certainly is a popular and powerful product, but I wouldn't recommend it for many applications.

For example, unless there's been a change that I've not heard about, Analysis Services doesn't offer a way for worksheets to write data back to the server. (Excel certainly doesn't offer a write-back CUBE function.)

On the other hand, both PowerOLAP and TM1 (a similar but more expensive product from IBM) have offered write-back since their inception. With this technology, I can use formulas in either product to write budgets and forecasts from my spreadsheet to cubes of data on the server.

Also, in PowerOLAP, a sales person can update her sales forecast directly onto the server at Corporate by entering her numbers in her spreadsheet, on her laptop, from her hotel room, using the Internet as her network.

Do you want rolling forecasts? Giving Excel worksheet formulas the ability to write forecasts directly to the server (with full security, of course) certainly gives the Finance department a technically easy way to accomplish that task.

I haven't done any time trials, but my impression is that TM1 and PowerOLAP worksheet functions calculate much more quickly than do CUBE functions against Analysis Services.

Finally, both TM1 and PowerOLAP are much more CFO-friendly than Analysis Services. Not only do they cost less, but they give the Finance department much more control over their information resources.

To illustrate, many TM1 and PowerOLAP installations are purchased, installed, and managed entirely by bean counters. The IT department never gets involved, which is the way many CFOs want it. On the other hand, I suspect that very few Finance departments have taken the same approach with Analysis Services.

Ben Williams
Title: CFO
Company: Weiler Corporation
(CFO, Weiler Corporation) |

I have been extremely pleased with the response time with which cube formulas and pivot tables update using an Analysis Services data source. In fact, PowerOLAP spreadsheet refreshes took such a long time, especially over a VPN from home or on the road, that this was one of the main drivers that drove us to look at other products.

Also, Excel 2010 does allow cube write-back. I haven't done it yet, but the Microsoft literature promotes this functionality.

As far as cost goes, Analysis Services comes bundled with SQL Server, so there is no additional licensing costs for those companies that already use SQL Server. In our case, the finance department completely managed the cube development using SSAS, but I agree that we were far from the norm in this regard. Most companies would need to use IT resources. However, many of the "canned" SQL views that came along with our SAP B1 installation made cube development very easy.

I find Excel/SSAS to be a lot more user friendly than Excel/PowerOLAP. If I need to look something up quickly (sales detail by customer, item, geography, etc.), I can quickly get my answer by inserting a pivot table that is connected to a SSAS cube where I can browse the data. PowerOLAP was a lot clunkier in this regard.

Back to the initial posting in this thread, I use cube formulas in Excel to build standardized reports and dashboards that can be quickly updated by hitting the "refresh" button and or updating the month from one to the next... A number of BI tools can be used, but I'd look for something newer and better than PowerOLAP.

Jacob Barnett
Title: inance Professional
Company: n/a
LinkedIn Profile
(inance Professional, n/a) |

PowerOlap and SSAS would be overkill for the job of a simple bit of reporting.

I'd definitely go for Excel - you can start with a dump of the TB but you should look into PowerPivot for a more robust, automated solution.

David Tompos
Title: VP-Finance
Company: Fountain Industries, LLC
(VP-Finance, Fountain Industries, LLC) |

Barry-
I would start with Excel. This will allow the management team to "try out" what measurements should be on your weekly dashboard. After about 6 weeks, you'll have a pretty nice Weekly Flash that you can build into the ERP. Don't spend the time / effort putting this into the ERP until you are 90% sure of what the team needs.
Cash Flow s/b a component also. Would be a great idea to show 13 weeks back on the same "flash", so you have some trend analysis. Get some ideas from the Balanced Scorecard work done by the Harvard Business Review. Good luck.

Barry Wallace
Title: Superintendent
Company: Searles Valley Minerals
LinkedIn Profile
(Superintendent, Searles Valley Minerals) |

Thanks for the advice. I have put together a flash report in Excel that I'm pretty happy with at the moment. It took a bit of time for the initial layout and design, but I'm confident that I can prepare the weekly report going forward in less that 30 minutes. I've got a good bit of information on the report including cash flow but it is still a one-page report.

Thanks again for everyone for your advice. For now, Excel seems to be working fine for our needs.

22816 views
Topics
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.