A FEW CHANGES HAVE BEEN MADE TO THE ORIGINAL INSTRUCTIONS PLEASE SEE BELOW IN RED
The CEO Juice Financial Model Mapping process is designed for dealers that are members of groups like CDA, SDG, BPCA, PDG, etc. where the group is comparing financial data to a "model". Our process is based on mapping your existing accounts to the accounts in the model. This would be a one time exercise and only need to be repeated if you added accounts. Then we would install reports that would refer to the mapped data, details on running that report here.
We use the attached spreadsheet to allow you to do the mapping, once mapped the data is stored to a table on your server where we can reference it. We estimate that it takes about 3 hours to map everything
Start by opening the attached spreadsheet. See here for instructions on connecting the spreadsheet to your eAutomate database.
Once complete then:
Then click on the GL Account lookup tab at bottom to see your existing accounts.
and the GL Depts tab to see your existing departments.
You can filter (1) and just see certain account types (maybe start with revenue). You can also just see your active accounts (2).
Then go to the tab called ZCJ Financial Model Map
Now pick your EA account in column A and department in column C and then match to the Model account in column F. You must map all GL Account / Dept combinations that have $$ posted to them in your EA system (see Supply Rev lines 3 & 4 example below)
If you direct All revenue to one account where the model expects it to be split then you must map the % splits (see lines 5 & 6 in example below for split of Contract revenue)
If you use percentage splits to make sure they total 100 percent (1)
**note - you are not required to use the drop down box on each GL account - you may copy your GL account numbers and paste into column A
BUT it may overwrite the drop down lookup capability for future use so be aware and carefully check the spreadsheet as you go....
example below - I just pasted 3 accounts into lines 7,8,9 - the lookup field for GL name is working properly
The ModelAccount# and ModelDept# fields should be left AS IS on the tab named CJ Lookup Data. Select from these choices on the Mapping tab.
The "Other" lines will be excluded from certain Imaging benchmark calculations but included in total company results.
Split Imaging into MFP versus MPS only if you have proper setup within EA to split them into specific accounts or use Imaging as a whole.
Then within the model analysis you will take your best guess as to the correct percentage splits between MFP / MPS based on your organization.
***Use either (B) Imaging or both (A) Imaging MFP/MPS but not both in the mapping tab.
Solutions is for segregating any stand alone product not dependent upon specific equipment sales Solutions examples --Document Management Software, Uniflow, E-Copy,
This is what determines the selections available on the ZCJ Financial Model Map Tab
There is an addition for branching. If you are not using either branching or departments, you still need to choose the blank space from the drop down menu.
UPDATE - Once you finish mapping a certain group or all of your income statement accounts go back to the Add In tab, pick CEOJuice again but this time click Update CEOJuice DB -
You may update different groups individually and you can check your progress or wait and update 100% of all your accounts.
Once complete you need to upload the information, see the same link for instructions (See here for instructions on connecting the spreadsheet to your eAutomate database.)
This usually means you have a illegal character in a GL account name such as an apostrophe, quotes, or commas, example Nat'l.
To fix this you must change the GL name in your eauto and then refresh the spreadsheet GL accounts.
Or if you receive this error
Table and Data Creation Error
Error Number 13
Error Description: Type Mismatch
This usually means you have partially filled in rows with either partial information or NA, you will need to delete the entire row and try to update again.
After the update you may then review the Results tab to see your current mappings
CEOJuice has built a custom report to show a list of everything mapped and corresponding GL totals. It has various settings so you can review any combinations that still need to be mapped.
When you run the report run it for the period of last year, show unmapped and mapped, and show mapping details.
When you have mapped what you believe to be all of your account combinations you can run this report to see financial results of mapped accounts.
Contact CEO Juice if this report has not yet been installed on your system.
Once we have installed the custom report on your system you will find in EAutomate Reports - Custom Reports folder
The report is called CEOJuice SDG GL Account Mapping Report See Here for instructions on how to run the custom report...
You will notice the addition of employee mapping tabs, please see for instructions Here
The tab How to Links in the excel file should bring you back to these sets of instructions if you need them in the future.
To begin Click the link below to download the spreadsheet -Financial Model Mapping Tool.
***For reminder of which accounts to map into the model accounts download and review the attached Income Statement Coding details.pdf
When you have completed the mapping of all accounts - return the spreadsheet with full results and the CEOJuice report to us for review and discussion firstname.lastname@example.org.
IF THERE IS A NEW VERSION OF THE SPREADSHEET, SIMPLY EXPORT FROM THE OLD SHEET AND IMPORT IN TO THE NEW SHEET TO TRANSFER.
**** SOME CLIENTS ARE HAVING CHALLENGES WITH XP MACHINES **** Please download the XP Version instead from BELOW****
Also if you need to map more accounts be sure to ADD to the list using any available lines on the bottom - do not clear out the list - upon the next update it will rebuild the SQL Table with only the lines in the current spreadsheet - keep your list running will ALL account mappings.