Hello, and welcome to the Excel Information Video for CEIS100. I’m going to talk to you about how we could obtain Excel, and how we can use Excel to solve a business problem.
So first let’s talk about how to obtain Excel. You can go to your course and click on Student Software Store, which will open the Student Software Store. In the Student Software Store, there are multiple different software packages that you can download. And you can—and there’s a variety of software you can download. For example, Microsoft Office 2007 and Visio and Access. So there’s multiple different—and even Windows Operating System. You can download these software packages here from the Student Software Store.
You also have access to Microsoft Excel from Citrix. If you go to lab.devry@edu, it’ll open up to the Citrix website. So lab.devry@edu will take you there. And if you click this plus sign, and All Apps, you will see the Excel—if I scroll down here—Microsoft Excel. When you check that box, OK, it’ll put Excel on your screen so that you’re able to use it. So there’s a few different ways you can get to Excel, and this will open up Excel for you if you ever need to use it and you don’t want to download it from the Student Software Store, although it is available for you. So it’s a good idea to install it from there.
Once you have Excel—I have a copy of Excel, and this is Excel from the Citrix environment—you can also go to Start All Programs, Microsoft Office, and Microsoft Excel. This will open up Excel for you so that you are able to use Excel for your lab or any other situations in which you want to us Excel.
Let’s go over what this problem statement is. So we’re—[break in audio]—two business problems. The first one, I’m going to give you a step-by-step guide, and the second problem you’ll do on your own. So let’s look at this first program. The first problem involves opening a daycare, and that you need to know how much it will cost to operate the daycare, how many children it attending to break even, and you want to make a profit.
So here’s some of the assumptions. You will employ four teachers at $15.00 an hour, one manager at $20.00 an hour. They will work 2,000 hours. Food or snacks will cost $3,000.00 per year. Supplies will cost $1,000.00 per year. And you want to know how much it will cost to run the daycare for a year. If you assume that you charge tuition of $8,000.00 per year, how many students do you need to break even, and how many students do you need to make a $25,000.00 profit? And then create a pie chart showing the expenses—percentage of expenses for each category.
So we’re going to go through how we might solve this problem. So I’ll open Excel. When we open up Excel, we notice that we have—if you look, you will see columns, and these are alphabetical, and rows which are numerical. The rows are horizontal, and the columns are vertical. The intersection of a row and a column is a cell.
So we are going to start typing in cell A1. I’m going to type a title for my daycare. I’m gonna call it Smiles Daycare. And I’m going to start entering in my data. I have several expenses. The costs are teachers, managers, food, and supplies. The number of teachers and managers—teachers I have four, and for managers I have one. I’m going to pay them per hour—the teachers. I’ll pay them $15.00 per hour, and the managers, I’ll pay them $20.00 an hour. And the number of hours they work is—oops—2,000.
Now, I can calculate the total value for each. Now, I know that since they’re getting paid $15.00 per hour, and they work 2,000 hours, if I multiply 15 times 2,000, that will give me the amount that the teachers will earn in a year. Since I have four teachers, I’m gonna take the pay per hour times the number of hours times the number of teachers to find the total cost for the teachers.
In order to create a formula in Excel, I can type = and I will click on B4 times—I can also type it in—C4 times—and then I’m going to click on D4. It’s always better to use cell references and actually typing in the numbers because then if you want to change the value later, like give the teachers a raise, this total value will automatically update. I need to also calculate the managers. So manager times pay per hour times the number of hours. So I found that it’s $120,000.00 for the teachers, and $40,000 for the manager.
My food and supplies do not need a formula. I already know that my expenses for those are $3,000.00 and $1,000.00. Now, I’d like to calculate the total cost for the daycare, and in order to do that, I can either add up each value—I can add up E4 plus E5 plus E6 plus E7, or I can use something called a function. If I click on the Formulas tab, I’ll notice that there are multiple functions available: financial functions, logical functions, text functions. If you look, you can see there’s multiple functions in Excel that are available for you.
I’m going to use the auto sum, this backwards E. So I click this, and this will take—this will calculate the sum of any numerical cells above or to the left of the active cell. So it automatically does the sum of E4 through E7, and these are the arguments E4 through E7, to this function. So I’ll press Enter, and now I’ve calculated the total cost for my daycare.
But if I look at my spreadsheet so far, it’s looking kind of bland. So we want to make it more interesting and look a little more professional by adding some—maybe some color and some highlighting. So I am going to actually select all of these cells, and I’m going to use this paint bucket for the fill color. That changes the fill color. This—[break in audio]—text color, maybe I’ll make that yellow and a little bit bigger. This is the title. I’m also going to use the merge and center. When I click the merge and center here, it will merge all of these cells into one big cell, and it has the title in it.
I’m also going to highlight these cells and add the same color scheme to it. Then I’m going to put borders around these. The borders is here in the font, and this will separate the cells a little bit more. As you can see, this Total Cost of the Daycare text is spilling out over into the next cell. So what I can do is I can double click between the A and the B, or I can even just drag to make it wider. But I’m going to double click, and it’ll fit the largest item. I’m going to highlight this in yellow so that we can see that this is the total cost.
Another thing I’d like to do is to format these as currency. Okay, so now I have my total cost for the daycare. There’s a few more things I need to do in my problem statement. I also need to find out, of my tuition of $8,000.00, I need to know how many students I need to break even, and then how many students to make a profit, and then create a pie chart.
Well, the number of students to break even—[break in audio]—the number of students I would need in order for my expenses to equal my revenue. I am going to go ahead and copy this and place it down here for my analysis. So this is my analysis. And I need to know if I charge $8,000.00 tuition—change that, too—I want to change this—charge $8,000.00 tuition. So how many students will I need? I’m going to copy this. I like to copy, as you can tell, and I’m going to do a formula in order to determine how many students I need in order to break even. So what I need to know is that if I take in here the total divided by the tuition, it’ll give me 20.5.
Well, there’s a few things wrong with this. The first thing is that I don’t want it to be a dollar sign. So I’m going to change this to number formatting, and I can click the little dropdown.
Another thing is that I can’t have half a student—right? Any time that I have a situation where I might have a student—a fractional value here, I need to round up. I can’t have 0.3 of a student, I can’t have 0.5 of a student; I need to round up. In order to do that, I need to use a function. So I’m going to go to the Formulas, and I’m going to go to the Math and Trig, and this gives me the ceiling function, and this is where I can actually have it round up. So I’m going to click on Ceiling. The value I want is still going to be this total divided by the tuition. And the significance is when we round up to the nearest multiple of significance, which is the nearest integer. So if it’s 20.5, I want it to round up to 21, and that’s what this will do. And I’ll click Okay, and now it has 21.00.
I can go back here, and these two values allow you to decrease or increase the decimal. I’m not going to have any decimals here. I don’t need any decimals for the students’ value. Let’s go ahead and copy this now, again, and I’m gonna put Desired Profit. So in my problem statement, my desired profit was $25,000.00. Okay? That is how much profit I would like for my daycare. So now I would like to know—I’ll paste this—students for desired profit. And actually, I need to have students—I need to change this to Students to Break Even. Okay?
So I copied and pasted this formula. But wait a minute. If you look at this formula, it is E14 divided by B19. E14 is a cell over here; B19—these are not the cells that I wanted to use. When I copied this formula—which divided E8 by B13—when I copied it, Excel changed the cell references because it’s a relative reference.
If I want to copy the value and have the cells not change—it depends on what your application is—I can press F4, and now you see it’s $E$8. Now, I press F4 again, and $ will be $13. In this case, I want these values to stay the same in my formula. So if I put dollar signs, that’ll make them absolute references. Now, I will copy and I will paste this formula, and as you can see, $E$8 divided by $B$13, this is the formula that I want. This is cell E8 right here, and this is cell B13, and that’s exactly what I wanted it to do.
But I needed to find the number of students for the desired profit. So in my ceiling function here, I need instead of just having E8, which is my total cost for the daycare, I need to add my desired profit. And you’ll notice that I put parentheses around because Excel follows the order of operations that math does, where I have—I need to have E8 plus B19 in parentheses divided by B13. So this is the formula that I would use here to find out how many students I need for desired profit. Again, I want to use the ceiling function because I want to be sure that I don’t have half a student, and now I find that 24 students is the number I need in order to have $25,000.00 profit.
What’s interesting about Excel, and this is really fun, is that I could change things around now and see how it changes my results. I could charge the students more and see, “How does that change my results?” So that’s something to play around with Excel. It’s a fantastic tool for analysis and number crunching to see how you can change your—[break in audio].
The final step we need is a pie chart showing our expenses and our percentage of expenses. So I’m going to select my costs, and then I’m going to press and hold Control on the keyboard and select my totals. Now, I hold Control so that both of these cells will be selected at the same time; you can kinda see they’re highlighted. So I select these A4 through A7, press and hold Control and select E4 through E7 and go to Insert Pie, and I’m going to choose a 3-D pie. This looks really nice, this pie chart. I’m going to choose this pie chart, and I’m going to customize it a little bit. When I choose this pie chart, you’ll notice that I have a context-sensitive menu that opens up on the ribbon here, where it allows me to modify the way that this pie chart looks.
I can press one of these chart layouts and see how it looks and how it’s different. I can modify the chart title, Smiles Daycare Expenses, and I can also modify the color scheme of my pie chart. So I’ve actually done some pretty good analysis here. Let me change this back to 8,000, but I can see, based on my analysis, that 73% of my expenses go to paying my teachers. I can see that the students I need for my desired profit is 24, and in order to break even, it is 21. So I can look at my data; I can understand what my analysis is doing, and I can look at this and make a recommendation for my daycare business.
So thank you very much. I hope this helped you with Excel.
[End of Audio]