Practical 3: MS Excel depreciation and Logical functions

1
Practical 3 MS Excel depreciation and Logical
functions

• Gopalan Vivek
• vivek_at_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/Practical
  s/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
Example
Now solve the question 1 in the practical
3 http//chaos.nus.edu.sg/Teaching/SCC2301/Practic
als/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 assets estimated useful life.
• Annual depreciation is computed as follows

Cost - Residual Value Estimated Useful Life
10,000 - 1000 5 years
1800 / year


Check the help for SLN function in Excel for more
details
11
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 assets
  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
D10-F10
Carrying Value(CV) Cost Accumulated
Depreciation
15
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
17
(No Transcript)
18
Now solve the question 2 in the practical
3 http//chaos.nus.edu.sg/Teaching/SCC2301/Practic
als/practical_3.html
19
Solve the Portfolio question in the practical 3
and submit your answers http//chaos.nus.edu.sg/T
eaching/SCC2301/Practicals/practical_3.html