Report of Sales of Various Car Models on Link Relative Ratio and Year-on-year Basis

Target:

Make a grouped report on link relative ratio and year-on-year basis according to the database containing sale figures of various car models, and compare the annual sales amounts of different car models with a chart.

Below are car orders of the past 3 years in the original database, and the final report:

1. Design layout

Fetch data from the database via wizard and remove extra columns as below:

Next, group data into 3 levels by Model, year and month. Firstly, group by Model.

Here’s the result:

Note: Traditional reporting tools are oriented to abstract dataset variables in grouping data, while esCalc enables handling data directly in a more intuitive and simpler way.

Then group by year. Because there is no “year” column, let’s append a column and get results via computation as below:

The result would be:

Follow the previous way to group by year, and by month. The formula for the latter is month(B4). The result is as below:

Next, add header to C1 and D1 in bold and center alignment. As column B becomes useless, you can remove it. The result is shown as below:

Note: esCalc designs the layout in the same way in which a spreadsheet works. Also, it can partly replace a reporting tool because of its distinctive features, which you may find impossible with conventional spreadsheets. You’ll make deep acquaintance with those features if you keep on reading this.

2. Compute summary values

Let’s compute the car sales in each year and month. Firstly, append a column and enter formula in D4 to compute the sales of Golf in January, 2010.

{C5} represents the homocells of C5，which are the cells with the same business position, including cells like C5，C6 and C7, but not those like C4, C3 and C2.

Let’s look at the result:

You can find that {C5} only contains the 51 pieces of data in January because the formula is written in the “Golf-2010 -1″ Summary Area. How about the calculations of sales of other months?

Click group level 3 to show only the summary data “Model-year-month”, and you will find this:

As you can see, the formula has been automatically copied to the summary areas of other months, which are the homocells of D4, such as D56, D86 and D580. It hasn’t been copied to non-homocells, such as D3 and D579.

Now, compute the sales of each year. According to the rule of homocells, we just need to compute the sales of “Golf-2010” and the sales of other years will be computed automatically. For example, enter a formula in D3 as below:

Here’s the result we get:

As expected, “sales” of all years have been computed automatically by entering formula once.

Note: When {C5} is written in D3, the yearly summary area of 2010, it will be interpreted as the data of the whole year; but when written in D4, the monthly summary area of January, it will be interpreted as data of the current month. This intelligent parsing is a quality that conventional spreadsheets lack.

To check the data of each year visually, click on the group level “2”, and you will get this:

Let’s add a header to column D before proceeding to next step. Please notice that the format painter can help keep the style consistent.

3. Compute link relative ratio and year-on-year growth rate

Link relative ratio means comparison between the current month and the last month, for example, link relative ratio of February is: D56/D4.

The above formula cannot be correctly recognized for common spreadsheets, because it is not “abstract” enough. But esCalc can! Let’s append a column and enter the above formula in E56.

As you can see, the formula has been automatically copied to the homocells.

The conventional spreadsheets copy the formula mechanically. There are 86-56 = 30 lines between February and January, so the formula D56/D4 in E56 will become D56 +30 / D4 +30 = D86/D34 when copied to E86. Obviously this is wrong. The correct formula should be D86/D56.

Now check the formula in E86 in esCalc:

Correct! esCalc will intelligently adjust the formula when performing a copy, while a conventional spreadsheet won’t.

Let’s take a look at cross group copying. For instance, can “January of 2011″ find the “December of 2010″ in esCalc? Or will esCalc adjust the formula incorrectly by referencing D579? Let’s check it:

Still correct! The intelligent adjustment of formula correctly solves the cross-group issue.

In cross-group copying with neighboring years，the first member of the current group will divide the last member of the previous group. However, sometimes we don’t need such intelligence, e.g. cross-group copying of neighboring models as shown below.

Obviously it does not meet the business logic by using “Jetta-2010-1″ to divide “Golf – 2012-12″. But if the data range is limited to the same model, this problem could be solved.

esCalc can easily realize this. Add A1504, the data range of Jetta, in the formula as below.

You will get this:

By doing so, D1470[A1504] doesn’t belong to Jetta, so the value is null. It is correct again!

The above cross-group calculation depends on the right formula migration as the following picture indicates:

Go on to compute the Year-on-year ratio. Year-on-year ratio refers to data of the current month compared with the same month in last year. For instance, D580 in January of 2011 should be compared with D4. Similar to link relative ratio, you just need to enter the formula D580[A2]/D4[A2] as below:

And you will get the following result:

Due to the limit enforced by “[]”, year-on-year ratio of each car model in 2010 should be null. And the result is just as expected.

Featuring homocells, intelligent parsing and smart migration, esCalc is as easy to use and capable as conventional spreadsheets on both computing and professional tabulation.

4. Format report layout

Firstly, insert a row before the report headers and enter the title to complement the table as below:

Secondly, set background color to the first row of level 2 groups.

As can be seen, row 580 changes color as well.

The homocell model in esCalc enables automatic copying of the formula and the style. The lack of similar model in conventional spreadsheets, however, makes them fail to produce a report intelligently.

Next, add more background colors and appropriate gridlines:

It’s all right to set the gridline only for one row and the all other homocells will automatically copy this style.

Sometimes we don’t want the same style for all homocells. E.g, Interlaced color for month data is friendlier. It can be realized as below:

The formula above means: If the sequence number is divided by 2 with 0 as remainder, the background will display yellow. # represents the sequence number within the group, e.g, the sequence number of row 5 is 1, and row 57 is 2. -724610 is color value, which can be selected directly from color palette.

Below is the interlaced effect after entering the formula:

Next, set the number format as percentage, e.g, set the format in a certain cell of LRR column as below:

You will get this:

Similarly, set percentage format for YOY column and we will get this:

To highlight months with sales growth rate greater than 100% in red, you can make it as below:

You will get this:

5. Plot chart

A reader-friendly report needs a chart to show the annual sales of all car models. Let’s insert a row and add a chart template:

Double-click the template to open the chart editor:

Set parameters as below:

After closing the dialog box, the chart will present as below:

You may notice that the years are arranged from past to present, but many people prefer the opposite, so reordering members of each group is necessary. Conventional spreadsheets can’t manage it due to the lack of professional reporting functions.

On the contrary, esCalc can easily realize it. Just perform a sort action by selecting cell B5 as below:

You will get the following result:

Now, the years are sorted from present to past, and the chart is also changed automatically.

Finally, take a look at the preview and print:

That is how the self-service tabulation software, esCalc, works.