esCalc Introduction A brand-new desktop BI tool

1. esCalc IntroductionA brand-new desktop BI tool www.raqsoft.com

2. esCalc is a spreadsheet software

3. Unlike traditional spreadsheets which emphasized on peripheral functions, esCalc does not , i.e: photography, social networking and sharing esCalc leads to revolution in computing capability. esCalc performs best in the complex computation, which is exactly the weaknesses of traditional spreadsheets.

4. For example Show year-on-year basis and link relative ratio sales amount of numerous salespersons and products • Join the monthly salary, attendance, performance information table • Find out the products whose price keeping rising for seven consecutive weeks • Find out the months whose sales amount is higher than the average sales amount of current year since 1999.

5. Where does the computing capability come from? Formula intelligently copied Free step by step computation Homocell Model Complete set-lization Associated computations of tables

6. The traditional spreadsheet cells are isolated from each other, lacking of business relationship. • In esCalc, cells with the same business position (homocell) are intrinsically linked. 5250 5250 8450 2050 5633 5633 6450 4300 6150 3650 3650 4700 2600 esCalc can solve complex computational goals easily relying on the homocell model.

7. Formula intelligently copy Formulas in esCalc will automatically be copied to homocells, this feature can free users from heavy manual operations. • Case: compute the monthly sales amount proportion of each sales

8. Original data: the sales amount details for each salesman last year • First, group by month

9. Then group by salesman

10. Enter the formula in D3 and get the sales value of Baker

11. Click on group level 2, shrink to Summary band • we can see the formula has been copied to the homocells such as D35 and D67,etc.

12. Enter formula in D2, compute the total sales of January We can see that the formulas are also be automatically copied to the homocells D325, D616

13. Last, enter the formula to calculate the ratio: D3/D2 • Similarly , the formula has been copied to the homocells. • esCalc finished the calculation without any difficulty. • It is more difficult to do the same operation in traditional spreadsheets. For example compared with Excel as below:

14. The same raw data offered Excel does not support automatic sort before grouping, so first sort by month and salesman.

15. Then group by month Similarly, group by the salesman

16. Click on the group level 3, shrink to the summary row. Then calculate the proportion in E4, enter the formula: =D4 / $ D $ 3.

17. Copy the formula in E4, and then paste to E36 to E294 one by one

18. Note: don’t use drag for bulk copy, otherwise the formula will be copied to the detail data. It’s wrong to do it like this.

19. Obviously, it’s more troublesome while the data amount gets bigger. This is nothing compared to the next difficulty.

20. $D$3 Continue to copy the formula to E327 D327 D327 D327/$D$3，this is wrong $D$326 It should be D327/$D$326

21. Traditional spreadsheets can just mechanically copy the formula lacking of intelligent adjustment based on the business association of the cells. The homocell model enables formulas not only to automatically copy, but also to automatically adjust according to the business relationship.

22. Check the esCalc formula: The same month： E3=D3/ D2 Different month ： E3= D3/ D2 Will not change what shouldn’t be changed Will change what should be changed intelligently E35= D35/D2 E326=D326/D325 This intelligent adjustment is what the traditional spreadsheet doesn’t have.

23. The traditional spreadsheet doesn’t support smart formula adjustment and can only re-enter the formula and then manually copy to other cells.And each set of data must repeat the entire process. It’s unbearable facing a large amount of data.

24. There will be even greater difficultieswhile changing a computation task. For example, what’s the monthly sales growth of each salesman? Group by the salesperson and month then summary.

25. - The subtraction of adjacent months is the sales growth of last month. D35 For example, Baker’ increment in February should be D35-D3 D3

26. How to calculate the increment in March? Let’s try to copy the formula to E64. D64-D32？This is obviously wrong The traditional spreadsheets, due to the lack of homocellmodel, can just copy the formula by increment mechanically, which is not in line with the business logic.

27. Manually enter each formula? Absolutely unbearable! While in esCalc, one can still group by the salesman and the month first and then summary.

28. Enter formula D35-D3 in E35 Check the formula of March D64-D35, this is in line with the business logic!

29. For continuous data, the earlier formula is perfect. Replace the salesman by Year.

30. Check the formula in E381, the increment in January 2012 should be D381-D348. The formula is correct.

31. Change back to the non-continuous data, the same formula is wrong. D381-D348 is not in line with the business logic. The subtraction between different salesmen is meaningless. The computation shall be limited to the same salesman.

32. Let’s add a data range for the formula in the E35: D35 [C2]-D3 [C2]. Here [C2] indicates that the data range is limited to salesman Baker.

33. Check E381, the increment of January: = 65040-0 = 65040 Whether the data is continuous or not, esCalc can intelligently replicate the correct formula. While traditional spreadsheet can not!

34. Free Step by step Computation Step by step computation breaks the complex computation objects into simple steps. However, traditional spreadsheets lack of freedom during the stepwise computation due to timeworn models. • For example : who are monthly top 5 salesman? We can take 4 steps: group, summary, sort and filter. The first 2 steps are the same with previous case.

35. Then, we need to sort by sales value. The traditional spreadsheets have to sort one month after another. This is the sorting result of January.

36. Repeat the operation 12 times to sort each months • Filter out the top 5 of each month.

37. Use the “top N” filter function? No way! It cannot distinguish detail data and summary data. Method 2 is to delete the data after fifth manually. Obviously it cannot deal with large amount of data. E.g. the 10 years data of AXA Insurance company which has tens of thousands of salesmen. Advanced users can use the third method.

38. First select “visible cells” to copy the summary data to a new sheet. Then input the formula in D3：=IF( NOT(ISBLANK(B3)),0,E2+1) Next, drag to copy this formula into other cells.

39. So we created the “Serial number within group” manually. Then set filter mode to filter. We finally finished the computing after many efforts.

40. Why cannot sort all the groups directly? Why cannot filter by serial numbers directly? Traditional spreadsheets cannot compute data freely step by step. They often need a lot of skill and manual manipulation to complete computation object. esCalc enables free stepwise computation.

41. First sort in D3 based on the same summary data. Just one step, all the groups are sorted. Then filter by serial numbers within group directly. Just one step to finish filtering.

42. In esCalc, you can manipulate the current data freely and do the next computation at will. Another example, count the orders of salesmen based on current result just need to enter the formula {D4}.count() into E3. It is really annoying to recalculate from the original data in traditional spreadsheets.

43. Whatever the next computation is, filter, add columns, sort, summary and group, regroup, esCalc is more convenient and free than traditional spreadsheets.

44. Complete set-lization computation esCalcdirectly supports set data types, and can easily solve the problems related to set. For example, if you want to find Best Employee of the Year, that means you need to find whose sales are in the top 5 every month based on the previous case:

45. First, work out the set of salesmen in each month. Then calculate the intersection of these sets. Easily done !

46. Traditional spreadsheets are more complicated. Method 1: group the data by salesman, figure out the appearance numbers of the salesmen, copy them into a new sheet and filter the sales whose appearance number is 12. This method is not easy to understand, but also makes great damage to data and the reusability is not strong as well. Method 2: Simulation set operations

47. Input this horrible formula into F4: =IF(AND(COUNTIF($C$15:$C$19,C4),COUNTIF($C$26:$C$30,C4),COUNTIF($C$37:$C$41,C4),COUNTIF($C$48:$C$52,C4),COUNTIF($C$59:$C$63,C4),COUNTIF($C$70:$C$74,C4),COUNTIF($C$81:$C$85,C4),COUNTIF($C$92:$C$96,C4),COUNTIF($C$103:$C$107,C4),COUNTIF($C$114:$C$118,C4),COUNTIF($C$125:$C$129,C4)),C4,"") This formula matches C4 with other sales in other months, and return C4 if they match.

48. Drag to copy the formula to the cells fromF5 to F8. With complex formulas, we get the result finally.

49. Traditional spreadsheets have high techniques requirements for users on set operationand users have to develop abstract algorithm. This is hard for common users. esCalcsupports sets completely, and it’s easy to do union, intersection and subtraction operation and thus solve complex problems.

50. Associated computations of tables Homocell model makes the multiple tables computation very easy. Here are three tables recording the basic salary, absences, and performance of this week separately. Please compute the actual weekly salary.