Use of Spreadsheet in Business Applications — NCERT Solutions

The DB function is used to compute depreciation by the Written Down Value (WDV) method, also called the Declining Balance method.

Use the compound amount formula:

$FV = PV(1+r)^n$

Here, $PV=1000$, $r=10\%=0.10$, and $n=2$.

$FV = 1000(1+0.10)^2 = 1000(1.1)^2 = 1000 \times 1.21 = 1210$

So the future value is Rs. 1,210.

Use the present value formula:

$PV = \dfrac{FV}{(1+r)^n}$

Here, $FV=1000$, $r=10\%=0.10$, and $n=3$.

$PV = \dfrac{1000}{(1.1)^3} = \dfrac{1000}{1.331} \approx 751.31$

So the present value is Rs. 751.31 (approximately).

In the SLM (Straight Line Method), depreciation is charged in equal amounts every year on the total depreciable amount. The formula given is:

$\text{Straight Line Depreciation} = \dfrac{\text{Total Depreciable Amount}}{\text{Expected Useful Life}}$

In the WDV (Written Down Value) method, depreciation is charged on the reducing book value of the asset, so the amount of depreciation keeps changing each year. It is also called the Declining Balance (DB) method.

The Excel functions used are SLN for SLM and DB for WDV.

The two basic methods of depreciation are:

1. Straight Line Method (SLM): Depreciation is calculated by dividing the total depreciable amount by the expected useful life. It gives the same depreciation every year.
2. Written Down Value Method (WDV): Depreciation is calculated on the current book value of the asset, so the depreciation amount decreases over time. It is also called the Declining Balance (DB) method.

The Excel functions used are SLN for the Straight Line Method and DB for the Written Down Value Method.

Relative address changes when a formula is copied to another cell, while absolute address does not change. The chapter says absolute addresses are used for those cells whose reference should not change while the formula is copied.

The basis is:

• Use relative address when the reference should adjust according to the new position of the copied formula.
• Use absolute address when the same cell must be referred to in every copied formula, such as a fixed rate or fixed value cell.

If relative addresses were used instead of absolute addresses in those payroll formulas, the references to fixed cells like the DA rate, HRA rates, transport allowance, PF rate, and NODM would shift when copied down the column. Then the formula would start pointing to wrong cells, producing incorrect salary calculations for later rows.

So the absolute address is necessary for fixed inputs such as $I$3, $I$4, $I$5, $I$6, $I$7, $I$8, and $I$9.

In the WDV spreadsheet, the formula in column J uses the year-end date in the first year. If F3 is written as a relative address, then when the formula is copied down it will shift to another cell instead of always referring to the fixed Year-End Date.

That is why the formula must use $F$3. The copied reference should remain $F$3 in every row, not move to F4, F5, etc. If it shifts, the period calculation will use the wrong year-end date and the depreciation result will be incorrect.

So:

• Copied incorrectly with relative address: F3 becomes F4, F5, ...
• Correct copied value: always $F$3

This fixed reference is necessary because the year-end date is a common input for all rows.