Question

Prepare the worksheets yourself for examples used in sections 3.1, 3.2, 3.3 and 3.4 respectively. Add two new more records in each worksheet (with your own assumed values) and verify whether the computations are correct.

Answer 1

This sheet tracks Employee Pay components. Administrative constants (Rates) are located in cells H3:H6 and must be referenced using absolute addresses ($).

Constants Setup: H3 = 28 (Days in Month), H4 = 35% (DA Rate), H5 = 40% (HRA Rate), H6 = 12% (PF Rate).

Data and Computations Table:

Emp ID	Name	Basic Pay	Attendance (Days)	Basic Earned (Col F)	DA (Col G)	HRA (Col H)	PF (Col N)	Net Salary
101	Amit	₹ 40,000	28	₹ 40,000	₹ 14,000	₹ 16,000	₹ 4,800	₹ 65,200
102	Priya	₹ 50,000	21	₹ 37,500	₹ 13,125	₹ 15,000	₹ 4,500	₹ 61,125
103*	Raj (New)	₹ 30,000	28	₹ 30,000	₹ 10,500	₹ 12,000	₹ 3,600	₹ 48,900
104*	Neha (New)	₹ 60,000	14	₹ 30,000	₹ 10,500	₹ 12,000	₹ 3,600	₹ 48,900

Target Formulas for Row 4 (Amit):

Basic Earned: =(C4/H3) * D4 → (40,000/28) × 28 = 40,000

DA (Dearness Allowance): =F4 * $H$4 → 40,000 × 35% = 14,000

HRA (House Rent Allowance): =F4 * $H$5 → 40,000 × 40% = 16,000

PF (Provident Fund): =F4 * $H$6 → 40,000 × 12% = 4,800

Net Salary: =(F4 + G4 + H4) - N4 → (40,000 + 14,000 + 16,000) - 4,800 = 65,200

Verification: For the new record Neha, her 14 days of attendance correctly cut her Basic Earned in half (₹ 30,000), and all dynamic downstream headers adapt perfectly.

Calculates asset depreciation evenly across its lifespan using the SLN function.

Constants Setup: C3 = Cost (₹1,000,000), D3 = Salvage Value (₹100,000), E3 = Useful Life (10 Years).

Year	Opening Book Value	Depreciation Formula (Col K)	Yearly Depreciation Expense	Accumulated Depreciation	Closing Book Value
1	₹ 1,000,000	=SLN(C3, D3, E3)	₹ 90,000	₹ 90,000	₹ 910,000
2	₹ 910,000	=SLN(C3, D3, E3)	₹ 90,000	₹ 180,000	₹ 820,000
3*	₹ 820,000	=SLN(C3, D3, E3)	₹ 90,000	₹ 270,000	₹ 730,000
4*	₹ 730,000	=SLN(C3, D3, E3)	₹ 90,000	₹ 360,000	₹ 640,000

SLN = `("Cost" − "Salvage")/"Life"`

= `(1,000,000 − 100,000)/10`

= ₹ 90,000 per year

The newly appended Years 3 and 4 track a linear drop of exactly ₹90,000 each. The locked variables block cell reference shifts when formulas are dragged down.

Calculates accelerated declining depreciation values over an asset's lifespan using the DB function.

Constants Setup: F3 = Cost (₹500,000), G3 = Salvage Value (₹50,000), H3 = Useful Life (5 Years).

Data and Computations Table:

Year	Opening Book Value	Book Value DB Formula Structure (Col G)	Yearly Depreciation Expense	Accumulated Depreciation	Closing Book Value
1	₹ 500,000	=DB(F3, G3, H3, 1)	₹ 185,500	₹ 185,500	₹ 314,500
2	₹ 314,500	=DB(F3, G3, H3, 2)	₹ 116,680	₹ 302,180	₹ 197,820
3*	₹ 197,820	=DB(F3, G3, H3, 3)	₹ 73,391	₹ 375,571	₹ 124,429
4*	₹ 124,429	=DB(F3, G3, H3, 4)	₹ 46,163	₹ 421,734	₹ 78,266

Fixed Depreciation Rate: Excel’s internal DB logic evaluates the rate using:

`1 - ("Salvage"/"Cost")^((1/"Life"))`

= `1 - ((50,000)/(5000,000))^((1/5))`

= 37.1%

Year 3 Computation: ₹ 197,820 (Opening) × 37.1%

= ₹ 73,391

Year 4 Computation: ₹ 124,429 (Opening) × 37.1%

= ₹ 46,163

The dynamic computations for newly added periods 3 and 4 show standard declining balance behaviour where total depreciation decreases sequentially over time.