Blog post by Jill King – Anaplan Pre-Sales
As I have shared with you in past blogs, I was working for a large enterprise with hundreds of analysts. On a regular basis our data from MS Excel had to be imported into Hyperion for data consolidation and to get analyzed by the Corporate FP&A team. Our group developed an extensive process to consolidate all of the data from our 20 excel spreadsheets into one summarized excel spreadsheet by department by account.
LOADING FORECAST DATA INTO HYPERION
We created a spreadsheet to consolidate the 20 department models into one “Hyperion Load” file. It had 20 tabs, 1 for each department that had a P&L forecast, which linked directly to the corresponding cell in the department model. There was an additional tab that consolidated all of that data into one spreadsheet using a vlookup in order to translate the numbered accounts (example: 7000 was salaries) into the correct Hyperion account format (Something like “Salaries – 7000”). The reason it was set-up this way was due to the inability to have a vlookup formula between files that would not get hung up or take hours to update. Our team concluded that a direct link to the excel files to compile the data first and then organize it in a separate tab to consolidate the data into one worksheet would be the best option. The final consolidated tab tied out all the data to make sure nothing slipped through the cracks in all that translation.
If all went well, files tied out on the first try. As you can imagine, this matrix of tabs and links generally created a snag. Our teams’ usual run was to then figure out what was missing and resolve the issues. We then save this resolved file in a text format, to an obscure folder on the network, and then run a “check” on the load to make sure it was in the appropriate format.
Once everything was loaded and correct, we would then wait until 11am or 3pm to have the data get uploaded into Hyperion. During this 30-minute upload period the team was unable to get into the forecast cube. After the 30 minutes was up, we would check to see if the data loaded correctly and if yes, pull out another tie out file that pulled the forecast from the system and compared it to what was in our excel models.
5 days later you might find the CFO asking you, “We need to cut marketing by 5 %. – please update the forecast and reload before the end of the week. Thanks!” Update? The whole process starts over again.
This is a non-issue for Anaplan users since all data is submitted real-time into the tool.
Estimated Monthly time savings = 3-7 hours (10-20 hours quarterly)
Quick Time-Saving Tips is updated every Monday on the Anaplan Blog. Interested in having Jill @jilleking review a tedious task or want to submit your own Time-Saving Tip? Tweet us at @anaplan and let us know!