How to Easily Calculate Sales Data with Spreadsheet?

Business computing is usually conducted by business users without technical expertise or programming experience. Due to limited technical skills, they resort to more ease-of-use business spreadsheet software like Excel. However, even some common calculations are still difficult for spreadsheets to solve, such as the year-on-year basis and link relative ratio.

For example, the sales details of a Volkswagen 4S shop, which are the purchase records of customers in various periods is presented in the below table.

We need to compute the link relative ratio of each month (Supposing the current month is December of 2012), and the year-on-year basis of sales volume of each month. We keep the detail data for the next computing. The result will be like this:

Users can easily implement the computing in the red box, since they only need to filter, sort, summarize in groups, and fold the data. But the link relative ratio calculation (i.e. column LRR) is not so simple. It seems that you only need to write =C458/C4 in the F458 cell, drag or copy it to the column F or other cells. Actually, you’ll find problems since the formula in the cell F890 will be changed into “=C890/C436” but not the expected “=C890/C458”. The reason for this is that the traditional spreadsheets only mechanically calculate the offset when copying the formula. For instance, 458 – 4 = 454, when you bias 890 by 454, the result will be 436. Therefore, you have to manually input formula to have a correct computing. But when the data is huge, the workload will be greater.

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 to the summary section, these formulas will be copied and pasted to the detail section automatically. But such “automation” is obviously not expected, and we have to input the formula manually since there is no other choice.

There is also similar situation when calculating the Year-on-Year (i.e. YOY) column: undistinguishable summary and detail, incorrect formula paste, and wrong formula in the section of details data.

However, esCalc can solve the above mentioned problems efficiently. It is the business spreadsheet software with the “homocell” functions. With multi-level structure of this powerful business spreadsheet software, mass computing is realized easily. For instance, 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. By entering the formula once, the homocells will be adjusted according to the business logics automatically. For example, if you write “=C458/C4” in cell F458, “=C890/C458” will appear automatically in the cell F890. In this case, only two formulas can solve such kind of problems with esCalc, the business spreadsheet software. The formula for link relative ratio is showed in the following figure:

The year-on-year basis is: