May 26, 2013

How to Compute the Link Relative Ratio of Automobile Sales

The business spreadsheet software is widely welcomed by business users for its simpleness and ease of use. However, there are some common calculations which are still tough for spreadsheets to solve, such as the year-on-year basis and link relative ratio.

Take the sales details from a Volkswagen 4S shop in the below table as an example, they are purchase records of customers in various periods.

We need to calculate the ratio of sales volume in the current month to that of its previous month (Supposing the current month is December of 2012), and the year-on-year basis of sales volume of each month. Detail data needs to be kept for other computing. The result will be like this:

The section in the red enclosed box can be implemented easily that users only need to filter, sort, summarize in groups, and fold the data. But the link relative ratio calculation (i.e. column LRR) is a quite different matter. Visually, it seems that you should write =C458/C4 in the F458 cell, drag or copy it to the column F or other cells. In fact, it is not correct since the formula in the cell F890 will change to” =C890/C436” instead of the expected “=C890/C458”. This is because that the common spreadsheets only mechanically calculate the offset when copying the formula. For example, 458 - 4 = 454, the 436 is the result if the offset of 890 is 454. To have a correct computation, you must enter each formula manually. Needless to say, when there is huge data, the workload will be great.

In addition, the meaningless formula will definitely appear in the detail cells like F1324, because the common business spreadsheet software cannot differentiate the summary section and the detail section. If dragging formula in the summary section, these formulas will be copied and pasted to the detail section automatically. Such “automation” is obviously not expected, and we have no choice but input the formula manually.

When calculating the Year-on-Year (i.e. YOY) column, we will be in the similar situation: undistinguishable summary and detail, incorrect formula paste, and faulty formula appearing in the section of details data.

However, esCalc is more efficient to solve the problems alike. It is the business spreadsheet software with the “homocell” functions. In the Summary section, any formula entered will be copied and pasted to the cell with the same business status (i.e. other summary sections), without any impact on the detailed data. Just input the formula for once, and other homocells will be adjusted according to the business logics automatically. For example, write “=C458/C4” in the cell F458, and “=C890/C458” will appear automatically in the cell F890. Therefore, with esCalc, only two formulas are needed to be entered to solve such kind of problems, of which the formula for link relative ratio is:

The year-on-year basis is: