Formulas to calculate sales tax

Questions

You need to be a member to view comments.

Join today. Cancel any time.

Sign Up

Course info

24 lessons / 2 hours

Overview

Hi there, Welcome to this Microsoft Excel BootCamp. Together we’re going to learn how helpful Excel is in nearly every part of our professional lives.

This course is for beginners. You do not need any previous knowledge of Excel. We will stick closely to the powerful built in features of Excel and will not get bogged down in confusing code & complicated formulae.

This training course is project based. We start with a simple company branded invoice and explain how to calculate totals & tax. Using a complex and messy spreadsheet we will clean it up using Excels automatic features. With our new tidy data you’ll learn how easy pivot tables can turn long and hard to understand information into simple tables & beautiful graphs. Before you’re finished you’ll be making helpful drop down menus to help you fill out & sort your financial data. . You will learn how to turn uninspiring profit & loss statements into a good looking, easy to use documents. 


Class projects:

  • Create a quote & invoicing form.

  • Cleaning & formatting messy imported data.

  • Inventory spreadsheet.

  • Pivot tables

  • Regional Sales Report

  • Profit & loss spreadsheet.

  • GST & Tax calculations

  • Graphs for use in Word, PowerPoint, InDesign & Illustrator

  • Creating spreadsheets that work within Word documents.

Who should attend?

  • This course is designed for people who have little or no previous experience in Microsoft Excel. You will start right at the beginning and cover all the basics.

  • Only basic computing skills are necessary - if you can send emails and surf the internet then you’ve found the right training.

  • By the end of the course, you will be producing real world results with Excel.

What do you need?

  • No previous Microsoft Excel experience necessary.

  • You'll need Excel 2016 installed on your laptop. The standard installation of Excel 2016 or the Microsoft Office 365 version is fine.

Course duration 2 hours

Daniel Scott

Daniel Scott

Founder of Bring Your Own Laptop & Chief Instructor

instructor

I discovered the world of design as an art student when I stumbled upon a lab full of green & blue iMac G3’s. My initial curiosity around using the computer to create ‘art’ developed into a full-blown passion, eventually leading me to become a digital designer and founder of Bring Your Own Laptop.

Sharing and teaching are a huge part of who I am. As a certified Adobe instructor, I've had the honor of winning multiple Adobe teaching awards at their annual MAX conference. I see Bring Your Own Laptop as the supportive community I wished for when I was first starting out and intimidated by design. Through teaching, I hope to bring others along for the ride and empower my students to bring their stories, labors of love, and art into the world.
True to my Kiwi roots, I've lived in many places, and currently, I reside in Ireland with my wife and kids.

Downloads & Exercise files

Download Exercise Files Download Completed Files

Transcript

In this video, we're going to add our amounts that has the dollar signs. Great! Then we're going to make an option where we add something to our quote. Say, adding an 'InDesign Course'. Then we add some money to it. It's going to add them all at the bottom for our 'Subtotal', then it's going to calculate the tax, and give us our total all together. So let's go and do that now in this video.

First thing is, we're going to put in some dummy text. So over here, it's going to be 'Excel Course', and I'm going to charge you, let's say, $300. Now, at the moment it doesn't have the currency symbol, so let's change that. So, instead of selecting just this one, I want it to be for all of them, so we're going to select all of these by clicking, and dragging across all of them. And then at the top, where it says 'General', under 'Home', drop that down, there's a basic one in here called 'Currency'. You might be lucky, and it might give you the right symbol. Mine's given me Pounds. I don't want English Pounds, I want either Euros or Dollars, so you go and change it. And there is a drop down here, to these options, but if you want the full list, drop this down, all the way down the bottom where it says 'More Number Formats'. Be on currency. And you can see, here's the symbol that I want. You can drop it down, there is a bunch in here.

So, depending on where you are in the world, you'll find your option in here. I'm going to find 'English'.

And I'm going to use 'English' but 'United States'. Great! Let's click 'OK'. It's applied a currency format.

It hasn't changed the number at all, just knows that it's the dollar signs.

Next thing I like to do is, I'd like to do the 'Subtotal'. 'Subtotal' is really easy, just all of this added together. So what we'll do is, we'll put in another one. Let's say you want to do a Word course as well.

I've got a Word course, go check that out. And this one here is cheaper, I'm not sure why.

One thing we'll do is, currency is right aligned here, I want them all to be right aligned. So select it all, 'right align'ed. Just to line up with these totals down the bottom here. So our first bit of formula worked.

Just to add everything in this column, and put it here in the 'Subtotal' where we had the text.

Next, we're going to do something called 'Autosum', the most common and easiest Excel formula to do.

And it appears in lots of different panels. You can kind of see, I've got it selected down here. He's down the bottom here, nice and small. If you've got a smaller screen you might have to go to 'Formulas'. Here he is, right at the beginning. Just click 'Autosum', and it's gone up, and reached up, and says, "Would you like all of this?" And it's close.

So I'm going to say, "Kind of, I want you-- actually I'm going to drag across them all above these. There's no point having that one in there. So, just drag across all the ones you want. You can manually type it in here. Can you see, it's running from column I, along the top, 18, which is there, so I18, all the way down to I35, which is this last option here. I'm going to click return on my keyboard. And you can see there, it's added it up. It's added the dollar sign, as well as made it the right currency. Awesome Excel stuff. That's it for adding things up using 'Autosum'.

Let's calculate the 'Tax' next. So, to calculate 'Tax', click on the 'Tax'-- you might call it VAT, or GST or Sales Tax, whatever you want to call it. We're calculating some sort of tax, right? Click in this cell. And up here, in our 'Formula' bar, we're going to start with equal, '='. This is just telling-- this is really important, it tells Excel we are not typing a word, we are typing our little formula. And what I want to do is, I want to take this 'Subtotal' here. So I'm just going to click off with my mouse. You can see, it's added to there automatically. I can just type it in. And I want to times, '*' this with my tax rate. Tax rates can't be typed in as percentage in formula. It needs to be a decimal place. So it's going to be zero point something.

And you need to find out what it is for your country, or state in your country. Where I'm at, at the moment, Ireland, it's 23%. And if I hit 'return', I have that 'Tax'. And, in New Zealand it's 15, which I thought was high, went up to that from 12.5; 23 is a lot higher. And if you're in some states of America,

I know it's 7.5.

And this is, I guess it's a little bit interesting, you need to put in a little zero, '0'. '75'. If you don't, and if you put in just '75', that's going to be 75% tax. That's pretty high. So it needs to be '0'. '0.075'. Now it will give you 'Tax'. Nice!

Let's add these two together for our 'Total'. So I'm going to click in this 'Total' cell. And I'm going to-- there’s a couple of ways I can do it. I can do 'Autosum', or I can just do '=’. And I'm going to say, you…

Can you see, it picks it there, I'm going to hit plus, '+'. And I'm going to click you, and then I'm going to hit 'return'. And that’s going to give me my 'Total'. My 'Subtotal' plus my 'Tax' equals this. Happy days.

Let's see if it works. Let's say you've done this, you're going to now do my Photoshop course. I've got one of those too. But that's super awesome, and it's super expensive. Say, it's '700'. You can see, it's calculated it. Calculated the 'Tax', and has my 'Total'. Happy days.

All right, let's get into the next video. We will look at adding and removing these columns in case we have extra stuff that needs to fill it in. Let's go do that now.

  • Powered by Marvin
  • Terms of use
  • Privacy policy
  • © Bring your Own Laptop Ltd 2024