Easy to use utility to push your budget(s) into eAuto.
Jump to Overview | Samples | Variables | Alert Functionality | Related Alerts | Download Spreadsheet
Overview
Overview
Easy to use utility to push your Financial Budget(s) into eAuto. This process will populate an Excel spreadsheet with historical data that you can edit to create a budget for each year. The data is stored in eAuto as well as our tables and available in our Power BI dashboards, eViews etc.
Budget by month, by branch, by Dept, by GL code, you decide how detailed you want it to be and how many budgets you want for each year.
If you already have a budget in Excel this should take less than 15 minutes to implement. If you are new to eAutomate use a budget to see historical numbers.
To be used by CFO's, Controllers, etc. This is NOT for Service and/or Sales Managers to create their department budgets.
Run Schedule: On Demand
* * *
Sample
Sample
Sample of dashboard view of Budget v Actual in ID770
* * *
Variables
Variables
This alert has no variables
* * *
Alert Functionality
Alert Functionality
1. To be used by CFO's, Controllers, etc. This is NOT for Service and/or Sales Managers to create their department budgets.
2. If you encounter issue with these buttons getting smaller and test getting larger, just restart excel please:
3. By using our Import Utility, you can easily build your budget from existing financial data in eAuto.
Our Power BI ID770 Financials Reports will automatically pick this budget detail out from eAuto with each refresh completed o ID770.
Steps
Steps
Step 1: Subscribe to alert ID40
You MUST be subscribed to and received notice from us that the install is complete before moving any further. We will install background procedures before anything below will work for you!
Step 2: Download Excel Workbook/Import Utility
At bottom of this post, you will find an excel workbook to download to your PC. This should be downloaded to the PC the budget will be created on as this ensures it can be saved in required .xlsm format and is easily accessible by an end user for further edits, etc. (no need to give end user access to your server).
Your IT will need to provide credentials and create a SQL user once, then end user creating budgets can go to Step 5 anytime budget updates are needed.
The following two steps to be completed by your IT:
Step 3: Enter Credentials on Worksheet named 'Settings'
Please use this video for instructions on Step 2 and Step 3:
Credentials on 'Settings' Worksheet:
(1) name of your eAuto Production database
(2) Enter name of server your eAuto resides on
(3) Leave Yes listed in Other Settings section
Step 4: Create SQL User in SQL Server Management Studio
This SQL User is to be given specific permissions to specific tables in the database. This SQL User and password will be needed for the individual creating budgets. Please run the .sql document named "ID_40UserCreateScript " at the bottom of this post to create user against either CEOJuice database or your eAuto database.
SQL User to entered here:
Step 5: Create your budget(s)
Watch this video for instructions on how to create Budget(s)
**Please note video indicates to enter CEOJuice db on Settings worksheet, this is NOT correct, you must enter your eAuto Production DB name.
Brief recap:
Make sure your 'Settings' worksheet correct reflects your eAuto Product database name and the server name your eAuto database resides on:
Go to 'Budgets' worksheet, select Refresh:
Enter the SQL User and Password created for this process:
Now you will see any/all eAuto Budgets. You should not see anything here if you have never entered Budgets into eAuto or not used this utility before:
Go to 'Budget Menu' worksheet, enter values on 'CREATE' row (in green) to create new budget. Number and Name the budget, select the Source Year (this pulls in your financials from that year, so perhaps last year?), enter to year (ideally 1-year out from your Source year). Then enter % increase or decrease in Revenue, Expense, and COGS. (105 = 5% increase over last year and 97 = 3% decrease over last year, 100 = no change over last year).
Once entries completed, select 'EDIT' (row in yellow).
Note that on the Budget Menu tab, in order to perform the task all highlighted cells must be filled out in the same row that the button is in.
Then go to 'Budget Detail' Worksheet. Make edits and select Update and Insert.
Note that when copy/pasting to the Budget Detail tab, excel will only recognize a local edit in the cell pasted. You'll need to either copy/paste Yes in all rows or delete the value on the Settings tab.
Now you will see your Budget here in eAuto under your Standard Reports:
SQL Permissions
SQL Permissions Issues/Errors
Be sure the SQL user you've created for this process has the following permissions.
Run script against either your CEOJuice database or your eAuto database.
**Also be sure to insert the needed database names and password into the script before running:
You only need to change CEOJuice db name IF your CEOJuice database is something different (i.e. CEOJuiceDB1 and CEOJuiceDB2)
Production Database
GLBudgets - SELECT, UPDATE, INSERT
GLBudgetDetails - SELECT, UPDATE, INSERT
GLJournalDetails - SELECT
GLJournal - SELECT
GLAccounts SELECT
GLAccountTypes - SELECT
GLDepts - SELECT
GLACcounts - SELECT
GLBranches - SELECT
GLBudget_BudgetSetup - EXECUTE
GLBudgetDetailPivot - SELECT
v_ZCJ_GLBudgetDetailPivot - SELECT
ZCJ_GLBudget - EXECUTE
CEOJuice Database
v_ZCJ_GLBudgetDetailPivot - SELECT
ZCJ_GLBudget - EXECUTE
* * *
Related Alerts
Related Alerts
ID770 - Power BI Financial Report (with budget)
* * *
Download
0 Comments