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:
No comments:
Post a Comment