Practical 3: MS Excel depreciation and Logical functions

1. Practical 3: MS Excel depreciation and Logical functions Gopalan Vivek vivek@bic.nus.edu.sg

2. Objectives • Logical functions • AND • OR • NOT • Depreciation functions • DB (Fixed Declining Balance Method) • DDB (Double Declining Balance Method) • SLN (Straight line Declining Method) • VDB (Variable Declining Balance method) • SYN (sum-of-years' digits depreciation) • Complete questions in Practical 3 given below http://chaos.nus.edu.sg/Teaching/SCC2301/Practicals/practical_3.html

3. Logical Functions • AND • returns true if ALL its arguments are true • OR • returns true if ANY of its arguments is true • NOT • Reverses the value of its argument • Eg. NOT(false) returns true Check the help for Logical functions in Excel for more details

4. D8=AND(B8,C8) E8=OR(B8,C8) F8=NOT(B8) G8=NOT(C8) Example Now solve the question 1 in the practical 3 http://chaos.nus.edu.sg/Teaching/SCC2301/Practicals/practical_3.html

5. Depreciation -definitions • A decline in the value of a property due to general wear and tear or obsolescence • Method of writing off wear and tear on assets that are used to produce income. • Amount of value that a possession loses over time.

6. Example

7. 4 factors required for depreciation calculations • Cost • Net purchase price. • All reasonable and necessary expenditures to get the asset in place and ready for use. • Residual Value (Salvage or Scrap Value) – • Asset value at the end of its expected “useful life”. • Depreciable Cost • Cost less residual value. • Depreciable cost is allocated over the useful life of an asset. • Life • useful life period of the asset

8. What property may be depreciated? • “Depreciable property” must meet the following qualifications: • It must be used in a trade or business or held for the production of income. • It must have a useful life of more than one year • It must wear out or lose value over time • It must be fully installed and in use to support revenue generation. (A depreciable asset is not only acquired for use to support of earning profit, it must be in use, and not just owned by a business.) http://www.fixedassetinfo.com/

9. Depreciation Methods • Straight-line Method • Accelerated Methods • Declining balance method • Sum-of-the-years digits • Production or Use Methods

10. Straight Line Method • This method spreads the depreciable costs evenly over the asset’s estimated useful life. • Annual depreciation is computed as follows: Cost - Residual ValueEstimated Useful Life $10,000 - $1000 5 years $1800 / year = = Check the help for SLN function in Excel for more details

11. =SLN(D11,$D$5,$D$6) =D10-F10 Carrying Value(CV) = Cost – Accumulated Depreciation

12. Decling-Balance Method • Results in relatively large amounts of depreciation in the early years of an asset’s life and smaller amounts in later years. • Is an accelerated method. • Assumes that plant assets are most efficient when new.

13. Decling-Balance(DB) Method -Type • Fixed DB method • computes depreciation at a fixed rate. • Double DB method • computes depreciation at an accelerated rate. • Variable DB method • Also computes depreciation at an accelerated rate. • switches to straight-line depreciation when depreciation is greater than the declining balance calculation Check the help for DB, DDB and VDB functions in Excel for more details

14. =DB(D11,$D$5,$D$6,C11) =D10-F10 Carrying Value(CV) = Cost – Accumulated Depreciation

15. =DDB(D11,$D$5,$D$6,C11) =D10-F10 Carrying Value(CV) = Cost – Accumulated Depreciation

16. Summary - depreciation VDB Variable-decling balance http://www.umanitoba.ca/afs/agric_economics/course/061.046/assign/Lab04.pdf

18. Now solve the question 2 in the practical 3 http://chaos.nus.edu.sg/Teaching/SCC2301/Practicals/practical_3.html

19. Solve the Portfolioquestion in the practical 3 and submit your answers http://chaos.nus.edu.sg/Teaching/SCC2301/Practicals/practical_3.html