Home

Departments

1. Sis OF elatunsiies pes E J mg Dashboards Gauges Ratio Categories Ratios Parameter Sources Parameters Top Bottom Down Up Refresh Expand All Collapse All Select All Net Ordinary Income Net Ordinary Income Ratio Formula Expense Ratio Advertising Expense Company Advertising Max Expense Company Advertising Ratio Formula Gross Profit Max Gross Profit Ratio Formula Expense Ratio Floorplan Expense Inventory Interest Company Max Expense Inventory Interest Company Ratio Formula Gross Profit Max Gross Profit Ratio Formula Expense Ratio Semi Fixed Expense Semi Fixed Company Max Expense Semi Fixed Company Ratio Formula Gross Profit Max Gross Profit Ratio Formula Expense Ratio Employee Expense Company People Max Expense Company People Ratio Formula Gross Profit Max Gross Profit Ratio Formula Expense Company Fixed Max Expense Company Fixed Ratio Formula Gross Profit Max Gross Profit Ratio Formula Net Ordinary Income of Gross Profit Ratio Gross Profit Ratio Formula C Program Files CleverQlDatalCleverQDataSample qbd E Banking 2 E O Current Ratio H O Current Ratio O Total Current Assets Ratio Formula O Total Current Liabilities Ratio Formula 2 Acid Test Ratio O Acid Test Ratio JJ Total Current Assets Ratio Formula O Total Current Liabilities Ratio Formula O Total Inventories Ratio Formula Net Worth b
2. D Gross Profit Max O Expense Ratio Semi Fixed Gages Indicators Parameter Sources Parameters ee FI sn Ril eee Es Rss Collapse All ERA Select All EJ Highlight item above first then view Set Key Parameters Show Other QBO File Check items on this form then Export On top of the tree you will see the file you are connected to Just underneath the structure of the tree is shown For dashboards the structure 1s Dashboards UserID Active Gauges Indicators Parameters in The main section of the form has the tree and the branches on the tree are colored in a similar fashion to the buttons on the left to match what the object is In this case the tree shows all the dashboards in the file for all users It indicates which dashboard is for which user and which ones are active Under each dashboard the gauges are shown For each gauge the indicators are listed And each indicator has a list of parameters For parameters referenced at the indicator level the parameter name is followed by where in the indicator the parameter is used 1 e Indicator Formula Max Min etc If parameters have formulas that are based on other parameters those are shown too In fact the Page 64 CleverQ Customization User Manual nesting of parameters and their formulas are not limited and the tree will show the relationships to all levels A unique feature of the tree is when y
3. Page 73 CleverQ Customization User Manual result has already been calculated If it has then the stored result is used If not then the ACE will make the calculation and the result stored in the cache and also sent to the VP So formulas required can be determined on the fly when a gauge 1s to be displayed or you can actually scan all dashboards and gauges and determine the formulas needed along with conditions for the formula The conditions include Date Range Department Filters and Data Type To scan and save the formulas ahead of time press the Populate icon on the Formulas block on the Navigator Occasionally you will want to clear all the formulas especially if you have made changes to the ACE Definitions and Visualization Definitions To clear the formulas press the Clear icon in the Formulas block on the Navigator To view the formulas as well as any results if they have been pre calculated press the View icon on the Calculated Data block on the Navigator The cache can also be viewed by going to the menu item Help About and then choosing System Information Pressing this button will display the cache contents which include Formula Date Range Filters Data Type Value Time Stamp and Execution Time seconds EX Pf Formula DateRange DataType TimeStamp A E Total Income Forecast T 12 sal Total Income Forecast T 1 2 BB Total Income Forecast T 1 2 E Total Income For
4. reset all properties Gauge Properties Additional Drilldowns hu _ JJJ _ Q Q o 2 2 2 2 4 4 2 Report _z Income Scorecard 222222222222 Dashboard fincome o o ooo o Gross Profit pe o File fe Documents and SettingsiLarry My Documents 2010 pdf gt aa brave o wwwclewergcom EE 2 a 3 gt 3 gt 4 FT Tested Add Mew Gauge Delete Gauge f Where Used Save Save and Close There is no limit to the number of additional drilldowns and you can drilldown to a dashboard gauge report file or webpage This provides tremendous capabilities to have a master dashboard with each gauge summarizing something and then being able to drilldown to another dashboard This process could be nested without limitations Saving your new gauge is either done by pressing the Close button or choosing an existing gauge from the top drop down list Page 50 CleverQ Customization User Manual Print Gauge Detail There is a printable report available for each gauge To get this report you must select an existing gauge from the pull down menu on the Gauge Detail form Then press the Print button on the bottom of the form This will give you a report similar to the following It includes a graphical view of the gauge all the information about the gauge and also all the information about the indicator s used by the gau
5. that will allow you to define and set a data view The single indicator non chart gauges have a property called Data Drilldown that provides an optional drilldown from the standard line chart to view data In either case you add or edit a data view by pressing the l button on the Gauge Detail form Sener Late E A R Top Receivables Gauge Name Gauge Type Initial Date Range T Lock Date Range Data Type Gauge Properties additional Drilldowns Press this button to add a new data view or edit the existing eee eee Type Top 4 P vendors AJP Aging Summary System Top AiR Customers AJR Aging Summary System Top Salespeople Sales by Rep Summary System Top Salespeople by Month Sales by Rep Summary System For the purposes of gauge properties Ratio is used synonymously with Indicator TF Tested Add Mew Gauge Delete Gauge Where Used Save and Close A blank Data View form is shown in the next figure Page 54 CleverQ Customization User Manual DETTE Seta Data View Name Row Mames For reference Report Name Column 1 Text Column 2 Date Column 3 value Headings Format Column Source ie Fe Re ColumnDate is usedifa Datavalue is used if a heading is specified heading is specified Column Types Sorting Filters Rowhane Column Types For reference Column Type Filterin
6. Report Name Data Type Monthly Row Mame Formula Column Type Option OR Formula Ratio ANIE Tested Add Wew Parameter Delete Parameter Where Used Calculate By preceding the formula A B with the word Ratio followed by a colon the CleverQ calculation engine does not use the individual monthly values to calculate but instead using the Sum of A and the Sum of B To accomplish Option 2 where we take the summation of each monthly value we create our parameter as follows a JE Parameter Name Option To orere aufe naming change fhe parameter can be based on specific item g a Formula based on other parameters Report Name Data Type Monthly dl Row Mame Formula Column Type Optionz Formula 4 B OR T Tested Add New Parameter Delete Parameter Where Used Calculate This is similar to Option 1 without the word Ratio in front of the parameter formula To summarize Option with the word Ratio as part of the formula is calculated once when the parameter is referenced while Option 2 is calculated for each time period in a date range Each calculation is stored temporarily and then referenced for each time Page 18 CleverQ Customization User Manual period a parameter is referenced Option 2 actually creates new data to be stored to be used by other
7. TM CLEVERQ YOUR BUSINESS C Customization Manual Copyright 2008 2010 Better Days Enterprises LLC 722 Alta Vista Rapid City SD 57701 Revision May 2 2011 Version 1 85 CleverQ Customization User Manual Table of Contents A SR Re a Le ee ts 5 IN ANTS aio T ou 5 PrETerenceS asadas ee dr ee me 6 O eee ees 6 Department 6 Allocations Define Department dba 6 Denne Deparment e cio 6 AMO AU OIG nadie 7 US rD Hned ANOCATHONS SERRES RS nd dim dentine noes pata 8 Data EXEC usa A A dues 9 Data Sources Extractor Definitions Us 9 UIC OOKS NE iS AN E 9 EXCEL NV Or id told E doi 10 Databases ba 10 Data Packard aaa isaac 10 EXxtractcd Dalias nido a 10 VC MIM DAU At hc saat cata hearst tit canines ta catalase aN haranantstate thon 11 Editins Dala nta 13 Puree Extracted Data ansiosa adolece fas 13 Analytical and Calculation Engine ACE Re 14 ACE DEMAS ice 14 A A Se M RE 14 Creatina Parametros de ae 15 Calculator Opos slds nine due nan 15 Parameter Formula Socis SAR Nan ii occ one 16 Paranee S eai a du Nine 19 MATHIEU HONS da 21 Oter UOC a 21 Acre Sate FUN OMS ns o ica 21 POPC CAS i EUROS ed da de tu 22 Formulas Om Portilla ias 22 PES SPA ANI ICES usina on ini 22 Editino PAM e ls ne te an te 23 Parameters With ETOS SES nn a odiiasee 24 Deleting Parameters versatilidad 24 Special Parametros 25 IRON PALAIS ESS Re rr AAN 25 ROA Parma ee no a ne Die 25 A A O Zi Kev Indicar canas 27 Creauns Indicator A aient
8. menu will display the Department Allocations User Defined form Here you enter values from company financial reports that represent the contributions from various departments The percentages are available to be used as a source of data for parameter definition Page 8 CleverQ Customization User Manual C PSremetenDetal AE Dept Allocation BE Gross Profit Wel Parks Parameter Name Gross Profit Parks 4 parameter can be based on a specific item on report data source or a Formula based on other parameters Report Name Dept User Allocation Row Mame User Alloc 1 User Alloc 2 g User Alloc 3 User Alloc 4 User Alloc 5 User Alloc 6 User Alloc 7 User Alloc amp User Alloc 9 F Tested Add New Parameter Delete Parameter Where Used Calculate Column Type Formula Data Extraction The Extractor Definitions block has a dropdown box listing the various data source types available Choose a data source type and that definition will open If a type has previously been selected you only need to click on the icon just above the drop down list QuickBooks Database Excel Data Package Data Sources Extractor Definitions QuickBooks A preliminary discussion of getting data from QuickBooks Data was covered in the Introduction Reports and Setup manual You can pull data from many different QuickBooks reports Some of the reports include A P Aging
9. Gross Profit Gross Profit Forecast L for Linear Gross Profit Forecast P for Power Curve Gross Profit Forecast E for Exponential Curve Gross Profit Forecast LN for Logarithmic Curve If you enter a B as the operand the Dashboard software will find the Best fit and will automatically decide which of the 4 formulas to use This is done by doing the calculation for all four methods and then using the highest of the R squared values for to determine the best fit The mathematics of this function 1s beyond the scope of this user manual but Page 79 CleverQ Customization User Manual you can learn more about this from various sources including Microsoft Excel Help This function is identical to adding trendlines to a chart or using the Microsoft functions Trend Forecast Slope Intercept and RSQ Gross Profit Forecast B for Best Fit OF Gross Profit Forecast for Best Fit If the L P E LN or B is omitted the forecast function will default to B To also factor in the seasonal trend with the forecast you would precede the L P E LN or B with the letter T For example Gross Profit Forecast TL for Seasonal Trend with Linear Gross Profit Forecast TE for Seasonal Trend with Exponential Gross Profit Forecast TP for Seasonal Trend with Power Curve Gross Profit Forecast TLN for Seasonal Trend with Logarithmic Gross Pr
10. There is one year of monthly data also Cell N4 contains the sum of all 12 months of Row A data Cell N5 contains the sum of all 12 months of Row B data A B C D E F G H J K L M N O 1 2 3 1 31 08 2 29 08 3108 4 30 08 5 31 08 6 30 08 7 31 08 8781 08 9 90 08 10 91 08 11 30 08 12 31 08 Year Totals 4 AA 0 0 0 8 Em 5 6 7 A B 7 6 8 8 10 107 12 12 14 147 16 16 18 144 8 A B 0 20 0 33 0 60 0 67 1 00 1 00 1 40 1 33 1 80 1 67 2 20 2 00 D 9 10 11 Two options for yearly total of A B 12 1 SumA SumB 78 66 1 18 13 2 AB for Jan AB for Feb 14 20 14 Row 7 contains a calculation for the individual monthly sum of A and B with the total of the sums in cell N7 Row 8 contains a calculation for A B for each month This is where it gets interesting Cell NS wants to be the total of the calculation A B but do we total each individual month or do the calculation of the Sum of A divided by the Sum of B In some cases you may want to do it one way and in other cases you may want to do it the other way Here is how you handle this in CleverQ Assuming the green cells have been loaded into CleverQ with a Report Name Example Data We first create a parameter A as follows Page 16 CleverQ Customization User Manual A A aan Data Parameter Mame UN ln To overnite aufo naning mange e name after Felcha M z een es 38 4 parameter can
11. This requires a custom program to be CleverQ Customization User Manual written and installed to make that interface link work If that has not been setup you will get a popup message like that shown in the following figure ive Total COGS 1431 2006 216 556 1 Total COGS 2 20 2006 704 575 1 Total COGS 3 31 2006 496 000 1 Total COGS 4 30 2006 661 262 1 Total COGS 6 31 2006 599 442 1 Total COGS 6 30 2006 1 487 623 1 Total COGS 7131 2006 1 244 520 1 Total COGS 0 31 2006 1 566 741 1 Total COGS 9 30 2006 846 915 1 Total COGS 10 31 2006 332 161 1 Total COGS 11 30 2006 162 111 1 Total COGS 12 31 2006 151 036 1 Total COGS 1 31 2007 260 964 1 gt Total COGS 2 26 2007 473 262 1 Total COGS 3 31 2007 350 610 1 Total COGS 4 30 2007 733 951 1 Total COGS 5 31 2007 530 854 Warning Total COGS 6 30 2007 967 056 Total COGS 7 31 2007 1 656 392 i El Total COGS 8 31 2007 1 415 036 Q The custom interface to open test Total COGS is not implemented yet Total COGS 9 30 2007 739 585 Total COGS 10 31 2007 966 289 Total COGS 11 50 2007 204 242 Total COGS 12 31 2007 238 991 Total COGS 1 31 2008 264 109 aol 14 14 gt bl of 168 Reset Table to Default Preview Print Export Data eur tem Date Value ColumnType DataType Show as Pivot Table Close You have the option of changing the view from a list view to
12. the existing parameter name will be used Math Functions You can use the standard math functions addition subtraction As multiplication division A raise to a power Other Functions There are a number of functions you can use in the formulas These are covered in Appendix A Aggregate Functions For indicators you have the added feature of including what is called Aggregate Functions These allow you to specify additional ways of using the parameters The Aggregate functions include Sum Avg Min Max Stdev Var First Last and Count The function Sum is assumed as the default unless you specify something else The aggregate function is placed within the parameter s brackets following the parameter name andacomma For example Total Current Assets Avg This would calculate to average of the Total Current Assets parameter over the time period specified by the gauge when the indicator is calculated So if the data range was last year it would calculate the average monthly value of the parameter over the 12 month period last year If you did not include the Avg then the value calculated would instead be the sum over the 12 month period last year Indicator Aggregate Functions Sum Total over time period Page 29 CleverQ Customization User Manual NOTE You can only use aggregate functions on parameters that are based on report data and not formulas Page 30 CleverQ Customizatio
13. will see it Desired Range You can enter a desired minimum and maximum value for a desired range The values can also be a formula Benchmark You can enter a benchmark value for the indicator This will display on most of the gauges You can enter a value or a formula If you use the aggregate function average in conjunction with the same parameters used for the indicator formula you would be able to see on a gauge how the indicator value compares with the average of all the data Page 34 CleverQ Customization User Manual Formatting There are times when you want to format the result of an indicator and have it displayed on a gauge acertain way Three options exist for formatting Units Decimals and Mult The Units can be set for or The Decimals is set to an integer from 0 to 6 The Mult can be set to H K or M H hundreds K thousands and M millions Without formatting you might see a value on a gauge that looks like 54 234 000 50 With a format of 0 and M the result would look like 54M which is a lot easier to read Categories To make it easier to be organized with the indicators you can assign each indicator to one or more categories Use the Categories button to open the Set Categories for Indicator dialog box Current Ratio puis anys A Ok Record 4 LS LA of 1 Filtered There is no limit to the number of categories you can set for an i
14. A AA A nn O e AT ANA HON ariera E TS A Forecastine Fonctions ci nied ean E E Forecast FUNCH ON cesse de caen enr eee lentes RO LUCIO Sd a en ie NAN O ne a ce M ne RL En MT DAC EUROS atadas ii talas ad A A an bte ie EXP ENCON in num eme ibn teen Hem ion TE FUNC TOD nodo pco cede A NOS UNC HONS AAA FOUN PUN CU OM EE dette tie SEN D D D ie ates fee A nn nn nn ta a ver reer er Copyright 2008 2010 CleverQ Patent Pending Designed for use with QuickBooks QuickBooks is a trade mark and service mark of Intuit Inc registered in the United States and other countries Microsoft Excel and Microsoft Access are trademarks and service marks of Microsoft registered in the United States and other countries CleverQ is a trade mark and service mark of CleverQ and Better Days Enterprises LLC Page 4 CleverQ Customization User Manual Introduction This document is to be used for the power user who will work with the data setup parameters and indicators and configure gauges It is required that the user have already read the Introduction and Setup Manual as well as the General User Manual Navigator All of the actions that can be performed from the pull down menus can also be accessed through a very user friendly interface called the Navigator This form 1s available by selecting the pull down menu Setup Navigator You will then get the following form Cle yeyey y fer yivetyy Formulas Calculated D
15. Open Sales Orders by Rep Filter o Payroll Summary Payroll Summary by Employee Profit amp Loss v Profit amp Loss Filter v Sales by Customer Summary 0 0 0 Sales by Customer Summary Filter Sales by Item by Rep Summary Sales By Item By Rep Summary Filter Sales by Item Summary Sales By Item Summary Filter 0 0 0 Sales by Rep Summary Sales By Rep Summary Filter Seasonal Trend FP Export Pie __ Done Seasonal Trend PL 0 0 0 144031 44 335552 98 1532 4 Cox ara The form can be resized to you can see more data if you want The data is shown in a pivot table and there are many options for looking at this data Page 11 CleverQ Customization User Manual EN View Extracted Data E so ae 8 31 2008 9 30 2008 10 31 2008 11 30 2008 E 12 31 2008 E 1 31 2009 E 2 28 2009 E 3 31 2009 E 4 30 2009 E 50251 82 65348 96 57450 59 68688 73 71157 08 59807 69 55115 44 70365 80 55191 61 65713 29 58673 32 64684 78 576 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 31200 31200 31293 42 31293 42 31293 42 31322 72 31322 72 31322 72 31408 64 31408 64 31408 64 31481 03 314 82451 82 97548 96 89744 01 100982 15 103450 50 92130 41 87438 16 102688 52 87600 25 98121 93 91081 96 97165 81 901 100914 22 86688 73 113409 93 112892 97 129392 97 152602 9
16. a pivot table In this case you will need to drop the fields into the row column detail or filter drop areas 1251 DataType BE A E 2007 Jan Feb Mar Apr EMay Jun Aul Aug Sep Oct ANo AAA ASE AHH FS CR KE Re CR CS Rs Cf ee CD ees gt gt y Value v Value Value v Value y Value Value v Value v Value v Value v Value v Value 260 964 473 262 4360610 733 951 530864 967 086 1 656 392 1 415 036 739 505 968 289 254 242 195 019 9314723 9163893 5171933 5187505 9222635 9239393 5316838 155818 226 739 9261649 446 115 9644836 426 782 960 243 6664570 1 323 671 2 090 685 1 808 855 924 558 1 280 936 446 634 Kai ee ee Reset Table to Default Preview Print Export Data View Details Show as List Page 58 CleverQ Customization User Manual You can also Preview Print or Export the data to Excel Page 59 CleverQ Customization User Manual Dashboards Scorecards Refer to the General User Manual for a detail discussion of Dashboards View Active Select Dashboard to Dashboards View Page 60 CleverQ Customization User Manual Reports Manager The discussion of the Report Manager is covered in the General User Manual and Report Manager User Manual Calculated Data Page 61 CleverQ Customization User Manual Object Relationships Having gone through this document and getting familiar with dashboards ga
17. considered desirable in most sectors This Ratio is a rough indication of a fnn s ability to service ts current obligations However the composition and quality of current assetts is a critical factor in the analysis of a fnn s Liquidity fini tm aro Current Value 0 0 Desired Range Min Desired Range Max Benchmark Units Decimals 1 li uit Mote Indicator of a firm s abilityto meet shortterm financial obligations it is the ratio of current assets to current liabilities Though every industry has its range of acceptable curenteraios a ratio of 2 1 i considered desirable in most sectors Since inventory is included in current assets acid test ratio is a more suitable measure where salability of inventory is questionable Fomula Current assets Current liabilities Where Used You can determine where a gauge is used by pressing the Where Used button on the bottom of the Gauge Detail form A popup message box will be displayed listing the dashboards where the gauge 1s used Dashboard 0 entries are for saved dashboards not currently set to be displayed Page 51 CleverQ Customization User Manual Viewing Gauges Of course gauges are designed to be viewed on a dashboard But on the Gauge Detail form you can view the gauge by pressing the View button Be sure to set an initial date range first although if you don t all dates will be assumed The gauge will then be displayed just as it will appear
18. date range and stored to be used by other parameters or indicators To only have a formula calculated once with the specified date range preceed the Formula with Ratio Add New Parameter Calculate If you select Yes the parameters for Amount COGS Asset Value Margin Turnover ROA and Days Inventory will be created 1f they do not already exist If the data sources for these parameters are not available you will also be prompted whether you want to create the parameters or not Doing so will produce errors if you do not load the necessary data In addition to the Sales by Item Summary report you need the Inventory Valuation Summary report The parameters that get created will be listed in the message box ada Hew Parameters Successfully created SbIS Hardware ROA SbIS Hardware Margin SbIS Hardware Turnover SbIS Hardware Days Inventory STS Hardware Amount SIS Hardware COS SEIS Hardware Asset Value The parameters Amount and COGS come directly from the report Sales by Item Summary The parameter Asset Value comes directly from the report Inventory Valuation Summary The remaining parameters are formulas based on these three parameters Margin Amount COGS Amount Turnover COGS Asset Value Avg Numdays 365 ROA Turnover Margin Days Inventory Asset Value Last numdays Amount Page 26 CleverQ Customization User Manual These
19. for the selected parameter It is only applicable for parameters based on data sources that have had a seasonal trend calculated Itis also only applicable for monthly calculations When a date range of 1 year is used for the calculation this function should return a value of 1 Any date range will work and the sum of all the monthly fractions will be returned for that data range Mavg Function This function will calculate the moving average for an indicator or parameter For example Total Current Assets mavg 3 The number following the mavg function and separated by a comma is the number of previous periods that the average value will be based on This is in addition to the current time period So in the previous example the moving average will actually be calculated on the current and previous 3 periods or a total of 4 periods You can also enter a negative number for the number of periods which will then use future values rather than previous values The function is very sensitive to the time period being calculated For example if the parameter is being calculated for a month then the moving average is based on the previous X months If a parameter is calculated on a daily basis then the moving average 1s based on the previous X days Some inaccuracies may exist if the number of periods is small and the time period spans non working days like weekends But if the Page 83 CleverQ Customization Manual calculated date ran
20. is called operating profit for Loss as the case may be Also called earnings fici T Null to zero parameter can be mapped to a specific item on a report data het s imibias oF NEk profi source or a formula based on other parameters F No Filters Map Parameter Report Name Profit amp Loss Formula Calculator Row Name Net Income Column Type Y Calculate Parameter Formula Company Account Item WAR an Employee Name Note for Formulas When 4 parameter formula is calculated normally the results are calculated for each time period in a date range and stored to be used by other parameters or indicators To only have a Formula calculated once with the specified date range preceed the formula with Ratio Add New Parameter Delete Parameter Where Used Calculate Vendor Name Customer Name Finally if everything looks good you can keep track of the parameters you have verified by checking off the Tested checkbox on the Parameter Detail form By closing the form selecting an existing parameter from the top of the form or pressing the Add New Parameter button the existing parameter will be saved Editing Parameters To edit a parameter you either use the pull down menu and choose Setup Parameters or click on the Setup icon in the Parameters block on the Navigator You then select the existing parameter from the pull down list on the top of the form The following figure shows
21. lt A HREF xxxxxx gt yyyyyy lt A gt In place of the xxxxxx s enter the actual URL address like http www yourdomain com In place of the yyyyyy s enter the text you want to display when the tip is displayed This text will be underlined and will be what the user sees on the screen rather than the actual link If you want to display the actual link make yyyyyy the same as xxxxxx There are some buttons on the right side of the tips that can make editing the tips easier In all cases select the tip or advise to edit Advice For different ranges Plain text or HTML Though every industry has its range of acceptable current ratios a ratio of 2 1 is Zoom into edit and see more text considered desirable in most sectors The Stronger Ratio reflects a numerical superiority of current assets over current liabilities i Save advice to file to on edit using an external Generally the higher the current ratio the greater the cushion between current itor f po editor obligations and a Firm s ability to pay them Read advice from file Though every industry has its range of acceptable current ratios ratio of 2 1 is considered desirable in most sectors This Ratio is rough indication of a Firm s ability roe to service its current obligations However the composition and quality of current rs Display advice on e assetts is a critical Factor in the analysis of a Firm s Liquidity y screen the way the user
22. must be filled in include the Gauge Name Gauge Type and any Gauge Properties The Gauge Name can be anything you want Choosing a gauge type is done by using the drop down list or pressing the Select Gauge Type button That will bring up a form that will allow you to choose interactively the type Page 45 CleverQ Customization User Manual Bar Chart Ratios Circular Meter Circular Meter Deluxe Data Query Horizontal Bar Standard Line Chart Line Chart 10 Lines Line Chart 2 Lines 42 000 Line Chart Past Future Line Chart with Previous Period 44 000 Line Chart with Ranges x pa Da Net Income by Year qe e iNet Worth by Year Assets abiliias Mat Worth Panel Arrows 6 Panel Indicators 4 Panel Indicators 6 Panel Lights 6 Description of Gauge This graph shows the value of assets liabilities Die Chart and net worth by year ou can drill down to get a larger graph that shows the same items Text on a monthly basis Drilling down on the larger wertical Bat Bar graph will give vou a pivot table for the Balance Vertical Bar Deluxe cheet vertical Bar Standard Vertical Bar Standard Blue After selecting a gauge type the list of properties will be set When a property says ratio the word ratio 1s used synonymously with Indicator For the purposes of gauge properties these two words mean the same thing The properties vary depending o
23. often yearly you only need to do this yearly or when you have added a new data source The results will be a new report that you can select in the parameters The reports start with the words Seasonal Trend and are followed by the abbreviation for the data source For example Seasonal Trend PL for the Profit amp Loss report The data 1s stored as the current fiscal year on a monthly basis but will be accessed for any time period you specify The data value is a decimal and if you add all 12 months you will get a total of 1 These values are used in the trend and forecast functions covered in other sections of this manual Manually Editing Seasonal Trends You can view and manually edit the seasonal trends that you calculated by pressing the Manual Edit button This will display the following form Page 39 CleverQ Customization User Manual Select Filter Report Name fe Dept ee e Total lt gt 1 Show High Variance Refresh Company Column Calendar Month kis I EVO EOS EA E A ERRETS ES A A A Copy Curent Awg Displayed Average dlo d d J EEE ETE sae be pa e Current Average is the average of all non zero records T r Avg to Displaye otals should either be 1 00 or 0 00 ecords for the selected Report Dept and Column Type Cancel save Record 14 4 gt gt I gt You first must select the Report Name and optionally Department Itis not necessary to choose a row name or c
24. pull data either from Monthly Daily Annual Quarterly Weekly or Current Data After you select an Initial Date Range the appropriate data type will be chosen You can override this if you want You can also select Auto In this case the program will adjust the data type based on the selected range automatically For auto mode the selection of monthly or daily is done based on the same lookup list that automatically sets Monthly and Daily If a custom date range is selected then the preference for how many days of QuickBooks Daily Data is loaded determines if daily or monthly data is used Based on the data type the date used in retrieving a value is as shown in the following table Data Types and Dates Used Annually Last day of last month of fiscal year Page 49 CleverQ Customization User Manual Quarterly Last day of last month of fiscal quarter Monthly Last day of month Weekly Saturday Daily Current or Latest Last date data loaded You can also add notes about this gauge for reference in the field on the right side of the form In addition to the standard drilldowns provided which include standard previous period ranges and details you can add your own custom drilldowns using the Additional Drilldowns tab auge Detail Current Ratio Gauge Name Note Gauge Type Select Gauge Type Initial Date Range T Lock Date Range Use drop down selection Data Type ad Security Group changing gauge type will
25. sectors The Stronger Ratio reflects a numerical superiority of current assets over current liabilities Generally the higher the current ratio the greater the cushion between current obligations and a Firm s ability to pay them La Though every industry has its range of acceptable current ratios ratio of 2 1 is 1 1 ES considered desirable in most sectors This Ratio is a rough indication of a Firm s ability a to service its current obligations However the composition and quality of current assetts is critical Factor in the analysis of a Firm s Liquidity Desired Range i to A Tested q sew rer S _ i i Add Mew Indicator Delete Indicator Where Used Calculate Copy Indicator There is a lot of information on this form about the indicator The absolute minimum that must be entered is the Indicator Name and the Indicator Formula Everything else is optional Indicator Name The name of the indicator should be something that makes sense and would typically be something that is familiar Indicator Formula The indicator formula can be as simple or as complex as you want Formulas are based on parameters and are referenced by the parameter name enclosed in brackets Parameters If you know the parameter you want to include you can just type itin You can also paste a parameter into a formula by selecting a parameter from a list that is opened when you press the button On the form that opens y
26. the last fiscal year have more influence than the prior year In this case choose a Year 1 Weight of 1 and a Year 2 Weight of 2 This will give the second year twice as much influence as the prior year You can actually enter any number even though the dropdowns only give you a choice of 0 1 or 2 If you want to have different weights for different data sources calculate the seasonal trends independently Custom Year Weighting If you want to use a different yearly weight than the default or want to use more than 2 years you can enter the weights for each data source independently This is done in the yellow box labeled Y early Weights You enter the weights as numeric values for each year separated by commas For example if the current year is 2009 and you want to use the past 4 years of data to calculate the seasonal trends with more weights on the more recent years you could enter 4 3 2 1 That would give 2008 a weight of 4 2007 a weight of 3 2006 a weight of 2 and 2005 a weight of 1 Essentially 2008 would have 4 times the effect than 2005 You can enter from 1 to 5 years Calculating Check off on the left side of the form the data you want to calculate the seasonal trends for then press the Calculate Seasonal Trends button For each selected data source you will see a progress bar to show the status of the calculations This could take quite some time Since your prior fiscal years don t change very
27. to facilitate the export process Note The export process will only work in the Top Down mode You have the option to show the other QBD file and its objects prior to exporting if you want to If you do press the Show Other QBD File button If not simply display the objects in this file any way you want to For example if you want to export all objects associated with a dashboard view the relationships for dashboards and then check off the dashboard you want to export Checking off a parent object will automatically check off all children or objects beneath it You can then manual uncheck any objects you don t want to export Page 68 CleverQ Customization User Manual LA Ce CleverQ Data KISSIRYDashboardData gbd 1 Il y IN 3 Dashboards UserID Active Gauges Indicators Parameters in LC Banking Company Expense Deal ark Status Lights Expense Expense Advertising l 61000 Advertising Expense Indicator Formula Gauges 3 3 Expense FloorPlan l l 62100 Total Expense Inventory Interest Indicator Formula ee Expense Semi Fixed Categories l 63000 Total Expense Semi Fixed Indicator Formula Expense Employee 66000 Total Expense Employee Indicator Formula Expense Fixed 69000 Total Expense Fixed Indicator Formula Key Indicators Ordinary Income Gross Profit Benchmark Gross Profit High Gross Profit Low Gross Profit Max E Met Ordi
28. 010 8 34 40 AM 0 015625 EF Total Income 7 31 2009 25158 75 6 28 2010 8 34 40 AM 0 015625 E Total Income 6 30 2009 24947 84 6 28 2010 8 34 40 AM 0 015625 E Total Income 5 31 2009 5588 6 28 2010 8 34 40 AM 0 015625 E Gross Profit Last 12 months 269129 76 6 28 2010 8 34 39 AM 0 015625 a Gross Profit 25 Last 12 months 67282 44 6 28 2010 8 34 39 AM 0 03125 Gross Profit 15 Last 12 months 40369 464 6 28 2010 8 34 39 AM 0 03125 Mm 0 Last 12 months 0 6 28 2010 8 34 39 AM 0 B Total Expense Fixed amp Variable Last 12 months 25602 8 6 28 2010 8 34 39 AM 5 625 E Total Expense Personal Last 12 months 130804 28 6 28 2010 8 34 33 AM 5 109375 Fe Total COGS Last 12 months 180727 38 6 28 2010 8 34 28 AM 0 015625 El Net Ordinary Income Last 12 months 112722 68 6 28 2010 8 34 27 AM 0 15625 E Profit amp Loss Total Income 5 30 2008 5 31 20101 ILJb 187937 77084613 E Profit amp Loss Total Income 6 30 200915 31 201 0J TIM 344 562402700167 la Profit amp Loss Total Income 6 30 2009 5 31 201 01 1T r2 0 08880824308732 E Profit amp Loss Total Income 5 30 2009 5 31 20101 Tlb 1167724 2725359 eal Profit amp Loss Total Income 6 30 2008 5 31 201 Ol LIr2 0 5853061 8804324 Record 141 4 8 gt on gt of 109 You can force a calculation of all the formulas not already calculated in the Data Cache by pressing the Recalc icon in the Calculated Data block on the Navigator This action can
29. 27 he tor NAME se a A dde 28 dicar Portland 28 PO E O E eee 28 CleverQ Customization User Manual Mat PUN CU ONS iii ici 29 COST Funcion tddi 29 AS ere Sate PUNCHONG ad Ne E 29 POr cAStne Funcion 31 FR AIG CS uri ras E nid 31 RAS Colorao ae eee a dt 31 RATES O ae ane de da ae ree ee nan 33 RARES Adicta ias sitios 33 DESTA RAM e OS 34 Bencina ao 34 PORD Ato esca o eo iia bos 35 CALC COLICS rt AAA 35 FORMUIAS on Formulas util 39 Calculo and estu er death 36 Edition told ai drid 36 A E e E E tease a ctu lances seen 37 Delete Indicators lic 37 Copy ne LINC ALOIS nido 38 A A ds Ne 38 Calculating Seasonal rendir 39 Default sar Wel SANG curras 39 Custom Year Wet nulo eie a 39 A A 39 Manually Editing Seasonal Trends cta dde 39 Nisualizati n Denda os dodo en 44 A ET EE AA E EET A E EPE E E N E TAT 44 Gane TYPO Sea doi dE 44 Creatine GAS 44 Punt Gates Detd Eeen e Re ee 51 A A TO 51 V O O A 52 Edna Gaute SE a a a te A 32 Deletino GAUSS nissan 53 AA A A A E E dan siiote e 54 Dashboards CES E 60 Reports Manat eroen Ne NM ANR nt nus 61 ODJect Relationships caudal 62 EX 010 AHN ii as 68 ADO ES Re ne me a Un in 72 Testine And Debut ot don node Den TO A SR S eee 73 DS WU IVI CC noia deja 15 Appendix A Bult In Pun Cu Ons ses ME GRR UE Grise DE SPECIAL PUNCH ONS sun nu Laden M AE AA AAA AE TI pedala FUNCION ds TT AUmdays Pone UON aaa E te in 78 CleverQ Customization User Manual Prey Panci Oma nl edit mel nie lei
30. 3 82 157873 82 157873 82 157873 82 157873 82 157873 82 1578 E 30 Total Fixed Assets 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 958013 25 913577 34 9135 6 32 18700 Securit 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 Total Other Ass 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 3440 1181120 83 1181058 88 1198625 13 1207336 61 1224954 96 1247166 87 1251859 06 1302036 76 1317530 83 1346180 31 1345964 01 1313053 34 13362 E 39 20000 Accoun 7485 5435 5714 5000 5000 9050 11733 75 10979 87 9744 9050 9050 13100 116 E 40 Total Accounts 7485 5435 5714 5000 5000 9050 11733 75 10979 87 9744 9050 9050 13100 116 BY 20600 CalOil 4050 98 4050 98 4050 98 4050 98 4050 98 4050 98 4050 98 1710 98 6390 98 6390 98 6390 98 5860 98 50 Bs 20500 QuickB 1600 1600 200 3000 3000 3000 3000 3000 1050 7050 7050 oua Total Credit Cards 4050 98 2450 98 5650 98 4250 98 7050 98 7050 98 7050 98 4710 98 9390 98 5340 98 13440 98 12910 98 113 24100 Emp H Bi You can filter sort rearrange expand and collapse group print and export the data Refer to the section later in this document titled Viewing Extracted Data Using the PivotGrid The following figure shows you a typical QuickBooks report and how the elements of the report get loaded into the data table Sales by Item Summary 09 04 0083 Accrual B D
31. 6 aos oo 0 05 0 06 0 08 0 06 oo oo 0 19 0 15 0 11 100 000 0100 Cost of Goods So oof ooo oof oo 0 00 0 30 oo 0 00 Go o 0 14 02 100 000 4200 Equipment Rental oof 000 o oo 0 00 0 00 000 0 00 o oo o oo 0 00 0 16 os 100 000 4520 Freight amp Delvery oof oco ooo o oo 0 00 o oo 0 28 004 G19 0 40 0 00 0 09 100 000 Eos ib Mates O SO Sof eos o ve om oof oos os o asi ou ol 10 vo 54599 Less Discounts Taken 000 000 o oo Go 0 00 0 00 0 00 04 544007 Permis and Licenses 000 000 000 0 00 000 000 0 00 00 000 1545007 Subcontractors RE PR A MC M M _a 11 BEE rof 0 09 Total 54000 Job Expenses as oof oef o osf oof oof oi oef oa oz om tol om Total COGS 0 08 0 22 0 22 0 11 1 00 0 00 Gross Profit 67 0 07 0 06 00 ace EE om ole tod os 60110 Fuel i i 60120 Insurance 60130 Repairs and Maintenance 0 10 0 10 es copy coman av Deployed average 0 08 005 008 O oo o 00 00 09 om om os 0 A AAA A A F4 te sd q Current Average is the average of all non zero records Totals should either be 1 00 or 0 00 vg to Displaye otals should either be 1 00 or 0 Paste Y Recors for the selected Report Dept and Column Type Cancel save Record 14 4 1 gt i p of 60 Seasonal Trends are calculated for each row name and column type within a data source Each calendar month has a value The total of all 12 months should be equal to 1 00 or zero If any
32. 7 163635 07 200210 07 232440 07 2366 15 93 243255 50 242244 55 2739 100914 22 86688 73 113409 93 112892 97 129392 97 152602 97 163635 07 200210 07 232440 07 2366 15 93 243255 50 242244 55 2739 1540 1540 1540 1540 1540 1540 1540 1540 1540 1540 1540 1540 24418 10 24834 50 24834 50 24174 80 24174 80 24174 80 24174 80 24174 80 24174 80 23522 32 23354 08 23354 08 233 6750 5400 4050 2700 1350 11672 10024 34 8376 68 6729 02 5081 36 3433 70 9886 04 87 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 3593 44 35 16252 08 18252 08 18252 08 184 36301 54 35367 94 34017 94 32008 24 30658 24 40980 24 39332 58 37684 92 36037 26 49989 20 50173 30 56625 64 551 219667 58 219605 63 237171 88 245883 36 263501 71 285713 62 290405 81 340583 51 356077 58 384727 06 384510 76 396036 00 4192 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 106112 57 150548 48 1505 650000 650000 650000 650000 650000 650000 650000 650000 650000 650000 650000 650000 6 30600 30600 30600 30600 30600 30600 30600 30600 30600 30600 30600 30600 45652 45652 45652 45652 45652 45652 45652 45652 45652 45652 45652 45652 180000 180000 180000 180000 180000 180000 180000 180000 180000 180000 180000 180000 li 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 1578 Total 15100 V 157873 82 157873 82 157873 82 157873 82 157873 82 157873 82 15787
33. Cancel to cancel the rest of the export process If you say Yes you will also be prompted to overwrite all parameters Donn verme Marame teng E Do ou Want Eo overwrite ALL parameters Mo Select Yes to not be prompted anymore Press No to be asked for each parameter Yes You will see similar prompts for indicators and gauges Page 70 CleverQ Customization User Manual At the end of the export you will get a message that the export is complete Page 71 CleverQ Customization User Manual Importing The process of importing objects is simpler To import you select the Import Object selection on the Setup menu Weren standarni Borio File View Edit Navigator Preferences Data Sources Parameters Indicators Gauges Dashboards Security Object Relationships Export Import Objects You can also click on the Import box on the Navigator to start the Import process Definition Relationships Export Import Upload to Subscription Service Y ou will then be prompted for a QBD file to import from Select the file and all gauges indicators and parameters will be imported If an object with the same name exists the object will be imported and a number will be appended to it After the import you will see a message saying the import is complete and it will list the number of each type of object imported Page 72 CleverQ Customization User Manual iure Eo ala te E Imported 210 paramete
34. F3 Copy button can be used to copy the 12 values for the currently selected record to aclipboard Then choose another record and press the F4 Paste button to paste the values on the clipboard to the selected record You can also use the function keys F3 and F4 respectively to accomplish the same functionality The Copy Current Avg to Zero Records button will take the current average values displayed in green on the bottom of the form and fill in all zero records This is useful to give the records which did not have any history when the seasonal trends were calculated and set the trend to the averages of all the non zero records The Copy Current Avg to Displayed Records is another handy way to fix records If you use the previous figure as an example where the records displayed all have a high Page 42 CleverQ Customization User Manual variance By pressing this button they can all be fixed in one step again using the current averages You can continue to make changes to other reports and other departments before saving with the Save button The Save button will save all changes made since the form was first opened Pressing the Cancel button will lose all changes made since the form was first opened NOTE If a trend is referenced for a parameter where seasonal trends where not calculated the trend value for that parameter will be computed on the fly Page 43 CleverQ Customization User
35. Last and Count The function Sum is assumed as the default unless you specify something else Assume the formula Total Current Assets is used for an indicator This formula would calculate to sum of the Total Current Assets parameter over the time period specified Assume we have the date range of Last Month and the current month is May It would return the value for April Now add prev to the formula Total Current Assets prev This formula would calculate to sum of the Total Current Assets parameter over the previous time period specified Assume we have the date range of Last Month and the current month is May It would return the value for the month previous to Last Month or in this case March Here is another example Total Current Assets Last with a date range of Last Year for annual data and the current year is 2010 and the fiscal year begins in January This would return the value for the last month of last year or December 2009 Add the word prev to the formula like this Total Current Assets Last Prev or Total Current Assets Prev Last and the value returned is for December 2008 If the data type was monthly the value returned would be November 2009 If no previous value is available the value returned is null or zero Page 78 CleverQ Customization User Manual prevY Function The PrevY function works similar to the Prev function except 1t ignores the da
36. Manual Visualization Definitions Gauges The gauge is the visual element of the dashboard that displays the results of indicators or in some instances special reports or graphs Gauge Types There are numerous gauge types which can be viewed by selecting from the main drop down menu Setup Gauges View Gauge Types The discussion of Gauge Types was covered in detail in the General User Manual and will not be repeated here Creating Gauges To create a new gauge you would select from the main drop down menu Setup Gauges Setup Gauges or Y 2 O Q Q rs Dashboards A a PRESENT Lie Cc Visualization CLEVER Visualization efin use the Gauges icon on the Visualization Definitions block on the Navigator You will get a blank form Press the Add New Gauge button The form will fill in with the fields used to define a gauge as shown in the following figure Page 44 CleverQ Customization User Manual seared Selection gauge Name i eae Gauge Type Select Gauge Type Initial Date Range Lock Date Range Use drop down selection or press Select Gauge Data Tyee ooo oo aa uier anno secuty Geum see reset all properties Gauge Properties additional Drilldowns For the purposes of gauge properties Ratio is used synonymously with Indicator Tested Add New Gauge Delete Gauge Where Used Save and Close The required fields that
37. Summary A R Aging Summary Balance Sheet Inventory Valuation Summary Page 9 CleverQ Customization User Manual Inventory Stock Status by Item Payroll Summary Open Sales Orders by Item Open Sales Orders by Rep Open Sales Orders by Item and Rep Profit amp Loss Profit amp Loss Budget Overview Sales by Item Summary Sales by Rep Summary Sales by Item by Rep Summary Sales by Customer Summary Time by Name Refer to the Extractors Manual for details of how to setup and load data from QuickBooks Excel Worksheets Refer to the Extractors Manual for details of how to setup and load data from Microsoft Excel After the data is loaded the format of the data is similar to QuickBooks but the report names are based on what the user has determined There is also a second interface that can be used with Excel This is called the Data Package eXcel Extractor DPXE and using the Data Package interface rather than the Excel interface Database Refer to the Extractors Manual for details of how to setup and load data from Microsoft Access or any ODBC data source After the data is loaded the format of the data is similar to QuickBooks but the report names are based on what the user has determined Data Package Refer to the Data Package Creator Manual for details of how to use the Data Package Creator to enter data and load it into the CleverQ software This is also the interface to use with the Data Package eX
38. at 2008 Ala Avg Price COGS Avg COGS 37504 2 G 250 1 5 250 00 ITTRA 20 000 00 17 310 00 17 310 00 336A 2 250 00 4 200 00 4 900 00 3938 11 230 00 12 340 00 12 340 00 13 580 00 13 000 00 56 500 00 3938 13 000 00 Total TT 103 400 00 Total 162 TT amp 3th 163 FD amp PUC PUC JGA 5 289 41 2 300 00 2 200 00 39094 12 053 00 7 500 00 T 20000 10 00 00 Total PUC Total 163 FOAPUC 10 400 00 163 Other 40484 Total 165 Other 200 00 200 00 Total 160 Used RV 143 015 00 1 070 059 00 Total AV 1 070 510 00 Total Inventory Parts Dealer Options 1 304 24 MATERIAL CUSTOMER PAY 52 34 MATERIAL DEALER PAY 52 54 MATERIAL POS 24 07 MATERIAL WARRANTY PAY 28 23 Total Parts Service LABOR CUSTOMER PAY LABOR DEALER PAY LABOR WARRANTY PAY HON TAXABLE 30390 Total NON TAXABLE Page 12 CleverQ Customization User Manual Having a good understanding of the structure of the data after it is loaded from the various data sources 1s the key to creating the parameters that will be made available Normally you would not use this form to view the data but it is very valuable when you are creating parameters and indicators so you can check your formulas Editing Data On the Navigator screen you will find an icon in the Extracted Data block called Manual Edit Clicking on this icon will disp
39. ata Data Cache QED File C Dacuments and Settings All Users Documents CleverQ CleverQDataSample qbd Copyright 2008 2010 Clever The Navigator not only provides buttons that can be used to open various forms but it also contains a diagram that illustrates the flow of data from the data reports on the left to the Visualization Processor and Report Manager on the right The icons represents different functions and hovering over them will give you a clue as to what they will do Clicking on an icon will perform that function or open up a form The following sections will describe each of the blocks in the Navigator diagram Page 5 CleverQ Customization User Manual Preferences The Preferences block can be clicked directly to open up the Preferences form The discussion of setting up the preferences was covered in the Introduction and Setup Manual Security Clicking on the Security block will open the Security Manger The discussion of Security is covered in its own separate manual Departments Clicking on the Department block will open the Setup Departments menu Based on how the departments are setup on the Preferences form you will need to setup the departments a certain way On the Preferences form you can define departments by Allocations or not define department at all Allocations Define Departments If you choose Allocations then follow these steps 1 Create your department
40. be based on a specific item on a report data saura a Formula based on other parameters Example Data Row Data Type l Column Type Formula Formula Report Mame Row Mame OR M Tested Where Used Calculate Add New Parameter Delete Parameter Note when we hit the calculate button and select Last Year 2008 the total comes up that matches cell N4 in the worksheet Creating a parameter B is similar You can also create a parameter to match the cell N7 as shown in the next figure a Se Formula Parameter gt A eee Name Sum b E T To overwrite aufo naming change fe name arte parameter can be based on specific item on report a Formula based on other parameters Report Name Data Type Monthly Ed Row Pame Formula Column Type SUM AB K Formula A E DR F Tested Add New Parameter Delete Parameter Where Used Calculate To accomplish Option 1 where we take the Sum of A and divide it by the Sum of B Cell G12 in the example worksheet you would create a parameter as follows Page 17 CleverQ Customization User Manual me kt aS x Formula Optioni Parameter Name Option To orere avfa naning change fhe name after 4 parameter can be based on a specific item on report de a Formula based on other parameters
41. cel Extractor DPXE Extracted Data The extracted data is stored in part of the Data Cache a called Extracted Data You can perform three functions from the Navigator on this extracted data 1 View 2 Manual Edit 3 Purge Extracted Data Page 10 CleverQ Customization User Manual Viewing Data The data loading process populates an internal table referred to herein as either Data or the Data Table This internal storage has the same format no matter what the source of the data is To view the data you press the view icon within the Extracted Data block on the Navigator You can also use the pull down menu and choose View View Data Table This action will give you the following form The form will open with the Report Name filter dropdown open Uncheck Show All and then select the report you want to see EN View Extracted Data CA AJP Aging Summary Filter E 6 30 2008 AJR Aging Summary New Construction Overhead Remodeling S ofTotAsset Amou A R Aging Summary Filter Balance Sheet V Dept Allocation Dept User Allocation Formula Parameter 0 Inventory Stock Status by Item 71227 23 Inventory Stock Status by Item Filter Inventory Valuation Summary Inventory Valuation Summary Filter Open Sales Orders by Item 7 Open Sales Orders by Item By Rep 0 Open Sales Orders by Item Filter 0 0 0 Open Sales Orders by Rep v
42. d Inventory Valuation Summar Total Inventory Add New Parameter Delete Parameter Where Used Calculate You can limit the list of parameters in the drop down list by selecting a parameter source first on the top of the form After choosing the parameter it will be displayed on the form You can edit any aspect of it If you rename it and it 1s used by other parameters or indicators you will be warned and prompted to change the name in those formulas You can see where a parameter is used by pressing the Where Used button on the bottom of the form To undo your edits press the Esc key By closing the form selecting another existing parameter from the top of the form or pressing the Add New Parameter button the changes to the edited parameter will be saved Parameters with Errors In the Parameter Source dropdown list is a choice ERRORS If any errors exist with a parameter definition you will see the parameter name and the source of the error on the Select Existing Parameter drop down list Deleting Parameters To delete a parameter you either use the pull down menu and choose Setup Parameters or click on the Setup icon in the Parameters block on the Navigator You then select the existing parameter from the pull down list on the top of the form Pressing the Delete Parameter button will delete the parameter You will be prompted to delet
43. data was available to calculate the seasonal trend then the total should be 1 00 If no data was available then the total should be zero At this point you can change the value for any cell on the form but be sure you maintain the total to be 1 00 If you try to save when a total does not equal 1 00 you will be prompted to cancel or save anyway At any point you can press the Cancel button and any changes made will be discarded A number of other options exist on this form to make it easy to maintain the seasonal trends Some of these options include additional filters After selecting a filter press the Refresh button Filtering Options Total lt gt 1 List all records where the total does not equal one Total lt gt 0 List all records where the total does not equal Zero Show High Variances Enter a variance and show all records where a least one month has a value that exceeds the average of all non zero records by the variance amount Page 41 CleverQ Customization User Manual The Show High Variance option is useful to find records where values are significantly different than the current averages This allows you to focus any corrections you want to make on specific records that may be out of the ordinary The current average is shown in green on the bottom of the form and is the average of all non zero records for the selected Report Dept and Column type After clicking on the checkbox you will be pro
44. e Yellow ES Red Red Green E Green Green Red 2 3 J oil d Green Yellow Red Red Yellow Green White Black Red Red Black White Red Black Red Range Icons Each of the three ranges can have an icon These are usually one of the following You can assign these to each range and they will show up in various reports within the program You can actually have any icon that can be found in the WingDings font collection Use the Windows Character Map accessory to copy the icon and then paste it onto the control on this form A typical use for the icons is to assign a frown to a range which is not desirable Then with the report manager you can filter based on this icon Range Advice Tips For each of the three ranges you can enter text appropriate for the when a value falls within that range Normally the text is simply displayed as you enter it But these tips Page 33 CleverQ Customization User Manual or advice can be formatted in HTML to give them more formatting options as well as the ability to include one or more links to other reference material To teach you the basics of HTML is beyond the scope of this manual so it is recommended that you either get a book on the subject or visit anyone of the numerous sources of information on the web Since including a link to another website may be a common task particularly if you want to direct the user to more information the format to use 1s shown here
45. e range preceed the Formula with Ratio Add New Parameter Delete Parameter Where Used Calculate This flag can also automatically be checked from the Relationships form which will be covered in a later section of this manual When security is on editing of the Key Parameters are limited to those users in the Key Ratio Editor NOTE It is highly recommended that you do not change Key Ratios or Key Parameters since these have been defined as industry standards and if you use the Subscription Service you will be able to compare your data with industry benchmarks and get advice specific to these indicators ROA Parameters When creating a parameter based on the report Sales by Item Summary and you choose the column type ROA you will be prompted to automatically create a set of parameters for the selected row name Page 25 CleverQ Customization User Manual a Net Income Parameter I Tested Name SbIS Hardware ROA Note To overwrite aufo naming change the name after selecting the report row and column T Key Parameter A parameter can be mapped to a specific item on a report data T Null to zero source or a formula based on other parameters FT No Filters Map Parameter Report Name Ge by tem Summary gt Row Name Hardware Column Type Calculate Parameter E Formula Cunit ao toyjatde pura tar Era tos Note for Formulas When a parameter Formula is calculate period in a
46. e the parameter and if it is used by some other object you will be prompted to be sure you want to delete all related objects This can include other parameters that use this parameter in its formula any indicators and any gauges that depend on the parameter Page 24 CleverQ Customization User Manual Special Parameters Key Parameters A key parameter is defined as a parameter that is used in a key indicator formula You can manually set a parameter as a key parameter by checking off the Key Parameter Checkbox on the Parameter Detail form Puraiietay Damil Net Income Parameter T Tested Name Net Income Mote To orere aufo naning change he name affer selecting the repos an Mo Total revenue in an accounting period less all expenses during the same period If income taxes El are not deducted it is called operating profit for Loss as the case may bej Also called earnings net earnings or net profit parameter can be mapped to a specific item on report data source or formula based on other parameters Map Parameter Report Mame Profit amp Loss Row Mame Met Income Column Type Calculate Parameter Formula Mote For Formulas When a parameter Formula is calculated normally the results are calculated For each time period in a date range and stored to be used by other parameters or indicators To only have a Formula calculated once with the specified dat
47. e tree For example if you press the Dashboards button wait several seconds to several minutes for the tree to be built then press the Expand All button you will see something similar to the following Page 63 CleverQ Customization User Manual Di Clever Da ta KISS IR DashboardData gbd Company Expense 2 C Status Lights Expense Decet LJ Expense Advertising 61000 Advertising Expense Indicator Formula C Expense FloorPlan CI 62100 Total Expense Inventory Interest Indicator Formula FJ Expense Semi Fixed Indicator CL 63000 Total Expense Semi Fixed Indicator Formula Categories l El CI Expense Employee 66000 Total Expense Employee Indicator Formula Dl Expense Fixed 3 69000 Total Expense Fixed Indicator Formula C Net Ordinary Income Key Indicators LD Gross Profit Benchmark DP Gross Profit High LC Gross Profit Low D Gross Profit Max E Net Ordinary Income Indicator Formula l Expense Ratio Pie l E O Expense Ratio Advertising C1 61000 Advertising Expense Indicator Formula Top Bottom 3 l 61000 Advertising Expense Max Lae Up 3 Cl Gross Profit Indicator Formula H C Gross Profit Max Refresh E pre LU Expense Ratio Inventory Interest l l 62100 Total Expense Inventory Interest Indicator Formula Expand All 62100 Total Expense Inventory Interest Max 3 Cl Gross Profit Indicator Formula
48. ecast T 12 E Total Income Forecast T 12 E Total Income Forecast T 1 2 ff Total Income Forecast T 12 12 31 2009 11 30 2009 10 31 2009 9 30 2009 8 31 2009 7 31 2009 6 30 2009 136972 071543446 173346 657112928 65926 5005905523 75827 0304487792 56854 7972027973 29291 9935986646 31374 2242 730576 6 28 2010 8 34 44 AM 6 28 2010 8 34 43 AM 6 28 2010 8 34 43 AM 6 28 2010 8 34 42 AM 6 28 2010 8 34 42 AM 6 28 2010 8 34 42 AM 6 28 2010 8 34 41 AM 0 28125 0 359375 0 578125 0 28125 0 328125 0 28125 0 3125 E Total Income Forecast T 12 5 31 2009 14512 3003158236 6 28 2010 8 34 41 AM 0 515625 E Total Income 8 31 2010 0 00000001 6 28 2010 8 34 40 AM 0 046875 E Total Incorne 7 31 2010 0 00000001 6 28 2010 8 34 40 AM 0 0625 El Total Income 6 30 2010 0 00000001 6 28 2010 8 34 40 AM 0 046875 a Total Income 5 31 2010 51241 16 6 28 2010 8 34 40 AM 0 03125 B Total Income 4 30 2010 67975 5 6 28 2010 8 34 40 AM 0 015625 BE Total Income 3 31 2010 62235 25 6 28 2010 8 34 40 AM 0 015625 E Total Incorne 2 28 2010 39014 04 6 28 2010 8 34 40 AM 0 015625 Ez Total Income 1 31 2010 425171 6 28 2010 8 34 40 AM 0 015625 FE Total Income 12 31 2009 291929 6 28 2010 8 34 40 AM 0 015625 E Total Incorne 11 30 2009 35334 45 6 28 2010 8 34 40 AM 0 015625 BZ Total Income 10 31 2009 26183 66 6 28 2010 8 34 40 AM 0 015625 EF Total Income 9 30 2009 20515 24 6 28 2010 8 34 40 AM 0 015625 E Total Incorne 8 31 2009 25541 25 6 28 2
49. ectors The Stronger Ratio reflects a numerical superiority of current assets over current liabilities Generally the higher the current ratio the greater the cushion obligations and a Firm s ability to pay them Though every industry has its range of acceptable current ratios a ratio of 2 1 is considered desirable in most sectors This Ratio is a rough indication of a Firm s ability to service its current obligations However the composition and quality of current assetts is critical Factor in the analysis of a firm s Liquidity 0 0 There are two ways to choose colors You can actually click on the color itself and you will then get a color chooser dialog Page 31 CleverQ Customization User Manual Basic Colors Define Custom Colors gt gt Cancel And if that is not enough colors you can define custom colors and get even more to choose from EEHEEHE H Custom Colors asus Hue 50 Red 0 Sat 240 Green 259 Define Custom Colors gt Color Solid Lum 120 Blue 0 OK Cancel Add to Custom Colors The other way to choose a color is to use the Color Scheme drop down list just located below the bottom range advice With one selection all three ranges are colored Page 32 CleverQ Customization User Manual Color Scheme or click on bars to select colors y J a_j _ Sl Red Yellow Green 1 Custom Red Green Red f Yellow Orange Red 4 Red Orang
50. formula and the second part is are the values plugged into the formula The result is shown in the Value column Access Directory C Program Files Microsoft Office Orrice 101 Access Version 10 0 Workgroup File C Documents and Settings Larry Application DatalMicrosofk access System mdi Ini File msacc30 ini Profile Run Time Mo MDE False Program File Ci CleverQ Development CleverQPrgm 1 20 mde Application Code Data Cu Program Files Microsoft Orrice OFricel0 GBDLib dim Security Enabled False Current User WE Co Program Files Common Files Microsoft Shared VBA VE46iV6E6 DLL 01 ACCESS C Program Files Microsoft Office Office 10 msacc olbf9 0 stdole C WINDOW Sisystemde stdolez Hz 01 OBFC LIE C Program Files Cormon Files Intuit GuickBooks gbFc dilf1 0 Office C Program FilesiCormmon Files Microsoft Shared Office 101150 BLL 2 ADODE Ci Program Files Common Filesisvystermadolmsado27 Ebfz 7 DAD Ci Program Filesi ommon Files Microsoft Shared 0401D40360 DLL 5 0 Scripting COWINDOWSsystem32iscrrun dil 1 04 MSComctlLib CHWINDOWS system32 MSCOMCTL OCe 0 SHOoc y COWINDOW Si systems ierrame dil 1 14 SFtTreeLib 0 Cu Program Files Microsoft Office Officel iSftTree Ts66 LU 60 0cx 6 0 SftPrintPreviewLib 10 C Program Files Microsoft Office Office 10 S tPrintPreview 12860 10 0cx 1 01 CleverQLib Cu Program Files Microsoft Office Office 101 OBEA ib dinig 0 Application Title Current Clever Standard Edition ll Copy ab
51. four formulas are preceded by Ratio refer to the previous discussion regarding parameter formulas indicators and whole numbers Indicators An indicator is fundamentally created for the purpose of displaying a value on a gauge Indicators are typically what are known as financial ratios or financial indicators These ratios are used to evaluate the financial condition of a company and departments within a company Indicators fall into ACE Definitions categories and some typical categories include profitablity liquidity debt activity market etc A very common indicator or ratio is the current ratio which is calculated by dividing the current assets by the current liabilities These values come from a Balance Sheet Using this software and this example of Current Ratio the software will take the values from the QuickBooks Balance Sheet that gets extracted into the data table assign parameters to the data and then create a indicator based on these parameters The indicator can then be used to drive a gauge that show s up on a dashboard Key Indicators A key indicator is defined as an industry standard indicator and it is recommended that you do not change these indicators When security is on editing of the Key Indicators are limited to those users in the Key Ratio Editor NOTE It is highly recommended that you do not change Key Indicators or Key Parameters since these have been defined as i
52. g of the Department ColurmDate Date Range and DataType Period are done with the gauge specification Show All Fields This option is not used for gauges and is an option for Data Drilldown Add New Delete Preview Data Save and Close Data Views are based on pulling data from the report data which is where all data is stored after 1t gets loaded from the data sources The report data 1s structured with the following fields that are accessible with data views ReportName RowName ColumnDate ColumnType DataValue DataType The fields ColumnDate and DataType are not part of a data view definition since they are filtered by the gauge itself You must supply a Data View Name to be used when you attach it to a gauge property You must also define which Report Name you will be using for the data After you select a report the available row names and column types will be listed on the right of the setup form These are for reference only Normally a Data View will contain from 1 to 3 columns The columns are defined on the setup form with the first column being text and either coming from the row names or the column types You would normally enter a heading for column based on the specific data The second column is optional and is used to display the date You must enter a heading for this to appear The third column is numeric value and to display this column you must enter a heading You can opti
53. ge The text is blue represents calculated values based on the default settings for the gauge CleverQ Gauge Detail Gauge Name Current Ratio Initial Date Range Last Month C Lock Date Range Current Date Range Las Month Sec Group Admin Mote Gauge Type Circular Meter Description The circle meter shows the value in the center ofthe gauge The value is graphically shown with a needle The range ofthe gauge is based on the min and max of the indicator but will expand ifthe value goes out of range 4 purple arowshows the median or benchmark value 4 light blue bar shows the indicator s desired range A multicolored bar shows the ranges with the colors as specified for the indicator Data Type Monthly Indicator Name Current Ratio Key Indicator Indicator Formula Total Current Assets last Total Current Curent Value 0 0 Liabilities last Parameter Values Total Current Assets last 199 502 57 Total Current Liabilities last 33 444 62 Maximum Current Value 10 0 Though every industry has its range of acceptable curent atios a ratio of 2 1 is considered desirable in most sectors The Stronger Ratio reflects a numerical supe onty of current assets over current liabilities Current value 2 0 Generally the higher the current ratio the greater the cushion between current obligations and a firm s ability to pay them Current Value 1 1 Though every industry has its range of acceptable curent atios aratio of 2 1 is
54. ge is a week then the previous X weeks will be used Date ranges can be anything For example you could be working with a 15 day period Then the moving average will be based on 45 days if you enter 2 2 the current period 1s 3 periods and a period is 15 days so 3 15 1s 45 days Math Functions abs Function The absolute value function simply removes the sign from a number always returning a positive value abs 159 2 159 2 abs 159 2 159 2 exp Function The exponential function returns a value specifying e the base of natural logarithms raised to a power exp 5 148 41 int Function This function returns the integer portion of a number TI LZ AO ZO log Function This function returns the natural logarithm of a number Log 2 0 69 Y ou can calculate base n logarithms for any number x by dividing the natural logarithm of x by the natural logarithm of n as follows Log x 7 LOG nm round Function This function returns a value rounded to the specified number of decimal places roundi L2 A Oy 1 sgn Function This function returns a value indicating the sign of a number sonl zz gt Page 84 CleverQ Customization Manual Argument Returns Greater than 1 zero Equal to zero 0 Less than zero 1 sqr Function This function returns the square root of a number sqr 4 2 Page 85
55. il form to see the information associated with the indicator You can actually edit the indicator from here as well If the property 1s Contents as 1s the case with the Data Query gauge you can use an existing data view or create a new data view to be used for that gauge If you have not selected an existing data view pressing the 21 button will open the form to allow you to add a new data view Page 47 CleverQ Customization User Manual i Ganga Veta Press this button to add a new data view or edit the existing Gauge Name inti Date Range FEE pata Type Gauge Properties additional Drilldowns ar _ Ba _ E Data View Source e Top A P Vendors AJP Aging Summary System Top AiR Customers AJR Aging Summary System Top Salespeople Sales by Rep Summary System Top Salespeople by Month Sales by Rep Summary System For the purposes of gauge properties Ratio is used synonymously with Indicator Tested Add Mew Gauge Delete Gauge Where Used Save and Close Refer to the section Data Views for information about creating or editing the User Data Views System Data Views cannot be edited If the property 1s Data Drilldown as 1s the case with the most single indicator non chart gauges you can optionally use a Data View drilldown rather than the default line chart drilldown The advantage to this option is you could dr
56. illdown not only to the underlining data behind a gauge but you could open up an external program and view a data record which was used as part of the data source You can use an existing data view or create a new data view for the data drilldown If you have not selected an existing data view pressing the button will open the form to allow you to add a new data view Page 48 CleverQ Customization User Manual A Gass Dsi VI is vanced Selection i 2 Gauge Type Ege ee Select Gauge Type Initial Date Range fo T Lock Date Range Use drop down selection cata Type a sect Groups ang gauge type wi reset all properties Gauge Properties additional Drildowns Data Drildown OD For the purposes of gauge properties Ratio is used synonymously with Indicator Add New Gauge Tested Refer to the section Data Views for information about creating or editing the User Data Views System Data Views cannot be edited The Initial Date Range should be set but is optional If you do not set this date range and the dashboard the gauge is used on does not have a data range then all the data will be used to calculate the result The Initial Date Range is used when the dashboard does not have a date range But you can lock the data range by checking off Lock Date Range In this case the gauge will always have this data range independent of what data range the dashboard is set to Gauges will
57. lay the following form Enter New Data with or without a template Enter Existing Create New Template Edit Existing Template FS Mule Company Enter New Data f Edit Existing Data New Template Edit Template Select Tamplatas Po Delete Save Cancel Quit Ea gt AA A Reset the form With this form you can enter new data manually edit existing data and maintain templates for entering data This is the identical form that is part of the Data Package Creator used to enter data manually separate from the Dashboard software Refer to the manual CleverQ Data Package Creator available from the Windows Start All Programs CleverQ menu Quit the DPC or close the form in the QB Dashboard Purge Extracted Data At some time you may want to clear all the extracted data out especially if you have been testing and developing your visualizations The Purge icon in the Extracted Data block on the Navigator can be used for that Page 13 CleverQ Customization User Manual Analytical and Calculation Engine ACE The Analytical and Calculation Engine within the CleverQ Software takes definitions of parameters indicators and seasonal trends along with the extracted data and computes the values necessary to display on gauges dashboards scorecards and reports ACE Definitions ACE Nafinitian ACE Definitions Parameters Parameters as used in this sof
58. me J Net Ordinary Income Forecast T Let Working Capital Ratio LD New 4 DSL Days Inv New A DSL Inv Avg J New 4 DSL Inv Last CL New 4 D5L Marg O New 4 DSL ROA oof New 4 D5L Sales Cl New 4 D5L TO Highlight item above first then view Show Other OBC File Check items on this form then Export Indicators Parameter Sources Parameters Top Bottom Down Up Retresh Expand All Collapse All Select All HUE Page 65 CleverQ Customization User Manual If you choose Key Indicators only indicators marked as a key indicator will be displayed This also provides a way to see those parameters that are used by Key Indicators A special button is enabled in this mode labeled Set Key Parameters Pressing this button will flag all selected parameters as Key Parameters Refer to the earlier sections on Key Indicators and Key Parameters for more information about them Now let s say you want to turn the tree around and instead of seeing the parameters that are used by the indicator you want to see the gauges and dashboards that use the indicator Press the Bottom Up button and click on Indicators SRE OSI pS ER a CleverQiDatalkISS RY DashboardData gbd TL aa de a5 Indicators Gauges Dashboards UserID Active DL Gross Profit Forecast L e Gross Profit Forecast T Parameter El D Gross Profit Margin SOLITCES H C Interest Coverage Ratio H C Inv to COGS Rati
59. me F amp I Analysis Fel Wages of FRI Income F amp I Analysis Add Mew Indicator Delete Indicator Where Used Calculate Copy Indicator After choosing the indicator it will be displayed on the form You can edit any aspect of it If you rename it and it is used by a gauge it will be changed automatically in that gauge definition By closing the form selecting another existing indicator from the top of the form or pressing the Add New Indicator button the changes to the edited indicator will be saved To undo your edits press the Esc key Where Used You can see where an indicator is used by pressing the Where Used button on the bottom of the form Deleting Indicators To delete an indicator select the indicator first by using the drop down list on the top of the form Then if the indicator is not used by a gauge you can delete it by pressing the Delete Indicator button If it 1s used by a gauge you will need to remove it from the Page 37 CleverQ Customization User Manual gauge first Use the Where Used button to find out what gauges the indicator is used on Copying Indicators First using the Indicator Detail form select the indicator you want to copy Then on the bottom of the Indicator Detail form is a button labeled Copy Indicator Press this button and the current indicator will be copied completely and the new name will be the original indicator name followed by Co
60. meter can be mapped to a specific item on a report data F Null to zero source or 4 formula based on other parameters TF No Filters Map Parameter dus SPIPDLPATANIPIET Parameter Source mx ARR Add New Parameter Book Yalue of Equity EBIT Row Name Calculate Parameter EBITDA Formula Equity Multiplier Flt Emp Total Expense Flt Item COGS Forecast Flt Item Margin Flt Item Turnover wK Gross Profit Forecast L Gross Profit Forecast T Note For Formulas When parameter formula is calculated normally the results are calculated for each time Gross Profit SAAR period in a date range and stored to be used by other parameters or indicators To only have a formula Inv Turnover Target calculated once with the specified date range preceed the Formula with Ratio Net Ordinary Income Forecast T Net Working Capital Add New Parameter Where Used Calculate Quick Assets Total Assets Turnover Total Expense Fixed amp Variable Copy Parameter Total Expense Forecast T to Clipboard Total Expense Personal Total Expense SAAR Total Income Annualized Paste Parameter Total Interest Expense Forecast T at End of Last TOTAL Inv COGS Forecast Selection TOTAL Inv COGS TOTAL Inv Days Inv TOTAL Inv GM TOTAL Inv Gross Profit Cancel TOTAL Inv ROA To paste a parameter from the list you can do one of two things after you highlight the parameter 1 Press the Copy Parameter to Cli
61. mple Forecast Gauge Note Gauge Type Line Line Chart 2 Lines es 2 Lines Select Gauge Type Initial Date Range Last Fiscal Year Use this gauge for Use drop age selection or press Select Gauge Type Lock Date Range Monthly Data button Warning se E EE reset all properties security Gras Y Gauge Properties Ratio 1 Le Total Income X Total Income Forecast TB Q Months History for Forecasts 54 EE Pay Vet Total Income Forecast TB a oO F Z A o a La q a a q un WI play Periods to Display in Future Ratio Name Total Income Forecast TB Ratio Formula Total Income Forecast TB Tested Color Scheme or click on bars to select colors Units A WTA Ss DEN E o E Advice For differer The previous figure shows the special gauge with the property that allows the number of Page 81 CleverQ Customization Manual months to be used It references a indicator using the forecast function but leaving out the number of months of history following the TB operand Note Using the gauge property Specifying the Months History for Forecasts is only available for this one gauge and will have slower performance than including the extra operand with the function The calculated values cache is cleared when this property is used The next figure shows the standard drilldown for the gauge as defined in the previous figure It uses the other special p
62. mpted for a variance Enter a number and then press the Refresh button In the example below a variance of 2 was entered You can always see the variance value entered if you hover the mouse over the Show High Variance checkbox o El ES Craso ena Manual Evie Select Filter Report Name Seasonal Trend PL Dept lt No Dept gt Total lt gt 1 Y Show High gt RowName Column types Total cog NS Calendar Month Row Name gt 41320 Fold Downs PY 41700 Consignments PY _0 0 Total 70000 Other Income FY oook 0 69 aore eoe U QE MO 84000 Cash Log Short A 1 56 0 06 e400 CashLogfshor 000 00 0 00 0 00 co 0 00 oso 0 00 82000 Provision for Income Tax Lust 0 63 0 2 Total Other Expense E LM eal o1 21 166 en E 77 EE EE te tal gt Copy e Zer Record AP rent Average ooa 028 007 oo oo ome oo 008 ee 00 oi ou 102 F4 Sony Cran Current Average is the average of all non zero records i Paste Awg to Displayed u g g Totals should either be 1 00 or 0 00 Cancel Save Records for the selected Report Dept and Column Type Record 14 1 gt pips of 9 The circled entries above exceed the average for that month by more than the variance amount entered of 2 There are some other useful functions on this form that make it easy to make corrections The
63. n Cancel becomes the parameter SbIS Total 200 Used Marine Amount Math Functions You can use the standard math functions addition subtraction i multiplication division A raise to a power Other Functions There are a number of functions you can use in the formulas These are covered in Appendix A Aggregate Functions For parameters you have the added feature of including what is called Aggregate Functions These allow you to specify additional ways of using the parameters The Aggregate functions include Sum Avg Min Max Stdev Var First Last and Count Page 21 CleverQ Customization User Manual The function Sum is assumed as the default unless you specify something else The aggregate function is placed within the parameter s brackets following the parameter name andacomma For example Total Current Assets Avg This would calculate to average of the Total Current Assets parameter over the time period specified by the gauge when the indicator is calculated So if the data range was last year it would calculate the average monthly value of the parameter over the 12 month period last year If you did not include the Avg then the value calculated would instead be the sum over the 12 month period last year NOTE You can only use aggregate functions on parameters that are based on report data and not formulas Forecasting Functions Forecasting Functions Forecast P
64. n SP Comp Pea PE GP Comp Serv AdmSerw Inc Comp Serv Payroll of Serv Income Comp Serv Tech Sery Inc Company Return on Assets Current Ratio Days Inventory Dupont analysis Company ROE Expense Ratio Advertising Double click on gauge to see the gauge details and leave this Form open Select 4 specific gauge and press the SELECT button to see the gauge details and close this Form SELECT CANCEL You can edit any aspect of the gauge and if you rename it the change will automatically be applied to any dashboards using that gauge Saving your edited data is either done by pressing the Close button or choosing another gauge from the top drop down list Deleting Gauges To delete a gauge you either use the pull down menu and choose Setup Gauges Setup Gauges or click on the Setup icon in the Gauges block on the Navigator You then select the existing gauge from the pull down list on the top of the form you want to delete Then press the Delete Gauge button If the gauge is used on a dashboard you will not be able to delete it You must remove if from all dashboards before you can delete it Use the Where Used button to locate the dashboards that reference the gauge Page 53 CleverQ Customization User Manual Data Views Data Views is a mechanism to primarily display textual data either on a gauge or as part of a drilldown from a gauge The Data Query gauge has a property called Contents
65. n User Manual Forecasting Functions Forecasting Functions Forecast Predicts a value based on history and seasonal trends or linear regression Return the seasonal trend fraction Calculates the moving average The details of these functions can be found in Appendix A NOTE You can only use forecasting functions on indicators that are based on parameters that are based on report data and not formulas Ranges Many of the gauges are setup to display different colors and tips depending where the value falls within a set of ranges Three ranges are available and for each range there is a lower and upper threshold In the example in the next figure the lowest range is red and has the range from 0 to 1 1 The middle range is yellow and has the range from 1 1 to 2 The highest range is green and has the range of 2 to 10 Many gauges will display the works Out of Range if a value falls outside these three ranges When a value falls within a range that has advice or a tip that tip will be made available to the user The range values can be parameters or formulas as well Use the button adjacent to the range value to choose a parameter All the functions that can be used as well The functions are evaluated on the gauge and are sensitive to the date range and department selected as well Range Colors Color Scheme or click on bars bo select colors mu Patent Pending 10 0 considered desirable in most s
66. n the gauge type For example some gauges only display one indicator ratio so the property list only shows one item Other gauges show multiple indicators ratios so there will be a property for each indicator If there is a label property as in the example below you can actually leave that blank and the indicator ratio name will be used for the label Page 46 CleverQ Customization User Manual i Ganga Ue tl Total Income Pie Advanced Selection Gauge Name Total Income Pie Note 54 Gauge Type Pie Chart PRE Initial Date Range Last 12 months T Lock Date Range Data Type Monthly 7 Press this to go to the indicator security Goup Gauge Properties additional Drildowns Met Ordinary Income COGS Tatal Exp Employee ExpiFixed amp Variable Met Ord Income Total COGS Emp Exp Fixed amp Var Exp ololo ojofofo Jo bel ke 4 ala fa fa alaala Sis En die For the purposes of gauge properties Ratio is used synonymously with Indicator T Tested Add New Gauge Delete Gauge Where Used Save and Close If the property 1s a ratio indicator you can use the drop down list to the right of the property name to select a ratio indicator For labels and some other properties the pull down list will be blank If the property is a ratio indicator there is a small button with a magnifying glass all the way to the right Press this button to bring up the Indicator Deta
67. nary Income Indicator Formula E Expense Ratio Pie E Expense Ratio Advertising Top Bottom 61000 Advertising Expense Indicator Formula Down Up l 61000 Advertising Expense Max Gross Profit Indicator Formula Refresh 3 3 l Gross Profit Max 3 E Expense Ratio Inventory Interest 62100 Total Expense Inventory Interest Indicator Formula 62100 Total Expense Inventory Interest Max Collapse All l Grass Profit Indicator Formula l l Gross Profit Max Il Highlight item above first then view Set Key Parameters Show Other QBO File Check items on this form then Export Indicators Parameter Sources Parameters acl ER Rs naa Es asia Expand All Select All ERA If you want to see the objects in another file at the same time press the Show Other QBD File button Select the file you want Then a second window will appear just like the first but without some of the buttons Page 69 CleverQ Customization User Manual 9 cuya File View Edit Setup Help FE C Program Files CleverQ Data CleverQDataSample qbd E Dashboards UserID Active 4 Gauges Ratios Parameters in Dashboards Gauges Ratio Categories Ratios Parameter Sources Parameters Top Bottom Down Up Refresh Expand All Collapse All Select All E Company Performance Status Lights Expense Ratios 5 E E E M Expense Ratio Fixed
68. ndicator You can use the pull down list to choose an existing category or just type in a new category To remove a category from an indicator press the delete button to the right of the category Formulas on Formulas Since you can create an indicator that depends on one or more parameters and those parameters could have a formula and that formula can contain other parameters that in Page 35 CleverQ Customization User Manual turn can have formulas the precedence of calculating these formulas are critical Be sure to test out your indicators parameters and parameter formulas before you deploy your solution Calculate and Testing After you have named your indicator and created a formula you can check to see if the indicator works Use the Calculate button This will bring up the Formula Calculator form where you can select a data range dept and period and then see what the result is You can change the date range department or period and the result will recalculate The software will remember you last settings for the next time you use the formula calculator a Ex Current Ratio g Indicator Mame Current Ratio W key Indicator Indicator Formula Total Current Assets last Total Current Liabilities last Formulas are based on parameters Parameters must Color Scheme or click on bars to select colors ie Date Range ESS ue Advice For dif maet Maxi Data Type Monthly 10 0 ide
69. ndustry standards and if you use the Subscription Service you will be able to compare your data with industry benchmarks and get advice specific to these indicators Creating Indicators To create an indicator you will either press the Indicators icon on the ACE Definitions block on the Navigator form or choose from the pull down menu Setup Indicators The Indicator Detail form will appear and will be blank To add a new indicator you press the Add New Indicator button To choose an existing indicator use the pull down list on the top of the form labeled Select Existing Indicator Page 27 CleverQ Customization User Manual The following figure shows this form with the existing indicator Current Ratio selected Al adica Vail Liguidity Analysis Current Ratio Indicator Mame Current Ratio MW Key Indicator Indicator Formula Total Current Assets last Total Current Liabilities last Formulas are based on parameters Parameters must be enclosed in Functions 49 Min Max Stdey War First Last Count can Follow the parameter name e g Total Assets vg The nl Function Sum is assumed if no Function is specified Color Scheme or click on bars bo select colors Units Decimals 1 Mult gt Fatent Pending Advice For different ranges Plain text or HTML Maximum ue Though every industry has its range of acceptable current ratios a ratio of 2 1 is 10 0 considered desirable in most
70. o Parameters Inventory Equity LD Inventory to Working Capital Ratio E Met Ord Inc of GP Indicators et Ordinary Income l C Net Ordinary Income oo C Company Income Cl Company Income 1 CL Company Income Larry C Company Income Robert C Status Lights Expense C Company Expense 2 Dashboards 2 7 Company Expense 3 3 Cl Company Expense Robert Net Ordinary Income of Total Income J Net Ordinary Income Forecast T A Net Working Capital Ratio Top Eottom D New 4 DSL Days Inv Down Up H C New A DSL Inv Awg 2 New A DSL Inv Last Refresh A New A DSL Marg H C New A DSL ROA Expand All H C New A DSL Sales H C Mew A DSL TO Collapse Al H C New A GAS Days Inv H C New A Gas Inv avg select Al 2 New Gas Inv Last Highlight item above first then view Set Key Parameters Show Other OBO File Check items on this form then Export Key Indicators ages ES na ee ls FEI FRE BE ELA Page 66 CleverQ Customization User Manual In this view you see the indicators in red and if you expand an indicator the gauges that use that indicator are shown as well as the dashboards that use those gauges Page 67 CleverQ Customization User Manual Exporting Exporting of parameters indicators and gauges to another QBD file is available from the same form that displays the object relationships discussed in the previous section Some additional buttons are available
71. ofit Forecast TB for Seasonal Trend with Best fit Forecasts have to depend on history and you specify the number of months of history starting with the prior month from the current date by entering another operand with the forecast function You can enter any number of months For example Gross Profit Forecast L 6 will calculate a linear trend using the last 6 months of actual data Gross Profit Forecast TB 12 will calculate a best fit seasonal trend forecast using the last 12 months of actual data Page 80 CleverQ Customization User Manual If the number of months of history is left blank then the value can be specified in one of the gauges that is commonly used to display forecasts That gauge is the Line Chart 2 Lines A property called Months History for Forecasts is available and you can enter a number here This is only used if a underlying formula used in the gauge has a forecast function without the number of months of history specified Gauge Name je Gauge Type Line Chart 2 Lines t Select Gauge Type Initial Date Range Lock Date Range Use drop down selection or press Select Gauge DataType ooo oo igi kie CE pet OA a aug pe reset all properties security Grup SS Gauge Properties Ratio 1 FRE EE for Forecasts p Periods to Display in Future FT Tested Add New Gauge Delete Gauge Where Used WIEN Save and Close Here is an example 3183 Gauge Mame Exa
72. olumn type at this point although you can if you want to work with a smaller list of values You will also need to make any filter selections After making your selections press the Refresh button You will then see a list of values representing the seasonal trends Page 40 CleverQ Customization User Manual Seasonal ena sandal erhi Select Filter Report Name Seasonal Trend PL ef Dept PF Total lt gt 1 P Show High Clear RowName Column type Total lt gt PE Refresh Company Column Calendar Month Row Name Type A A A A ECS US SU DOS ECM ECS D pronto Designineome M oo oo oo ooj 0 12 oos ou oo o 0 00 ooe 100 00 horo Labor Income 0 08 0 06 00 007 ose om oo 0 08 010 0 13 008 0 10 1 00 0 00 0199 Less Discounts given 000 000 o oo o oo 0 00 0 00 o oo 100 Go 0 00 0 00 000 1 00 0140 Materials Income oo oo ooa 0 03 0 03 0 06 oroj ou ooa 02 0 18 010 100 000 Joso Subcontracted Labor nco oo oo 00 000 0 00 00 oo ou 0 07 04 01 100 000 fetal 40100 Construction Incomel 0 06 0 06 006 0 05 006 0 06 0 06 00 Go 014 01 01 100 000 D H0520 Permit Reimbursement inc 000 02 o oo 0 00 o oo 000 000 040 o oo 0 16 016 000 100 000 40530 Reimbursed Freight amp Dei 000 o co o oo 0 00 o oo 000 0 46 1 000 04 00 000 100 0 00 fetal 40500 Reimbursement Inco 0 00 0415 000 0 00 0 00 0 00 01 02 o oo 0 28 0 11 000 100 000 Jrotalincame 00
73. on a dashboard You can even use the right mouse click to access a working gauge sub menu Editing Gauges To edit a gauge you either use the pull down menu and choose Setup Gauges Setup Gauges or click on the Setup icon in the Gauges block on the Navigator You then select the existing gauge from the pull down list on the top of the form or use the Advanced Selection button we Gaisa Were BE a EE ee 5 esi Gage Name Ct ae Gauge Type Select Ge Type Initial Date Range F Lock Date Range Use drop down selection or press Select Gauge Data Type od Type button Warning securty Gruss A ero gage rev reset all properties Gauge Properties Additional Drilldowns mme pa NS AMP O AA iik The pull down lists all gauges in alphabetical order The Advanced Selection button will display a new form that will give you many options for finding an existing gauge Page 52 CleverQ Customization User Manual Ajansa Gansa alee ring All Clear Filters For a specific Dashboard For a specific Department For a specific Security Group For a specific Gauge Type Contains a specific Indicator Contains an indicator in the specific Indicator Category AIP Days Outstanding AIP to Sales Ratio AIF Top Payables AR Aging AR Days Receivable AIR Top Receivables AIR Turnover Acid Test Ratio Advertising Expense Ratio Comp Expense Payroll Comp Mgmt amp Admi
74. onally enter a format from the dropdown list of enter your own using the symbols below Symbol Description Page 55 CleverQ Customization User Manual Symbol Description period Decimal separator Separators are set by double clicking Regional Settings in Windows Control Panel comma Thousand separator 0 Digit placeholder Display a digit or Q Digit placeholder Display digit or nothing Display the literal character Percentage The value is multiplied by 100 and a percent sign 1s appended E or e Scientific notation with a minus sign next to negative exponents and nothing next to positive exponents This symbol must be used with other symbols as in 0 00E 00 or 0 00E00 E or e Scientific notation with a minus sign next to negative exponents and a plus sign next to positive exponents This symbol must be used with other symbols as in 0 00E 00 The data can be sorted by one of the columns either in ascending or descending order Filters can be applied to the DataValue RowName and ColumnType fields The filter operator can be selected from the appropriate dropdown list and for data values you might want to filter for positive negative zero or nonzero values or some other criteria Leaving the filter operator and filter box blank will cause no filtering to occur Filtering for RowName or ColumnType includes selecting a filter operator and one or more appropriate entries Each ent
75. or no value This is different than saying the calculation returns a zero value which could be a real result When a parameter is null any other parameters or indicators that depend on Page 15 CleverQ Customization User Manual the null parameter will also be null and any gauges will show a warning usually saying there is insufficient data to display the gauge If you check off Null to Zero the null result is returned as a zero and the dependent objects will calculate and display without any warnings The second option is No Filters There may be filters defined for various elements in the system and sometimes you might want to calculate a percentage where the numerator is a filtered value but the denominator isn t If you create an parameter with no filters then that parameter could be used as the denominator Parameter Formulas The parameter formula can be as simple or as complex as you want Formulas are based on other parameters and are referenced by the parameter name enclosed in brackets Formulas can also contain functions Functions include simple math functions other functions aggregate functions special functions and forecasting functions There are some things you need to consider when creating a parameter formula and the best way to explain it will be with an example Consider the following Excel worksheet containing some data in the green cells There are two rows of data labeled Row A and Row B
76. ormula with Ratio Add New Parameter The form is blank initially and you either need to select an existing parameter from the drop down list on the top of the form or press the Add New Parameter button The previous figure shows the form after pressing the Add New Parameter button Creating Parameters To create a new parameter press the Add New Parameter button on the Parameter Detail form You must enter a name for the parameter and then either a reference to a Report Name Row Name Column Type or a formula created from other parameters The Parameter Name must have some meaning since it will be referenced in other places in the software If you initially leave the Name field blank and select a Report Name Row Name and Column Type the name field will automatically fill in using the abbreviation for the Report Name Row Name and if selected the Column Type separated with an You can use this name or type over it and rename it If you want to create a parameter from other parameters rather than from a report source then leave the Report Name Row Name and Column Type blank and enter your formula Calculation Options Two options exist on this form which will also affect how the parameter is calculated The first is Null to Zero When a parameter is calculated and no value exists usually because the underlying extracted data 1s not available the parameter value is a null
77. ory and then indicator sorted alphabetically in that way If an indicator belongs to more than one category it will show up for each category If may show up more than once but it is the same indicator If you start typing the indicator name the list will start based on what you type Press the F4 key to open up the list 1f you have not clicked on the down arrow of the control Sator armil Indicator Marne Key Indicator Expense Fixed Company Expense Analysis Expense FloorPlan Company Expense Analysis Expense Semi Fixed Company Expense Analysis Expense Ratio Advertising Company Expense Analysis Expense Ratio Employee Company Expense Analysis Expense Ratio Fixed Company Expense Analysis Expense Ratio Inventory Interest Company Expense Analysis Expense Ratio Sermi Fixed Company Expense Analysis Met Ord Inc of GP Company Expense Analysis Met Ordinary Income Company Expense Analysis Met Ordinary Income of Total Income Company Expense Analysis Total Expense Annualized Trend Company Expense Analysis Total Expense of Total Income Company Expense Analysis FRI Total Income F amp I Analysis Document Fees Income F amp I Analysis Fiel Ext Sere Cont Margin F amp I Analysis Fi Ext Service Cont Income F amp I Analysis FEI Gross Margin F amp I Analysis F amp I Gross Profit F amp I Analysis F amp I Het F amp I Analysis F amp I Profit of Ry Sales F amp I Analysis F amp I Profit per Retail Unit Sold F amp I Analysis Fl Reserves Inco
78. ou first select a Parameter Source and then select the parameter A Parameter Source is either a formula or the report name the parameter is based on To paste a parameter from the list you can do one of two things after you highlight the parameter Page 28 CleverQ Customization User Manual 1 Press the Copy Parameter to Clipboard and then when you go back to the indicator detail form and paste it into the formula where you want it 2 Press the Paste Parameter at End of Last Selection and the parameter will go at the end of the formula on the indicator detail form If you need a parameter you have not create yet you can create a parameter based on a report on the fly by pressing the Add New Parameter button on the Select Parameter form In this case you the Parameter Select form will change it contents and you can then select a parameter source row name and optional column type Then press either the Copy Parameter to Clipboard or Paste Parameter at End of Last Selection buttons and the new parameter will be available It will automatically be created so it can be used again It will automatically be named with an abbreviation for the parameter source followed by a then the row name If a column type is included another is added and the column type is added If a user chooses to create a parameter on the fly and a parameter already exists for the selection a new parameter will not be created and
79. ou select a branch that is a gauge indicator or parameter you can then press the View button and the detail form for that object will be displayed You can even edit the object at this point If you make any changes that could affect the tree press the Refresh button to rebuild the tree Objects at the root level are listed alphabetically So if you are interested in a particular indicator press the Indicators button and you will get an alphabetical list of indicators Expand any of the indicators to see the parameters that are used by that indicator Relan nus Pc CleverO Data KISS RYDashboardData gbd il Indicators Parameters in AMS a pen E a El alicia Es ns AT CL Forecast Payroll Expense T CL Forecast Semi Fixed Expense T Bebe LE H Gross Profit Cl Gross Profit Annualized Trend E Gross Profit Forecast L Gages LD Gross Profit Forecast T H C Gross Profit Margin Indicator CI Interest Coverage Ratio Categories l 62100 Total Expense Inventory Interest Indicator Formula 69900 Other Interest Indicator Formula EBITDA Indicator Formula C 62100 Total Expense Inventory Interest l 69100 Total Expense Depreciation key Indicators LU 69900 Other Interest oof 90000 Total Taxes Det Ordinary Income Inv to COGS Ratio Cl Inventory Equity J Inventory to Working Capital Ratio Net Ord Inc of GP Cl Net Ordinary Income Met Ordinary Income of Total Inco
80. ove to Clipboard Log Report Yiew Cache fe Debug Mode OK To stop the Debug Mode remove the check in the checkbox by clicking Debug Mode will stay in effect until you close the program also It 1s reset whenever the Page 75 CleverQ Customization User Manual program first starts up There is additional overhead when using this mode so calculations will take a little longer Page 76 CleverQ Customization User Manual Appendix A Built In Functions Functions are predefined formulas that use arguments to calculate simple or complex values Functions have a name followed by parenthesis The arguments used in the functions are enclosed in parenthesis separated by commas if there is more than one For example round 123 456 1 Functions can also be nested In this case the result of one function is used as an argument for another function round abs 123 456 ij 10 Functions can be broken up into categories These categories include the following e Special Functions e Math Functions e Logical Functions e Financial Functions There are other functions available but were chosen not be included here since they most likely would not apply to a business application like this Note Information about some of these functions come from the Microsoft Developer Network MSDN and documentation for Visual Basic for Applications You may want to refer to these sources for additional information and exam
81. parameters Care must be taken when using aggregate functions with parameter formulas For example if you used the formula A Last B Last you would get the same value as if you used A B But if you preceded the formula with Ratio like this a Ex Parameter Name Example3 To orere aufo nanng change EN 4 parameter can be based on a specific item a Formula based on okher parameters Report Name Row Mame Data Type Monthly Column Type Formula Examples x i OR 5 Formula Ratio 4 Last B Last Tested L You would get the result as shown in cell M8 in the worksheet which is the calculation for the last month in the date range Obviously it makes sense to test your parameter formulas out and make sure you use the correct option Parameters If you know the parameter you want to include you can just type itin You can also paste a parameter into a formula by selecting a parameter from a list that is opened when you press the button On the form that opens you first select a Parameter Source and then select the parameter A Parameter Source is either a formula or the report name the parameter is based on Page 19 CleverQ Customization User Manual a Equity Multiplier Parameter Tested Name Note To overwrite auto naming change the name after selecting the report rom and column I Key Parameter 4 para
82. pboard and then when you go back to the indicator detail form and paste it into the formula where you want it 2 Press the Paste Parameter at End of Last Selection and the parameter will go at the end of the formula on the indicator detail form If you need a parameter you have not create yet you can create a parameter based on a report on the fly by pressing the Add New Parameter button on the Select Parameter form In this case you the Parameter Select form will change it contents and you can then select a parameter source row name and optional column type Then press either the Copy Parameter to Clipboard or Paste Parameter at End of Last Selection buttons and the new parameter will be available It will automatically be created so it can be used again It will automatically be named with an abbreviation for the parameter source followed by a then the row name If a column type is included another is added and the column type is added See the following figure for an example If a user chooses to create a parameter on the fly and a parameter already exists for the selection a new parameter will not be created and the existing parameter name will be used Page 20 CleverQ Customization User Manual Sr Parameter Parameter Source Sales by Item Summary Row Marne Add New Parameter Total 200 Llsed Marine Column Type Amount Copy Parameter to Clipboard Paste Parameter at End of Last Selectio
83. ples Special Functions getdata Function This function will return the value from the data table just like you specified a parameter but without manually creating the parameter GetData ReportName RowName ColumnType For example GetData Profit Loss Gross Profit Required for this function are ReportName and RowName You must enclose the items with double quotes The function can also include a function and operands just like you would specify with a parameter name Page 77 CleverQ Customization User Manual GetData ReportName RowName ColumnType Funct Operandl Operand2 For example GetData VECTOELE Loss Gross Pror POrLecast EI ALIAT Note there is a place holder for ColumnType with double quotes The function will actually create a new parameter based on this function if it previously did not exist The notes section for the parameter will indicate that is was automatically created and when numdays Function This function will return the number of days for the current indicator or parameter being calculated numdays prev Function The Prev function will return the value for the previous period based on the data type and is used in conjunction with any of the aggregate functions The word Prev can be place before or after the aggregate function and can have spaces or not Some examples are shown below The Aggregate functions include Sum Avg Min Max Stdev Var First
84. py You can then edit this indicator and change the name Seasonal Trends On the Navigator screen you will find an icon labeled Seasonal Trends in the ACE Definitions block Clicking on this icon will display the following form a Colcotete Seasonal Maris ES ae Sel Data Source 7 Yearly Weights 3 vrs loaded E ven Valuation Sunmary Ve es 3 y loaded Pavroll Summary Yearly Weights 1 yrs loaded dot Yay Weis 3 load F alesby Ren Surmay 17 3 y loaded Yearly Weights 3 yr loaded monthly data is usually required to Year 1 Weight Year 2 Weight Seasonal Select All calculate seasonal trends 1 o Trends Purge Clear Selections Note Two prior Fiscal years of Default Default Calculate Manual Edit OK With this form you can calculate and store the seasonal trends for any of the data sources listed This will include some QuickBooks reports as well as all the Excel Database and External Data Package data sources you have specified Page 38 CleverQ Customization User Manual Calculating Seasonal Trends Default Year Weighting You should have at least 2 prior fiscal years of monthly data for this seasonal trend function to work properly but this is not necessary The standard calculation uses a weight of 1 for the first year and second year You can change the weight for each year independently Enter a zero if you do not want to use a year Another common choice is to have
85. r s 132 ratios 51 gauges OK Testing and Debugging As part of the process of creating dashboards and scorecards and defining parameters indicators and gauges you must spend time testing what you have done In fact this process is no different than designing software except instead of working with a computer programming language you are instead working with higher level objects Issues can be the result of definition formula or data errors To assist in debugging errors or just performing validation some features of the CleverQ software make this process a bit easier Aside from the obvious functionality in the software where you can view results on a gauge dashboard scorecard the Calculate button found on the parameter and indicator forms can be useful There are also two other areas functions that can be used for error checking the Cache and Debug mode Data Cache The Data Cache consists of three parts e Extracted Data e Formulas e Calculated Data Extracted Data Formulas Calculated Data Data Cache The Extracted Data part has been previously covered in an earlier section The Formulas part of the Data Cache is a repository of all the formulas used for the visualizations Since so many calculations can occur and to maintain good performance the results of the calculations are stored so they are quickly available when needed Whenever a calculation is required by the VP the Cache is checked first to see if the
86. r the data drilldown You can preview the data using the Preview Data button on the bottom of the form which will cause the result of the data view to show up on a new form in a list format IPS Total COGS Total COGS Total COGS Total COGS Total COGS Total COGS Total COGS Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Total Expense Reset Table to Default Preview Print Export Data View Details Show as Pivot Table 3 23 2009 3 25 2009 3 26 2009 3 27 2009 3 26 2009 3 31 2009 3 31 2009 1 31 2006 2 28 2006 3 31 2006 4 30 2006 5 31 2006 6 30 2006 7 31 2006 9 31 2006 9 30 2006 10 31 2006 11 30 2006 12 31 2006 1431 2007 2 28 2007 3 31 2007 4 30 2007 5 31 2007 6 30 2007 1 243 398 372 72 089 90 979 051 642 146 435 226 544 203 498 227 558 207 919 245 722 234 632 294 462 165 139 229 736 161 546 203 326 195 019 314 723 163 893 171 933 167 505 222 835 Date Value ColumnType DataType 2 A dd dr dd dl dl dd dd Sd dl dl dr NN NN ND By selecting a single record and pressing the View Details button you can drilldown into another program to see that specific data
87. red de Formula Total Current Assets last Total Current Liabilities last High gt 2 Low i OZ 0 0 Desired Range dt ER Sencha PL Categories Note Indicator of a Firm s ability to meet short term financial oblig amp tions it is the ratio of current assets to current liabilities Though every industry has its gl cee range of acceptable current ratios ratio of 2 1 is consid red desirable in most sectors Since inventory is included in current assets acid test ratio Add Mew Indicator f Delete Indicator Where Used Copy Indicator Finally if everything looks good you can keep track of the indicators you have verified by checking off the Tested checkbox on the bottom of the Indicator Detail form There is a place to add notes for the indicator to assist in explaining the indicator By closing the form selecting an existing indicator from the top of the form or pressing the Add New Indicator button the existing indicator will be saved Editing Indicators To edit an indicator you either use the pull down menu and choose Setup Indicators or click on the Setup icon in the Indicators block on the Navigator You then select the existing indicator from the pull down list on the top of the form You can also choose a Page 36 CleverQ Customization User Manual category first before you select an existing indicator The following figure shows you that this pull down list organizes the list by categ
88. redicts a value based on history and seasonal trends or linear regression Return the seasonal trend fraction Calculates the moving average The details of these functions can be found in Appendix A NOTE You can only use forecasting functions on parameters that are based on report data and not formulas Formulas on Formulas Since you can create a parameter that has a formula and that formula can contain other parameters that in turn can have formulas the precedence of calculating these formulas are critical Be sure to test out your parameters and parameter formulas before you deploy your solution Testing Parameters After you have named your parameter and either selected a report or created a formula you can check to see if the parameter works Use the Calculate button This will Page 22 CleverQ Customization User Manual bring up the Formula Calculator form where you can select a data range dept filters and period and then see what the result is You can change the date range department filters or period and the result will recalculate The software will remember your last settings for the next time you use the formula calculator a Net Income Parameter I Tested Name Net Income Note To overwrite auto naming change the name after selecting the report row and column Total revenue in an accounting period less all r expenses during the same period If income taxes Key Parameter are not deducted it
89. roperties for the 2 line gauge which displays additional periods of data in the past and future Patios Lo 1500000 1600000 1400000 1200000 1000000 E Total Income Forecast TB 800000 f Total Income X 600000 400000 200000 Ca 0 Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2007 2008 2009 Reset Chart Load e x Save Preview Print Export View Details One further option exists with the number of months of history If neither the formula has the value nor the gauge does not contain the value then the system wide preference value is used as entered on the preferences form Page 82 CleverQ Customization Manual Company Name ll Departments are defined by 7 QuickBooks Company C Program Files Dealership Software Data RCD Sales Co LTD E Classes File 2009 gbw C Allocations Wot Defined First Month of Fiscal Year Jan Setup Departments Logo D CleverQ Data kISS Clever Q Logo jpg za ia M check for updates when program starts Security Enabled E M Lise Windows User ID CLEVERQ ina osen sy zeo se YOUR BUSINESS CUE M Start Program with Navigator Subscription ID support ID And if that preference is not set then all historical actual data is used Trend Function The Trend function is used to return the seasonal trend amount
90. ry must be enclosed by single quotes and if you use the operator In List each entry in the list must be enclosed by single quotes and then each entry separated by commas To make it easy double clicking on either the reference list for row names or column types will copy that entry into the appropriate filter box Page 56 CleverQ Customization User Manual Daia Wie Saig Row Mames For reference Total 69500 Utilities El Total 69700 Other Fixed Data View Mame best Report Mame Profit amp Loss Column 1 Text Column 2 Date Column 3 value Total 70000 Other Income Total 72000 Interest Income Total 80000 Other Expenses Format Total 90000 Taxes Column Source F Row Names ColumnDate is used if a Datavalue is used if a Total 95000 Other Taxes heading is specified heading is specified Total COGS Column Types Total Expense Total Income Headings Item Date Value Sorting Total Other Expense Total Other Income Filters ki RowMame In List Total COGS Total Expense Total Income ColumnType Filtering of the Department ColumnDiate Date Range and DataType Period are done with the gauge specification Column Types for reference M Show All Fields This option is not used for gauges and is an option for Data Drilldown Add Mew Delete Preview Data Save and Close And finally there is one last option that would provide the ability to show all fields fo
91. s using the Define Departments button 2 Either define allocations using the predefined department allocations using the Allocations button and optionally define allocations using user defined allocations using the User Defined Allocations button SEE amen Print Report Define Departments Pressing the Define Departments button on the Setup Departments menu will display the Define Departments form Here you can add or delete departments Page 6 CleverQ Customization User Manual Usina Use pie ts Department Name DETEN IN fa OOS Par La DE E IN Add new departments on the bottom of the list To rename a department you must first delete it and then add it in with the new name To delete a department select the department above and press the Delete key on your keyboard Deleting a department will delete all existing data for that department as well as all setup information Any gauges assigned to that department will be reset to no department Record 14 4 gt bl ba of 5 Allocations Pressing the Allocations button on the Setup Departments menu will display the Department Allocations form Here you enter values from company financial reports that represent the contributions from various departments Page 7 CleverQ Customization User Manual O Veuarinent sluentions Gross Gross Employee Avg of a Department Gross Sales Sales Gross Profi
92. t Profit Employee Expense Building SqFt Building Land 5q Ft Land Land Avg Lo Yo Expense Yo Sq Ft Sq Ft Building Alloc Pedy shop HI pa __ 300 000 2 8 0 000 1 2 eooo som aol iva 1 00 08 34 pas 700 000 soel 20 000 iore 70 000 103 ef 4 1 00 2 0 71 files 10 000 000 4 7 prenons 72 6 280 000 si s Goo 07 150 000 53 6 47 1 64 0 fee 600 000 sie 300 000 12 1 150 000 22 1 6 000 40 7 120 000 42 9 416 20 3 11 800 000 100 0 2 480 000 100 0 680 000 100 0 14 750 100 0 280 002 100 0 100 0 100 0 Cancel Save vr Record I4 lt 1 gt i gt i lt os The blue numbers on the form are calculated and are available as parameters for calculation of indicators If gauges are setup for departments then the Avg Alloc factor 1s used to calculate the value for the gauge Bessey Vall Dept Allocation Gross Profit Parts Parameter Name Gross Profit Parks Mote 4 parameter can be based on a specific item on reportidata source or a Formula based on other parameters Report Mame Dept Allocation Row Name Gross Profit Column Type Body Shop Formula TF Tested Add New Parameter Delete Parameter Where Used Calculate The Column Type dropdown will show you a list of departments User Defined Allocations Pressing the User Defined Allocations button on the Setup Departments
93. ta type and always goes to the previous year Change Function If you create a formula Total Current Assets Total Current Assets prev the result would be the difference between the current time period and the previous time period You can specify this formula easier by using the change function For this example the new formula would simply be Total Current Assets change If no previous value is available the current value is returned Forecasting Functions The forecasting functions have a different format than the other functions and are similar to the aggregate function format These functions are used within the brackets that define a parameter The function is placed within the parameter s brackets following the parameter name andacomma For example Total Current Assets Forecast There may also be additional operators after the function name separated by commas Forecast Function The forecast function is used to calculate or predict a value for a specific parameter using regression or curve fitting formulas The function can be used in conjunction with the seasonal trend values previously calculated for the parameter The function is based on one of four types of equations Forecast Function Regression Formulas i nee Logarithmic Curve y h In x Which of these methods is determined by the operand following the function name The following examples shows you would use the function for the parameter
94. take a long time depending on the number of formula entries so the user can set a time limit on the calculations It should be noted that whenever data is extracted or ACE definitions are changed the formula results are purged in the Calculated Data You can manually force a purge of the Page 74 CleverQ Customization User Manual calculated results by pressing the Purge icon in the Calculated Data block on the Navigator Viewing the cache may be useful for troubleshooting the calculations Debug Mode Seeing even more detail than the Cache 1s available in the Debug Mode On the System Information screen there is a checkbox labeled Debug Mode To start the debug mode click on this checkbox This causes the Cache and Error tables to be cleared Now do something that will require calculations like displaying a gauge or dashboard etc After that action is complete come back to the System Information form and click on the label Debug Mode leave the checkbox as is You will now see a table containing the following GaugeName Parameter Formula DataRange DataType Value ErrorDescription and DeptName Several entries may exist for one formula since in the debug mode each parameter in the formula is displayed on a separate row so you can see how the calculation occurred for each element of the formula The column formula contains two parts separated by a vertical line The first part is the
95. tware are the mechanism for getting the data so it can be referenced in an indicator formula It puts a label or name on an element that exists in the data table Parameters are based on either 1 Report Name and Row Name 2 Report Name and Row Name and Column Type 3 Report Name and Column Type Some reports do not have a Column Type such as the Balance Sheet Profit and Loss Statement and Sales by Rep Summary For the other reports you need to specify the Column Type as part of the parameter Parameters do not get associated with a department date or period This is handled ata higher level when a gauge is defined Clicking on the Parameters icon in the ACE Definitions block will bring up the following form Page 14 CleverQ Customization User Manual EE ae er ee A parameter Detail Parameter T Tested Mame Mote To orere aufo naning change be name after selecting the Fepor Fo and Com Key Parameter A parameter can be mapped to a specific item on a report data Null to zero source or formula based on other parameters F No Filters Map Parameter Report Mame Row Mame Column Type Calculate Parameter Formula Mote For Formulas When a parameter Formula is calculated normally the results are calculated For each time period in a date range and stored to be used by other parameters or indicators To only have a Formula calculated once with the specified date range preceed the F
96. uges indicators and parameters you may want at some point to see the relationships between all these objects The main menu option Setup Object Relationships Export will give you a very informative view of the objects in your QBD data file Herend rayado Ejea File View Edit Navigator Preferences Data Sources Parameters Indicators Gauges Dashboards Security gt Object Relationships Export Import Objects You can also click on the box on the navigator to select this option Definition Relationships Export Import Upload to Subscription Service After selecting this option you will see the following form Page 62 CleverQ Customization User Manual A fle le rings 1 Us D CleverQDatalKIS5iRYDashboardData gbd mm Dashboards Gages Indicator Categories Indicators key Indicators Parameter Sources Parameters En Rial im Es Rs Top Bottom Down Up Retresh Expand All Collapse All Select All Highlight item above first then view Set Key Parameters Show Other OBO File Check items on this form then Export ERA This form is blank when it is opened but you can make quite a few different selections to see a tree or outline view of the object from different perspectives The buttons on the left with colored labels represent objects and is used to build the tree The tree 1s actually upside down and these buttons determine the root of th
97. y Year O Company Return on Assets Return on Assets Net Income Ratio Formula PJ Total Assets Ratio Formula O Interest Coverage Ratio H 0 Interest Coverage Ratio O Expense Inventory Interest Company Ratio Formula E C Operating Profit EBIT Ratio Formula oO Expense Company Depreciation Expense Income Tax Provision oO Expense Inventory Interest Company Net Ordinary Income O Other Interest O Other Interest Ratio Formula oO Dupont analysis Company ROE C DuPont formula Company ROE C Equity Multiplier Ratio Formula J Total Assets O Total Equity E E Net Profit Margin Ratio Formula Highlight item above first then view Show Other QBD File Check items on this form then Export Form view x After you have checked off the objects you want to export Press the Export button Even though you may have checked off dashboards they will not be exported Only gauges indicators and parameters are exported If an object by the same name already exists you will be prompted to overwrite it You may see this message as soon as you export if the same object is checked more than once on the tree This is because you have already exported it When a message appears similar to this one Mireia rameter The parameter COG5 Parts amp Accessories already exists Do you want bo overwrite this parameter Yes Mo Cancel Press Yes to overwrite it No to not export it or
98. you that this pull down list includes a number of items to help you choose the parameter including the report name Source row name column type and formula Other Page 23 CleverQ Customization User Manual otal Assets Turnover Other Met Profit Margin Net Income Total Income Met Working Capitol Total Current 4ssets Total Operating Profit EBIT Met Ordinary Income Expe Other Interest Profit amp Loss 69900 Other Interest F amp A Margin income Parts amp Accessories Quick Assets Total Current ssets Total1 Service Hours Collected Service Income Service Lab El Service Income Profit amp Loss Total 47000 Service Department Service Labor Rate a Total 13100 RY Inventory Balance Sheet Total 13100 RY Inventory Total Assets Balance Sheet TOTAL ASSETS Total Assets Turnover Total Income Total Assets Total Checking Savings Balance Sheet Total Checking Savings Total Current Assets Balance Sheet Total Current Assets Total Current Liabilities Balance Sheet Total Current Liabilities Total Equity Balance Sheet Total Equity Total Fixed Assets Balance Sheet Total Fixed Assets Total Income Profit amp Loss Total Income Total Inventories Balance Sheet Total 13000 Inventory Total Inventory Amount Sales by Item Summary Total Inventory Amount Total Inventory Asset Value Inventory Valuation Summar Total Inventory Asset Value Total Inventory COGS Sales by Item Summary Total Inventory Total Inventory On Han

Departments

Contents

Download Pdf Manuals

Related Search

Related Contents