Accounting 101: Asset Tracking & Depreciation Using MS Excel | Chris Benjamin | Skillshare

Accounting 101: Asset Tracking & Depreciation Using MS Excel

Chris Benjamin, Instructor, MBA and CFO

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
10 Lessons (46m)
    • 1. Course Introduction

      1:41
    • 2. Instructor Introduction

      0:47
    • 3. Headers

      6:30
    • 4. Headers Formatting

      5:09
    • 5. Data Entry Part A

      4:48
    • 6. Data Entry Part B

      6:32
    • 7. Calculations and Entries

      5:36
    • 8. Adding Additional Months

      6:47
    • 9. Adding Additional Categories and Wrap Up

      7:24
    • 10. Course Conclusion

      0:52

About This Class

Are You An Accountant, Accounting Manager or CFO?

Do You Need An Easy To Use But Very Effective Way To Manage Your Fixed Assets, Depreciation and Amortization?

Do You Need A Schedule For Your Assets That Your Auditors Will Love?

Do You Want To Learn How Asset Tracking Works In The Real World?

If You Answered "Yes" To Any Of The Above, Look No Further.  This Is The Course For You!

*** Updated June 2019 with new content! ***

Enroll today and join the 100,000+ successful students I have taught as a Top Rated instructor!

Three reasons to TAKE THIS COURSE right now:

  1. You get lifetime access to lectures, including all new lectures, assignments, quizzes and downloads

  2. You can ask me questions and see me respond to every single one of them thoroughly! 

  3. You will are being taught by a professional with a proven track record of success!

  4. Bonus reason: Udemy has a 30 day 100% money back guarantee if for some reason you don't enjoy the course!

Recent Review:

James H. says "Fantastic, practical and realistic course. I needed an easy but highly useful way to manage our growing asset base, while also making sure everything was being depreciated correctly.  That's exactly what this course did.  Providing the template was just a great bonus as well.  Loved learning from Chris."

Why You Should Take This Course With Me:

Fixed assets are one area of accounting that require very detailed record keeping.  Besides backing up the book value on your financial statements, you need to accurately calculate, book and track your depreciation each period.  The larger your asset base, the more complex this task can become.  Using best accounting practices, in this course we will develop a spreadsheet to track all of the key data points related to your assets, such as date of acquisition, location, depreciation period, net book value, and much more.  

As well I provide the template we build in the course for you to download! If you don't have the time or desire to build your own, you can simply use the fixed asset tracking and depreciation spreadsheet we build in the course and modify it to meet your needs.  

Become a fixed asset expert and accounting wizard with this course!! 

This course is 100% in Microsoft Excel, showing you from start to finish how to build a template to use for fixed asset tracking and depreciation.  Starting with a blank worksheet, by the end of the course we have a fully developed Excel template.  

What We Do In The Course:  

  • Start with teaching basic Excel functionality 

  • Start with a blank Excel workbook which will become our fixed asset spreadsheet

  • Build the proper structure for documenting and tracking fixed assets

  • Format the spreadsheet so it is easy to read and follow

  • Place formulas in to calculate totals where appropriate

  • Show you how to track your fixed assets over time

  • Teach you how to then use that information to make informed decisions

  • And Much More!!!

At any point if you have a question, please feel free to ask through the course forum, I'd be happy to answer any and all questions.  

***JOIN NOW AND LEARN ASSET TRACKING, DEPRECIATION AND AMORTIZATION USING EXCEL! ***

About The Instructor

Chris Benjamin, MBA & CFO is a seasoned professional with over 20 years experience in accounting, finance, financial reporting, and small business, and employee management.  Having spent the first 10 years of my career in corporate settings with both large and small companies, I learned a lot about the accounting process, managing accounting departments, financial reporting, external reporting to board of directors and the Securities and Exchange Commission, and working with external auditors.  

The following 10+ years I decided to go into CFO Consulting, working with growing companies and bringing CFO level experience to companies.  I help implement proper best business practices in accounting and finance, consult on implementation of accounting systems, implementing accounting procedures, while also still fulfilling the CFO roll for many of my clients which includes financial reporting, auditing, working with investors, financial analysis and much more.  

Thank you for signing up for this course. I look forward to being your instructor for this course and many more!

Chris Benjamin, Instructor, CFO & MBA

Transcripts

1. Course Introduction: Hi, everyone. Thanks so much for signing up for the course. My name is Chris Benjamin, and I'll be your instructor in the next video. I'm gonna do a little bit more of an introduction to myself in my background, But for this course introduction video, I want to just give you more of a road map of where we're going to be going. So first of all, thanks again for signing up for the course. So basically, in this course, once we get past the introductions, we're gonna be working completely in Microsoft Excel. Now, this will work in Google Sheets as well. Or if you have another spreadsheet program that you like and what we're gonna do is start with a blank workbook. From there, we're gonna build it out step by step so that we have a very functioning, dynamic, fixed asset tracking spreadsheet. So, by the end, will be able to track all of our assets by different class types. Appreciation periods. We could even sort by where we bought the different assets in will be calculating a monthly depreciation by asset. It'll calculate our entries for us, and then we're gonna tie that back to our accounting system as well. So at any point we could look in our accounting system and say, You know, here's Here's the entry that was made for depreciation and ties back to excel and likewise Excel ties to our general Ledger. In our counting system, we'll have All of our numbers are total assets depreciation for the period accumulated depreciation for the totals and whatnot. So we're going to go on quite a bit in this course by the end of your have a terrific spreadsheet that you can use. And at the end of the course, you can download the spreadsheet yourself so you can either take the one that we build in the course, download it and then modify it for your own needs. Whereas you're following along, build your own. I encourage you to do that as well. Now, if during the course you have any questions, feel free to send me a question happy to answer any questions for you. That said, let's go ahead and move on to the next video so we can go ahead and get started. On building are fixed asset tracking, spreadsheet 2. Instructor Introduction: Hi, everybody. I'll be your instructor. My name is Chris Benjamin. I'm a chief financial officer and what I do is basically I work as a more of a consulting CFO. I work with small businesses and growing companies and help them implement accounting. Best practices, accounting software create different spreadsheets like we're gonna be doing in this course to help them manage their business. From an accounting and finance perspective, I have over 20 years experience in accounting and finance. I spent the first half and corporate America working my way up through the ranks all the way up to CFO level on in the last 10 years, I spent more on the consulting front like I just described. So it's really rewarding for me to work with growing companies and really helping them be the best that they can be. So that's a little bit about my background. Enough said about me, though. Let's go ahead and get started on the course 3. Headers: All right, everyone. So let's go ahead and get started. So we probably get too much done in this lecture. Just give you kind of the path of what we'll be doing. So first of all, go ahead, open a blank workbook. If you're gonna follow along, build your own. I've already saved mine. So I titled it Fixed asset list. Ah, what I will do. I've already mentioned this, but at the end of the course, this file will actually be available for download. So what we build during the course I'll make available for you to download with the course Conclusion eso If you want to disuse my template and kind of change it to meet your needs. That's perfectly fine as well. Ah, another point. I try to keep the videos to about 5 to 7 minutes. Whatever. Have a good kind of stopping point? We'll stop it. We'll just pick it up right again in the next video. Just so they're kind of easy to giant. Just, uh, next point. If at any point I do something in excel, you don't quite get what I'm doing, you know, maybe I just do something to take it for granted. I don't explain it like some formatting or something like that. Feel free to send me a message. Just asked me. What did I do? Just tell me which video it was which, like, lesson and ah, approximately. Like what? Time stamp Or described to me Kind of what happened. And I can probably explain to you what I did. What keys? I pressed her, did with my mouth. So But I try to be aware and and tell you everything that I'm doing. So I think that's about it, guys. So again, if you go ahead, open up a blank workbook. I've already saved mine fixed asset list. So the way we'll do this is we'll start off. We're just gonna start building this. We're gonna start putting in headers as we go. We might add, you know, additional headers that once we think about it a bit more. Now, once we get some header set. Well ah, do a bit of formatting on it, get it cleaned up, and then lastly Ah, any formulas we need to put in to calculate. And then after that, we'll put in some sort of dummy data just to demonstrate how it works and how you can actually use this out in the real world. So that's about it. So, uh, I think we're good. So I'm gonna rename this tab, get started. We might as well go ahead and get started with only up to two minutes. Eso you double click. I'm in a tie. Call them tabs. Their sheets. Ah, sheet name, You can rename it. Well, let's call this fixed assets. Makes a nice and easy right now. The way I'm envisioning this is, um, for a company that has different classes of fixed assets, it's gonna be best if we have a separate sheet for each class of asset because, um, one the depreciation period typically differs between classes of assets to it will just be a lot cleaner. So when we actually have to go and make our journal entries, it'll be cleaner. If we have, you know, five different sheets that the track, five different asset classes, then if we put them all on one and then have to sort of separate them later, it'll just be easier if it wants to make your life a lot easier. So what we're gonna do is we're gonna build this for just one asset class, and then it's I mean, it's a simple exercise to copy it and created for several. And then really only difference would be the appreciation period, Uh, for those up, Um, so maybe this isn't fixed. Assets will call this fixed assets. Ah, Class A. I'm just gonna call it class safer now. Maybe we'll come up with a better name later on. Like, this will be equipment, and the different one will be computers, etcetera. All right. So, guys, the methodology that that I tend to use is, um So I first of all you to start with, period. So this will be so. I've worked in companies that book depreciation on a monthly basis, and I've worked with companies that book it on a quarterly basis. So it depends. I'm gonna build this as if this was a quarterly exercise. But, I mean, you just replace the quarters with the months and it functions the exact same way and honestly doesn't. Doesn't matter too much which way we go about it. So I'm gonna say the period. So we're gonna so down here, we're gonna have a list of periods to say, Q 1 2018 etcetera. We'll give the asset name and we will clean all this up in a little bit. Ah, asset class. So even though we already give you a little speech, you know this will all be the same class. It's still good just to denote it on here. Just so it's, uh, you can see it tended, like to keep track of the vendor, you know? Where do we buy it? Just a za reminder. As time goes on, we lose track of those types of things. Ah, the date acquired with the total asset price. So this would include everything. This includes the full amount that needs to be depreciate. So it's gonna include the taxes, the shipping, the full amount. It costs you to get the asset their functioning up and running. Ah, well, say periods to depreciate. So again, a couple ways to do this I mean, typically depreciation is booked in, like five years. Seven years. It's typically a number of years, right? It's not like you have a 2.5 year depreciation period s, so we could either say years did appreciate, or we can say months total doesn't matter either way, guy. So I'm just gonna say ah, years to depreciate. Appreciate. I think I saw that right. And again, where you clean all this up in a little bit right now. I'm just trying to get some headers up here. Um, let's see. So then we can calculate our monthly. Well, we said it be quarterly. Um, so let's just do quarterly appreciation. Uh, beginning months. I shouldn't say beginning month will say beginning quarter, since we're going with quarter serialised 2/4. Although, actually, since we're not too far into this and I have a feeling most of you maybe book appreciation on a monthly basis, let's let's go with that with, uh, a monthly guys just part of that sort of on the fly type of thing. All right. It's a monthly depreciation, uh, beginning month to depreciate. And then let's see the final months to appreciates, then almost done. And then we'll end this video, and then we will, uh, get the formatting some of this so it looks a lot cleaner. That's final months. There were just a total assets. Um, we're going to say the period appreciation and then we're going to take you total uh, total accumulated depreciation. Alright, guys, let's end. This video is pretty ugly right now. Your thing, Chris, What on earth you doing? Ah, believe me. This will look a lot cleaner once we get through the next video. 4. Headers Formatting: Okay, guys. So first things first, let's some give me a quick one. If we highlight all these rows and if you know where you don't know Ah, few left. Click on any given row and just hold down your left click and then scroll with your mouth. It'll highlight all the roads they have selected. And if you double click on any of the, um, the lines in between basically the separator between two columns, it will resize each of them to be as wide as the largest sort of amount of text in that column. So we're also going to do something else, though, So first things first. Let's just make this a bit smaller now. Period will probably be a bit bigger. That's gonna be the month we said. Just let's just change this two month and then, um, as that name or probably a bit bigger class of a bigger vendor. That bigger date is probably fine, the last that prices find your should appreciate. So something like this, like we don't need a big wide column for a number of years, right? So what we can do here is if we make it smaller now, you see, it does that thing again where it gets kind of cut off. If you go up here in news wrap text that will wrap the text and it makes this row bigger. Now, we don't like how it's cut off, so we will just make it slightly wider. So at least one word fits. Um, the other thing we're gonna do. So let's just do it now. If we highlight this entire row, let's go ahead and center Everything. Just It looks a lot nicer. Um, so monthly depreciation. We can leave that for now. Ah, this spreadsheet probably will be longer than like. We'll have to scroll across to see everything. That's just kind of the nature of it. Ah, Well, want to wrap this one? So let's just make it smaller. Hit wrap Text. Hey, that looks pretty good. Um, same thing here. They get smaller, wrap the text. Total assets will be a dollar amount, period. Appreciation would be a dollar total. Accumulate appreciation. That's when we can probably wrap group's role. So there we go. All right, guys. So we got our headers. I believe that's all we need. Um, I mean, if you want, just little aside, something we always put over here. Just ah, would put monthly journal entry That wasn't necessarily putting in the dollar. Merit. You could, but you'd put debit depreciation an expense credits que me late at appreciation. Now, of course you could. Then put that. You know, for the category. Put in the specific names if you have the G l numbers. Um, go ahead and do that. I'm just gonna put this in bold and just gonna outline this. Um, there we go. So that should be a given. But hey, just in case, hate somebody, new starts. You know, maybe they're not as familiar, whatever the case might be, So All right, guys, we have our headers now. Um, I want to format it a little bit just to clean it up. So first things first. Let's bold everything in the hetero. I was like, too bold. My head arose. Then. Let's also give it some coloring. Now, I don't like to do the whole row and hit a different color cause little color all the way across and beyond. I'd rather just focus on the actual cells where we have information. So I'm going to do this, and I'm gonna highlight it in whatever color you care to. Let's just do blue. Ah, this is just part of the formatting of presentation, if you will. Another thing I would do, I would Let's go ahead and just, uh I'll show you what I'm gonna do. So if you highlight everything again, come to your borders button and go all borders. So this is completely your choice. Ah, I like Korean. The separation, and then it just presents a bit nicer. Alright, guys, let's see here. What else can we do to this before we start sort of checking in with some actual data, and then we're gonna have to put in some formula. So the way this will work is obvious Here, show you with some data, but we'll put in the months to say it's, um January. Um 2018 will assume is 2018 so asset name. So we said this is fixed assets class A. Maybe this is going to be, um um computers. So, you know, another field you might want include his location like, because I really envisioned using this not just the track and calculate appreciation, but that but actually know where your assets are keeping a running log of them. So you might want to write Asset name and I want to say where it is. So maybe it's like a Dell desktop computer, and it's, you know, Julie new Mars. Whoever you know, employees actually has that computer. Uh, and then also, a lot of times people tag different assets, actually, give it a number they sign in the number, Not the most horrible idea. Honestly, guys, So I'm not going include those types of fields here, But I encourage you to think about that. If that's something you do track in your own company to do that as well. Um, let's see, we're up to about five minutes. Let's go and cut this off and then we'll start putting in some dummy data and just see how that goes. And then, obviously, at some point here, we're gonna have to calculate ah, few numbers 5. Data Entry Part A: Alright, guys. So maybe. All right, let's just pick this up. Let's just put in. Ah, some dummy information. So Dell desktop. Um, maybe it's a model 6500. I have no idea if they make a model 6500 again. We're just doing this for fun. Um, this will be computers. The vendor was del dot com. Date acquired was maybe a 1 15 18 Don't last that price. Let's say it was $2500 we're also gonna format for dollar signs and whatnot. Years did appreciate. So I believe computers were going to say three years and we might actually center that some monthly depreciations. That'll be a formula. Um, let's come back to that in a second. We'll save our formulas for for last on this Ah, beginning month to depreciate. So I mean, this is part of your company policies, so cos I typically worked in you would just start in the next month. So rather than unless you literally bought it on the first of the month, Typically, though, just a good policies. Hey, we'll just start appreciating this the next month, and that makes him a nice clean amount doesn't make a huge difference in the long run. Now it doesn't, but, um, nonetheless, it's Ah, that just makes it a little bit easier. Stuff. February 20. A team final month. So Ah, three years. So that'll be January 2021. Is that right? 1920 21 D up. All right, so make this big, and this is just for reference. Uh, total assets. So Okay, guys. So this section here is actually gonna be, um, almost its own kind of broken away from this. So I'd vision like a list here. We're gonna have a bunch of computers and all this information, right? This will just be a total for the period. Will say, what's our total assets are total period depreciation or total accumulate depreciation. So far, So, um, let's put in a few dummy data, then we can come and we'll fill in this. So actually, before we do that, one thing I am going to do is I'm just gonna Let's just put in, said x this amount of information I'm gonna break that off. I'm gonna give that its own border, and then we're gonna do this. Oops. And then likewise, he would do the same for February, etcetera. So let's actually finish making this and then we can copy it down for February and show you what February look like. And most importantly, how you just go about updating these. All right, So monthly depreciation. Well, first of all, let's do quick formatting. We know this is gonna be a dollar sign, and this is gonna be a dollar sign. Right? So if we if you hold on your click or your control button and then you click on it and then if you continue holding them control and clicking on other cells, you can select multiple cells that aren't adjacent. Uh, we're gonna click on our accounting for match up to you if you want to keep the period. Depends on, you know, typically, if we're appreciating something, it's gonna be over some threshold. You know, you're not depreciating something that was $5. So, um, whether it was 252,500 and 50 cents and make a huge difference, Um, it might be nicer. It might be easier to tie out honestly at some point. If you ever had to go cross reference this to an invoice, so I would actually encourage you to leave the dollars. Ah, and sense it's a monthly depreciation is going to be, ah, this amount divided by. So let's think about this. So a monthly amount, it's gonna be three years, but three years is really 36 months. Well, the easy way to calculate that will always be the number of years Times 12 because there's 12 months in the year. So there you go. So every month will be 69. 44 that we'd appreciate for that specific computer. Now, are we gonna book So say we do fill this up. And we have, you know, multiple computers. Looks like one of six that we bought in January. Do we book it each of these individually, I'm guessing. No. I mean, I've never worked anywhere where you would do that. You would just make an entry for the month, and it will be the total amount of your monthly depreciation, then anyone who wants to cross reference it can cross reference back to here. Um, I'd encourage you in your accounting software. Um, when you make your journal entry in the notes, wherever it might be put it across like say, um, reference, Fixed asset list, uh, you know, spreadsheet for detail or something like that does way. Anyone looking at it in the future, they want to know. How did you calculate this depreciation? Expense number you have? There you go. It's the easy reference back to this. Ah, it's set up. Alright, guys. So let's, um let's end this video. We come back, let's fill in a bunch More assets will go through that exercise that might fill up a whole video that will worry about this section, and then I'll show you kind of a next month type of thing. 6. Data Entry Part B: Alright, guys, so we can make quick work of this. So we've said this is all gonna be January. So it's up to you. You can write January the whole way down. I typically wouldn't. I mean, we've blocked it off. We kind of designated that. This will all be January starting here. This will be February. Um, so we will fill in. We do those computers. Um, I am gonna copy that down. One thing I'm just got was thinking ahead. If you just copy and paste this, it's gonna goof up our formatting down. Here s the one thing you can do Just a little trick if you copy something. And if you hold down control press C, which is what I did. You can then highlight where you want to paste it. If you don't want to screw up your formatting, just go paste values. There we go. So it doesn't pace the format of that. Sell it all because then it will just keep that one. Um, let's see. Okay, that's probably only thing. The only other thing we could so depreciation period should be the same. I would free to the same asset class that would be, um, as well. Your formula here will be the same formula. Right? Thing is, we can't pace values on a formula, so we're gonna have to kind of do these separately. So first we're gonna copy that we're gonna paste our values for the three. Or you could just type three million multiple times if you hunt. Um, actually, at the center of this guy's just cause presentation thing again. So here we want to copy the formula and not the, um and actually, we do want the format. Except for here is gonna goof it up. So you have a few choices, guys, Um, what I would do is just copy this, Um, another way of pasting. If you hold down, shift and use your arrow keys on your keyboard, you can then do that to highlight motor cells and then press control and president to paste . Oops. And I really mess that one up somehow. I'm gonna undo that. Oh, it's copping the ah, the border from up on top. Our it guys. New plan of action. Here. Let's do this. We're gonna copy this. We're gonna highlight these. If you right click, You can also get to Space Patch Special. Ah, here. We just do formula. So the formulas in there what's not in there, though, is the formatting. So what we'll do while it's all highlighted? Just hit this. And bingo done. That was easy enough to solve. Okay, So, again, since this is January, we're going to start depreciating February. This will actually be the same. Um, we're gonna do our pay special and do values. Ah, price will change. Data change, vendor. Possibly change. Okay. So, guys, let's put in some dummy information. Ah, let's say this is Adele laptop model. Um, whatever. 99 hundreds. This is a, uh, let's see. Was that aces Air freedom trying to think of the brand name? It doesn't matter. Um, actually, let's say this is an alien warehouse. Not too many companies, probably by alien work computers. If you're not familiar with them, they're basically owned by dollars. Kind of souped up gaming computers. Um, that are fairly expensive. Um, let's say this is the model, um 12 hundreds. Um um what other brands to say? We got a Toshiba laptop model X 94 a. Let's see, let's say we got a Microsoft Let's say we got Microsoft Ah, surface tablet. And we're gonna include that with computers, surface tablet, and then we bought Ah, let's just buy another dust up. Just stop. Um, sure, the same model really doesn't matter. Guys, this is more for demonstration purposes. All right, so this is gonna be down. This will be down. Maybe we bought this at the Microsoft store in person. Um, alien, where we probably bought on alien where dot com believe that would probably be their sight until she even say we bought this at OfficeMax. So part of the reason. Having the vendors and again, if you ever, ever to cross references, go dig up the invoice. You know what you're looking for? You're looking for an invoice from dell dot com. Uh, again, I would also I mean, as I mentioned before, I'm not going including here. But if you tag each of your assets with a unique identify, um, definitely include that in here. Also, if you want to put a location like, where is this Dell desktop who is currently using it in your company? Okay. And date acquired really to I mean, we already said we was gonna start to appreciate the next month, but date acquired helps as well. Just with locating back up any information you can give yourself. Um, So I'm going to copy the format down once we get done here on 20 again, Doesn't this part? Doesn't matter, guys. Okay, so let's make afraid it's gonna copy. Let's do this. So if you go to this and drop down, come them or number formats, it's gonna move this over here. I'm not sure if it will change on the fly. So if you click date, you can choose the different date format. So if we click this, um, and it'll show you what a date would look like So there we go, guys. Actually, the only difference is that one has 2018. Um, so let's just so we can do a few things we can use our format painter. Except then it gets rid of our bold. So there seems like you can't ah, can't always win. You can always have it all at once. But that's okay. It's a quick fix. Alright, guys, let's Ah, throw in some quick numbers. Alien wears or super expensive. This is a $5000 laptop. Let's say this was a cheap wine. It's a $600 laptop. A tablet. I also I don't know what they cost. I'm gonna guess I don't think there's prices. IPad. So I'm just gonna say this costly 400 bucks. Um Ah. Dell desktop. This is, say, another $3000 computer. Now, Um, as I format these typically you're fixed asset policy will dictate. What's that threshold of what you'd appreciate. You know, not everybody appreciates a $400 you know, computer or a surface tablet. You know, you probably just do a one time expense, and that's fine. Um, I'm just putting in this thing here again. For demonstration purposes. You obviously go and follow whatever it is that you're company policy is, uh, okay. But since everything falls under the computer category were depreciating, Anything computer related for three years, appreciations all calculated for us. Since we copy that down, you'll see. So that works perfect. So I cut those video off guys. When we come back, we'll fill in this section 7. Calculations and Entries: Alright, guys. So up next. So this whole boxes for January, right? So what we need is just a total depreciation for the month. So let's just start. What weaken do all of these? So total assets. So we're assuming this is our first month. Also, guys. So this is the start of the list. Obviously that makes sense, I hope, Um so the formula would write is equal some open bracket. And if you use your mouse to click and hold, you can then highlight multiple and then close your bracket. So if you're not familiar with that formula, um, you know, it's just kind of one those basic formulas, but it's a quick way to add up a group of numbers rather than individually adding each cell to the next cell. Um, it's the some foreign well, and then you put in the range and it'll add all of those up. So that's our total assets that we have currently. So when we get to February, what we'll do is we'll add February's, um and they will add it to the previous month assets as well, cause that's already we really just want that total asset. We're not too, too concerned about what we added in the period. I mean, we can come here and look at if we need to, but really, we're for our balance sheet and whatnot were skin the total assets period appreciation. So equal some. That's kind of the same thing, guys. And I'm gonna do it a different way this time. So using my keyboard, if I arrow over and at the first cell, and I hold down shift and narrow down So holding that shift keeps that kind of locked on so you can highlight multiple cells, and then I'm gonna close my bracket and hit Enter so exact same thing as if I had just highlighted it like this. But holding that shift and using arrow keys accomplishes the same. So that's our period appreciation, guys. So that's what we're actually gonna book in the period. Four depreciation. That will be our entry here. Our total accumulated appreciation. Well, that at this point, is just going to be this. Ah, fairly simple. Okay, guys. So hopefully you see how easy that is. I mean, is it really a big deal? No. So but what have we accomplished while we have a nice way to track all over assets. We know what we have the class where we got it, how much we spent on it, the monthly depreciation. And then we're I mean, one thing we could add his book value if you wanted to. Um, not a horrible idea. Just so you have it on here for reference. And I mean, that should then definitely tie out to your balance sheet by this class. So, um, I'm thinking I mean, even if not on your sort of summarized balance sheet, but on your maybe a detailed balance sheet, Um, when you look at it, would you would have broken out by class, So you'll have, you know, fixed assets dash computers. Um, you know, buildings, blah, blah, blah, equipment like machinery, whatever asset classes apply to your business. Um, So, actually, guys, let's do that. Let's just add one more. That will be a good exercise, too. So we need to get rid of that border on the the right hand side. So if you come over your borders and go more borders, So here's a visual representation of the borders right now for what I have highlighted. So the top one has a thin line. The bottom has a stick loan. Uh, there's some cells that have, um, the horizontal line. And there's some that have the vertical in the way because it's a light shaded Great. I mean, some do some don't. All right, so we just want to get rid of the right one, so just double click it. So actually, we click it, it goes solid, or I think just one click get sort of it. So if we look now, got rid of that. So what we do want to include is one more. So I'm gonna do a few things here. First of all, I'm gonna move our little journal entry. Um, actually move it down here and next. So it's more in line with this. Totally up to you. If you want to put in the debit and credit amount, You actually want to copy this down each month? Um, no, no, not the most horrible idea. And honestly, then, if you could then actually write in your journal entry number as well. So say this is, um, entry, um, 52 05 Then you know, if you ever have to cross reference this entry to your books and you're good to go. So goes the equals, your period depreciation and that really should equal also your period appreciation. Perfect. So what you'll do when we create February's will copy this sort of same thing and make sure it's all linked up correctly. So we want our book value is what we want. So let's do our same tricks here. Let's wrap it. I sure let's just copy of this entire format. Perfect. Um, let's just to make our lives easy. Let's just do this. Let's see, How do we want to go about this? Guys, let's do this because we kind of the bigger, darker, bold, if you will around that Ah, here. We could just pick the right hand side, right border there. We got quick. Is that so? Book value, What's book value gonna be able? It's going to be our total assets lesser accumulated depreciation, so that should be our book value. So that should tide your balance sheet for this asset class. Um, if you have a very detailed one that breaks out, you know both the total assets and then they accumulate appreciation. You see, see that number? That number That number on your balance sheet. We have our journal entry which ties out to our accounting system. And perfect. So this is someone I'm assuming Possibly yours, uh, job each month. You know, it's part of what you do is part of your month and closes to complete this. Alright, guys. So that's that's a January. You know, we start, we got our first month. So, um, let's go ahead in the next video. We'll do February. We might not worry too much about the data that's in there, like we'll put in dummy data, but we might just actually use the same data. Um, just for demonstration purposes, I really just want to show you how to update this section more than anything. 8. Adding Additional Months: Alright, guys. So actually, I realized something really quick as I was thinking about that. So this is all stuff we bought in January? Um, we literally it's right in front of our faces. We said we were gonna start appreciating that till February. So, um, so this, if anything, is February's entry. So let's go ahead and change that. So, um, let's make a few changes here. So first of all, let's put ah, put period. And then we'll say this this February 2018. I actually want to write that. Ah, another little tip for you If you put ah apostrophe before you type anything, it forces excel to just leave it as you type it. So even though I typed February 2018 and it converted it to a date, put an apostrophe before that and it will just leave it as it is. Okay, guys. So this is now February's ah amount. So what? We also might want to write in here. Since this is January's, you know, purchases. That's right in here right above this. Since we don't need all the space, I mean, we don't there's no need to have, like, a running total or anything. Um, solicit will be, um, as of February 2018. Perfect. So problem soft. So now we know Make this entry February, uh, easily handled there. Alright, guys. So let's copy this now. So now we have January is perfect. We're off to the races. Let's just copy all of this. I'm gonna come over here. Here. I'm a press control V to Paste. Perfect. So let's update this a little bit. So first of all, this is February. Now, I'm just gonna leave the same stuff just out of, um, you know, I'll update the dates here. Let's say we bought the exact same computers for whatever reason, it's more just to save us time, guys. I mean, it doesn't really matter what we, um what we bought. And actually, maybe what I'll do is I'll just put ah computer a computer, be I was gonna copy that all the way down, and I know that goofed that up. Just so when you download this, you don't look back and go Now what? Why was that all the same? Okay, now I remember. Um, let's see, the quick way to do this is to highlight the entire box and just do this again. Perfect computers. We can leave this dates. Let's just do 15. 18. Um, I mean, again, dates don't matter. It's completely cross reference. Perfect amounts ls turned some different amounts just for funds. He's just So this changes as well over here. Um, that'll be important. So let's say this was 7000. Um, 4000. 1600. 1000. This was 3200. So, period, be the same three years. Appreciate my depreciation amounts. Calculate automatically, um, so month to begin depreciating. This will now be march. And then this would be February. Now we want to copy this next. Let's just make the slightly bigger It looks like s We want to copy those. And then we can pace this as if we do the right click as the values. And there we go. So didn't goof up that Ah, this will also be march. Now, I know this takes a little bit of sort of manual updating, if you will, guys, But I mean, really I mean, you're doing this once a month to keep track of your assets, so it's not a horrible exercise. Now, you want to check your formulas here? Because we assume, like I did the exact same. You know, I copied this box down here, but say you bought eight things. I want to make sure your firm it was there. Still correct. So if you highlight any cell and press the F two button, it will bring up the formula editor, and it will also show you it'll highlight what that formula is doing. Likewise. He could do it here, so Ah, so here. Same thing. Let's see. Yep. That's highlighting that this. Now, um, we're gonna make a few changes here, guys. Okay, so, first of all, total assets, so total assets would be all of those, but it's gonna be plus this amount. Uh, now, if you really want to get tricky if you prefer, if you want to have something that's, you know, um, assets purchased this period and then separately, you have total assets. Totally fine. Um, I don't know that there's a lot of need for it, so I'm just gonna put it Is total assets. Um so your period appreciation will just be the period, your total accumulate appreciation and I hit F one by mistake. Which brings up your help box, by the way, just get rid of that. So this is now saying period appreciation. But it's also gonna be plus your previous months accumulate appreciation, right? It's accumulated on their book value. So let's think. Is this correct? So this is saying, Take our total assets, which it is. It's both January and February's and subtract er total accumulate appreciation so perfect that formula works still. So we just had to update this one in this one. Now for our entry. Now, this one should refine its the period's depreciation. Ah, same thing here again. Just you want to check all your formulas that mean any month and you want to go through Ah , Solicitor B for March. And this is probably a different entry number 60. There we go. Perfect. So goes simple. Is that so? Now you have on ongoing asset lists. You have the months you purchased it, the asset information when you're actually start appreciating it. So this would be your entries than for, you know, the following month. Obviously, um which in some ways it's kind of nice because, um, so say it's fate. What you can do is make it part of your month and close. So part of month and clothes for February is to make sure that your asset list is updated. I know you already have your amounts for March. So another part of your clothes is to book the previous um ah mahn century. So say it is, you know, March 2nd now near it in the middle of your month and close. All right, so you can update this with all of February and then because you're doing your February close, you can now book you know, the century, or you could have booked in advance. I mean, book it. Well, you know, as soon as your schedules update, there's nothing stopping you from booking ahead for the next month. And so, uh, that is that That's fixed assets. I'll come back. It will do a little bit work clean up and work on this in the next video. But for the most part, guys were pretty close to done. Simple is that 9. Adding Additional Categories and Wrap Up: Okay, guys. So, um so we mentioned this was for one asset class. So just to show you I'm assuming you might mostly might know this, but if we want to copy now this sheet and create a different sheet that we're going to use for a different type of asset class Um, if you right click on the sheet, go to move or copy, uh, it automatically defaults to move. So if you just click on create a copy that will make a copy of it. That's basically saying, Where do you want it? Before this one, the current one were on. Or do you want to move it to the ends? Let's move it to the end, which is really just right next door to it. Um, guys, I'm going to go ahead and, uh, rename this, and just so you have kind of a clean one to work with, So instead of fixed assets computers and honestly, you don't actually have to write fixed assets, we already know this is our fixed asset tracking. Ah, sheet. So what other types of assets might we have? Well, there's lots of might have office Ah, furniture that we wanted appreciate what might have company vehicles. Um, so one thing. Actually. Think of a company vehicle. This spreadsheet, Obviously one thing I didn't address is very much based on the straight line method of depreciation. It's fairly common. Um, if you do want to do something different and accelerated method, you're gonna have Teoh, you know, monkey with the formulas a little bit and probably have to add in hoops. Um, I didn't type a name here yet. That's why yeah, you have to add in some fields to sort of calculate that properly, um, for the most part, to try to keep this fairly straightforward. And for the most part, most your assets probably aren't appreciate it. I don't know too many. I mean, in my experience, I work with a lot of smaller companies, so they're not buying things like vehicles that need sort of accelerated depreciation method. It very much is like equipped, like office equipment, computers, office furniture, etcetera. So, um, let's just name this. I'm trying to think of a good category that ah, you know, maybe, and I start up or smaller business might have You might have. You know what? You might have you might have patents might have intangibles. Um, let's just call this patent. So even if you had intangibles, you'd probably want to say specifically what they were. Um, So I'm going to strip out all of this. Um, Well, strip out this now. Intangibles. Ah. So let's see all of this. Honestly, Other in the entry numbers I'm just gonna put in X is there. Um, intangibles can be interesting. I mean, what's the valuable life of it? Typically as well, You might have to test for things like impairments. Actually, intangibles might not be the best. Now that I'm thinking about might not be the best example. Um, just based on Maura kind of current accounting rulings, the way it tangibles air handled. So they're not always going to be just a straight line method is what I'm getting at. So let's go with something more simple. Let's just go with the building he went from. Ah, and I'm gonna rename this Will YouTube video up there? Just computer. So rather than fix, that's his computer. Six passes buildings. We know this is our fixed us that list. Alright, guys. So computers is fine buildings, you know, So this one might be, you know, property. Um, at you know, 123 ABC street, Um, his buildings vendor coming. Maybe the real estate company bought it from this one's gonna be a little bit more. You're probably not buying buildings every single, you know, week or anything like that. Right? So, um, vendor, So I'm just gonna leave that blank. Let's leave that asset price. Let's say it was 3 50,000 years. Gonna appreciate it over. Geez, I don't even know 30 years. Not too many companies by buildings anymore. So it's I'm trying to think of what the proper depreciation period would be, Um, and so on. So book value needs to be a bigger perfect. So in a case like this, you don't need all of this blank space so you could do a few things. I mean, you can just chop this out. You want to be careful about this? Um, it's up to you. I mean, you could certainly clean this up, because how often they're gonna be buying properties and probably for February is a similar situation. Like, I wouldn't mind having a spreadsheet like this knowing full well that you know it's gonna be a lot of blanks. And you could even right in here. Um, none this period. I would encourage you to do something like that. It just shows people that you at least looked at this and, um, address it, if if you will, so 30. I mean, here we have nothing. Now you notice that divide by zeroes. That's because it's trying to cock a formula. But there's no values up to you if you just want to leave in 30 just for presentation value , if you will. Um, just so these air kind of kind of blank Ah, same thing here up to you if you want to leave them in or not. The main thing is that you do want to get these boxes over here. You want them to be correct on. Let's see. Let's see, how are are our totals over here work? So that worked out Well, it grabbed her total. That's because we delete it rose from kind of the middle. So say we had to leave that this bottom row, we would have good things up, obviously would have deleted are, um, formula. So there's no appreciation there, so total accumulated is correct. And our book value stays the same. Let's see. Ah, One thing we did do was chop. Oh, you know what we did, guys? We chop part the header part of ah, are her journal entries off heaven. So we have a few options. We could just recreate them or we have ah, back button. So this will go back and undo button if you will. And you can undo multiple things now quickly to do that as well. Keyboard shortcut is controls the something hit controls, the controls, the It's kind of undoing those. I'm just keep pressing control Z, I want to get back to where? There we go. So right now, if we scroll all the way over here, we see that, Uh, yes. We can't delete that many rows and still keep our formula so we can delete one row. If we want I should say delete. Those are formulas. Just lose our ah journal entries. So you might have to do something like this. Guys, if you want. If you want to keep the same presentation that iss so one of those things. Hey, live and learn. Um, in excel. Let's get rid of these. And there we go. So I'm going to save this. So, um, for all intents and purposes, guys, we're done with the spreadsheet. I mean, um, you get the point now. You can copy this. Change it to whatever categories you need. Um, add on each individual month as you go. Make sure your formulas air. Correct. Definitely. Want to do that. Make sure you book your entries. You mark what entry it is. Make sure you tie out. You just want to make sure your accumulated, sir. Correct. The calculations are correct. And that thes match your balance sheet at the end of you know, your clothes. I mean, let's re part of your clothes sort of reconciliation. So, um, that's what I would encourage you to do with this. And other than that, guys, I think that is it. So if you have any questions during the course, Uh, definitely 70 message. Tell me what point in the video. I was what I did. And like I said, I'm gonna make this available for you to download. So it'll be attached to the course conclusion video, Um, as a downloadable item. So please feel free to use this, um, or go in and look at it and see what I was doing in the video to try and figure anything out. So thanks so much, guys. And I'll do a final course conclusion sort of. Ah, video for you guys as well to wrap things up. 10. Course Conclusion: Congratulations, everybody. You made it through the course. I'm glad you stuck with it. And by now you should have yourself a fully functioning fixed asset tracking spreadsheet. I'm glad you're able to follow along. And if you want, you can also download the one that we built in this course. That should be the attached to this video or to the course of the whole eso. You can use that unmodified for your own needs if you didn't want to build your own. So hopefully gained a lot from this course, I encourage you to do two things. One thing is to check out my other courses. I have quite a few courses, all about accounting, finance, entrepreneurship and building other models in Excel as well. Things like budgets and forecasts as well. I love hearing your feedback. So if you could take a few minutes once the courses over and leave me feedback for the course, anything I could do to help out with those five star reviews, just send me a question if you have any happy to answer them for you. That said, thanks so much everybody. I look forward to being your instructor on hopefully more courses to come