I need to be able to provide a weekly flash report to the business owners as well as
Does anyone have experience preparing weekly flash reports?
Answers
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.
1
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
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.
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.
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
2
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
Barry, I'm working on a
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.
I have used PowerOLAP in the past and would generally not recommend it. PowerOLAP is an older
1
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
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.
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.
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.
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.
1
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.
Filed Under:
Technology