Fixed Assets

Overview

This Tab can be used for registering any fixed assets, so their net asset value, and depreciation can be calculated. The figures in the Fixed Asset Register should be shown excluding V.A.T..

The Depreciation Amounts are shown in the Depreciation Line in the ‘Profit & Loss’ Tab.

This Tab is used to keep track of inventory. (Currently not used)

Click to view large-sized image

Click to view large-sized image

Formula used

The formulae created to calculate the Accumulated Depreciation (shown at the bottom of the sheet) are quite involved. The overall logic though has been laid out below:

MS Excel 
=IF(
     FAR_Table[@[Date of Purchase]:[Date of Purchase]] > J$59, 0,
     IF(
         AND(
            FAR_Table[@[Date of Disposal]:[Date of Disposal]] >
            EOMONTH(J$59, - 1),
            FAR_Table[@[Date of Disposal]:[Date of Disposal]] <= J$59
        ),
        MIN(
            ( FAR_Table[@[At Cost]:[At Cost]] - FAR_Table[@[Salvage Value]:[Salvage Value]] ) / FAR_Table[@[Useful Life (Yrs)]:[Useful Life (Yrs)]] * ( FAR_Table[@[Date of Disposal]:[Date of Disposal]] - FAR_Table[@[Date of Purchase]:[Date of Purchase]] + 1 ) / 365,
            FAR_Table[@[At Cost]:[At Cost]] - FAR_Table[@[Salvage Value]:[Salvage Value]]
        ),
         IF(
             OR(
                FAR_Table[@[Date of Disposal]:[Date of Disposal]] > J$59,
                FAR_Table[@[Date of Disposal]:[Date of Disposal]] = 0
            ),
            MIN(
                ( FAR_Table[@[At Cost]:[At Cost]] - FAR_Table[@[Salvage Value]:[Salvage Value]] ) / FAR_Table[@[Useful Life (Yrs)]:[Useful Life (Yrs)]] * ( J$59 - FAR_Table[@[Date of Purchase]:[Date of Purchase]] + 1 ) / 365,
                FAR_Table[@[At Cost]:[At Cost]] - FAR_Table[@[Salvage Value]:[Salvage Value]]
            ),
             0
        )
    )