Practice Video: Depreciation Functions (SLN, SYD, DDB)

Loading...

From the course by Macquarie University

Excel Skills for Business: Advanced

136 ratings

Macquarie University

Excel Skills for Business: Advanced

136 ratings

Course 4 of 4 in the Specialization Excel Skills for Business

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Across the globe, millions of job advertisements requiring Excel skills are posted every day. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others, so keep reading. In this last course of our Specialization Excel Skills for Business you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the Advanced course, we will prepare you to become a power user of Excel - this is your last step before specializing at a professional level. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel.

From the lesson

Financial Functions and Working with Dates

This module introduces financial functions into our Excel knowledge base. Financial functions are often used in businesses and Excel has a variety of tools to help you with that. This week, you will learn to apply date formulas in calculations, create loan schedules with financial functions, as well as use depreciation functions.

Practice Video: Working with Dates (EOMONTH, EDATE, WORKDAY.INTL)5:47

Practice Video: Financial Functions (FV, PV, PMT)8:22

Practice Video: Loan Schedule (PMT, EDATE)10:49

Practice Video: Net Present Value and Internal Rate of Return (NPV, IRR)7:47

Practice Video: Depreciation Functions (SLN, SYD, DDB)6:29

Meet the Instructors

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

Alex's last task for the week is to calculate

a depreciation schedule for the office furniture assets.

Excel offers several functions that will help us calculate depreciation,

and there are different methods that you could apply to different situations.

The first method we're going to look at is the Straight Line method.

This assumes that an asset depreciates by the same amount each year.

So, for example, if we have some office furniture,

which we pay $20,000 for,

we assume the office furniture will last about 10 years,

and we hope to sell it or trade it in for $3,000 at the end of the 10 years.

This is called the salvage value.

We're going to use the straight line method to

calculate how much it depreciates each year.

The calculation is going to work by subtracting the salvage value from the asset cost,

and then dividing by the number of years of life.

But we don't need to perform this calculation because

Excel straight line function will do it for us.

So, we're going to click into cell B16,

and type =SLN and Tab.

The straight line depreciation function expects three arguments,

the cost of the asset,

the salvage value, which of course can be zero, and the life.

And when we close our brackets,

there is the amount it's going to depreciate by each year.

So having calculated that,

we can just drag that across and if we have a look at the graph here,

it's depreciating at a nice constant rate,

but stopping at the $3,000 mark.

In the table below,

we've added some calculations to take

the original asset value and subtract the depreciation amount.

So you can actually now see how much the asset will be worth after each year.

So that was one method,

but as you probably know,

not all assets depreciate by the same amount each year.

Many assets depreciate far more in the first few years of their life.

For example, motor vehicles.

So another method you could apply is the Sum of Year Digits.

This method assumes that most of

the depreciation will happen in the early part of the asset's life.

The calculation works a little bit differently,

it does the sum of all the periods.

So for example, we would add one, two,

to three, all the way up to 10 to get 55.

It then depreciates by a fraction of that 55 each year.

So for example, the first year it will depreciate by 10 over 55.

The next year, by nine over 55 and so on.

But again, we don't need to worry about the maths

because Excel has a function to do it for us and.

It's called SYD.

We're going to click into cell B17 this time,

and type =SY and Tab to select the Sum of Year Digits.

Once again, we must specify the cost,

the salvage, and the life.

But the SYD requires an additional argument,

which is the period number.

We have listed these over here.

So I'm just going to click on the one and press Enter.

You'll notice the depreciation here is nearly double of the straight line value.

We're going to copy this across now.

And if you have a look at the figures towards the end,

the depreciation has come down significantly.

Also, looking at the chart you will notice that we are

getting an accelerated depreciation at the beginning,

which is then tailing off towards the end of the life.

Finally, a third method that is quite commonly

used is the Double Declining Balance method.

This is another accelerated method.

The double declining method takes the straight line percentage,

and applies a multiple to it.

By default, it doubles it.

Hence, the name double declining.

So for example, over 10 years,

we expect our asset to depreciate by 10 percent a year.

The double declining balance method will double

this to depreciate by 20 percent each year.

Let's see how it works.

So we're going to click into cell B18,

and type =DDB and Tab.

Once again, we're going to specify the cost,

the salvage, the life, and the period.

You will notice there's an optional argument factor.

If you don't specify this,

it will use 20 percent.

But you can use an alternative factor if you wish.

We're going to go with the default.

So we're going to close our brackets,

and now just for a check we're expecting to get 20 percent depreciation on $20,000,

which would be $4,000.

Let's check if that works. And there you go.

We're then going to drag that across,

and that is our double declining balance method.

Now, one thing to be aware of with the double declining balance,

is while it will often yield the salvage cost,

it does not always.

So if my salvage amount was actually $2,000,

because of the way the calculation is done,

it does not get to exactly $2,000.

So it does take it into account,

but it doesn't always yield exactly the salvage amount stipulated.

So, there we have three different depreciation methods,

Straight Line, the Sum of Year Digits,

and Double Declining Balance.

And just to finish off,

I should put that in my chart.

So there is our finished chart showing the three different methods side by side.

We have looked at quite a few of Excel's financial functions this week.

Make sure you have a go with the Practice Challenge,

so that you become more familiar and have lots of practice at these functions.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2018 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play