Using MS Excel to Solve Real Word Problems

  1. Order of Operations and Basic operations in the spreadsheet
  2. using edit fill down edit fill series and central tendencies formulas
  3. modeling percentage growth problems using Excel

 

 

Day 1. Order of Operations and Basic Operations in the Spreadsheet

Introduction

MS Excel is a spreadsheet. This means that it displays data in a table. However, you can do many interesting things with this table. As you read through this packet, you will learn about using MS Excel so that you can answer some amazing real world questions. In contrast to Javascript, MS Excel can answer most of the same types of questions without doing any complicated computer programming.

Introduction to Formulas and Edit Fill Down and Edit Fill Series

 

 

MS Excel can do great things with formulas. For example, you can add two numbers together. To show how you can do this using a formula, please launch excel and enter 2 into the very first space in the table. Each space is called a cell. Enter a three in the cell to the right. Then, move into the third cell over and type in "=A1*B1". 
Then, press the enter key. The table should display, "6" and not =A1*B1. However, if you use the arrows to move up and down from the cell, you should notice that when you have clicked on the cell you see "A1*B1" but that when you have clicked on another cell, you see only "6" . You just entered your first formula. However, it may not make much sense yet.
To make sense of formulas, you need to take a close look at the table. You should notice that each column has letters at the top. You should also notice that each row contains numbers. The letters and numbers refer to a specific place on your table. For example, A1 is the upper right most cell. Since you typed 2 into this cell, the value for A1 is "2". The cell next to A1 is B1, because it is in the same row, but a different column. The value for B1 is 3 because you typed a "3" into this cell. When you entered the formula, =A1*B1 you are really telling the computer to find the product of 2*3 because the A1=2 and B1=3. You might also notice that the "=" is absolutely necessary to making the formula work properly. The "=" tells the computer that a formula is coming and not a value.  
You would not go through all this trouble, however, if all you could do is multiply 2*3. You could do that with a calculator or you may even know it in your head. No, you use a spreadsheet to solve generic problems. For example, you can go ahead and change the value in A1 and the answer in C1 will be automatically updated. Try it and see what happens!
Excel uses the same conventions you use when you try to solve a long and complicated problem. For example, consider the problem:2+3*3.

If you enter this as a formula, you will get the exact text. Can you determine what went wrong?

Did you notice the missing "=". Without that, excel does not know you entered a formula. This can be a major headache but it is easy to fix.
You need to move the cursor into the formula editor. It is the line next to the symbol f (x). Once you are there, you can enter the "=" and your answer should appear after you hit return.
You answer was not 15. Do you know why?

The order of operations states that you must solve all of your multiplication problems first, then addition. So, you would multiply 3*3 to get 9. Then, you would add 2 to get 11. This is exactly what Excel got. You need to be very careful to check that you and the program agree on what you want it to do. After all, the computer can not think it follows orders, from you!

 
Try to Solve these problems to test your mastery of the order of operations and then check your work using excel. Remember that your first priority is to eliminate parenthesis, then exponents, then multiplication and division and then finally addition and subtraction These problems all use special symbols that you may not be familiar with. Predict what you think each expression will be in excel and then type in the expressions exactly as they are written  to check your answer. You should be able to figure out the meaning of the "^" symbol and the "sqrt()" symbol.
Problem Prediction Computer Results Problem Prediction Computer Result
2+2*2 2^2
2*2+3 3^2
2+2+3*2 4^2
4/2+2 5^2
2*2+2*4 10^2
4/3*3/4 sqrt(16)
1/2 + 2/3 sqrt(25)
3/4*2+2/3 sqrt(36)

Homework:

In this assignment, you will need to use a spread sheet to solve a series of problems using special symbols. Please make a prediction first and then check your answer using excel. By the end of the assignment, you should be able to figure out the meaning of these formulas: abs(),%,!. 
Problem  Prediction Computer Results
abs(2)
abs(-2)
50/100%
100/50%
200/50%
3!
4!
5!
3^2-abs(4)
3^2-abs(-4)
75/100%-50/100%
5!-4!
5!-4!*abs(-5)

 

Day 2. Using Edit Fill Down, Edit Fill Series and mean, mode and median

 Yesterday, you should have learned that the most important symbol when using Excel is the "=" symbol. This tells the computer that you are entering a formula. You should have learned, in class, that the "*" symbol is for multiplication whereas the "/" symbol is for division. Remember, division undoes multiplication and vice versa. The addition symbol is "+" and the subtraction symbol is "-". You should have learned that the "^" symbol is for exponents or to repeated multiply a number. You hopefully learned that the "sqrt()" will get you a square root and you should have learned that the "abs()" command will make any number positive. The "!" command might have been difficult to recognize. This function multiplies a series of numbers, starting from the number in front of the symbol, down to 1. You will use this all the time when you learn probability so it is good to have it tucked away in your head. Finally, you should have picked up that the "%" will turn a fraction into a percent. There are many, many more symbols for you to learn, but this list is a great start and it should make it easier to learn more math in the future! Today, you are going to learn to make statistics easy using excel. You will learn to use the mean, mode and median functions on pretty complicated data sets. 
Please enter 2 into A1 and 3 into B1. Then, please enter the formula, =A1*B1 into the third cell.    
Just for fun, highlight the cells from A1 to A10. (you can highlight cells by placing the mouse into a cell and then pressing the left button on the mouse. With the left mouse down, you can pull down the mouse and the cells with change color. When you release the left button, you will no longer highlight cells)
With these cells highlighted, click on the edit menu. You should see a bunch of choices. Select the fill choice and then click on the down choice.
You should now see a column of "2"'s running down the screen. The edit fill feature of Excel is very powerful. It automatically copies the values of the cell into the other cells. This can be a huge time saver.
You may have noticed that there is another option under the edit fill mode. To learn more about this feature. please highlight the cells from B1 to B10 by repeating the procedure you just used. Then, return to the edit menu and again select the fill mode. This time, however, select the series option and not the down option.
You will see a little dialogue box. Just click on the ok button. You should now see the first ten numbers, following the number 3. The fill series feature is very powerful. It allows you to enter values that increase in a column without having to type in every value.
After selecting the ok button, you should see the values in column B like those is this diagram.
Now, proceed to cell C1. This cell has a formula in it. Highlight the cells from C1 to C10. Now, repeat the process to fill down and you should see that each cell contains the product of the first two cells. If you move the mouse into any of the cells, you will see that there is a formula in each cell, but  each cell is different. The numbers math the row that contains the cell. For example, the formula in cell C5 reads, "=A5*B5". This built in feature of Excel allows formulas to also be used with the edit fill feature. This is extremely powerful when you need to perform a task repeatedly. Which we will tomorrow. For today, however, we are only going to use this to build data sets to analyze using some built-in formulas in excel.
 Do you have trouble remember which is the average when you see a test question asking for the mean? Well, excel understands when you type average. Try this, please go to cell A11 and enter this, "=average(  
 Then, take the mouse and highlight the values from A1 to A10. You should see them appear in the formula editor.  
 When you click enter, you will see the average, which is 2 because all of the numbers are 2. However, why don't we repeat this process in B11.  
 You should now see the value 7.5, which is the average of this data set. You are probably thinking that you should also find the average in the third column. There is a short cut for this formula too!

You can edit fill right. Try it by guessing where you will find this command.

  
 You might have forgotten some of the steps so if you did, read the next few sentences carefully because most things in excel work the same so you can apply your learning. You want to first highlight the cell that has the formula you want and the cell that needs to get it. The cells should be side by side, with the cell that needs a formula to the right of the cell with the formula. Once the two cells are highlighted, proceed to the edit menu and select fill. You should notice the right option and then all you have to do is click on it and you are done. The correct formula will appear in C11, except it will be custom written for column C. This saves a lot of time when you can get down a few of these tricks.

 

 

  
This process is the exact same to find the median, or the middle of a ranked list. Try to enter a formula for the median and then use edit fill right to save time!  
Classwork 
 Make a list of numbers that is 100 numbers long that contains only even numbers and find the average  Make a list of the first 100 multiples of 3 and find the average
 Male a list of numbers that is 100 numbers long that contains only odd numbers and then find the average  Make a list of the first 100 multiples of 3 divided by the first 100 multiples of 5 and find the median
 Make a list of the first 100 perfect squares and find the median Make a list of the first 100 multiples of 7 divided by the first 100 multiples of 8 and then find the median

 

Homework

a Prime number is divisible by only itself and 1. Use Excel to find the first 50 prime numbers. Hint...Make a long column of numbers and a long column of factors and see if the division gives a remainder or not.

 

Day 3. Budgets and Decision Making using percents

Modeling a percentage growth problem is fun and interesting because it has so many real world applications. A spreadsheet makes modeling percentage growth really easy and we are going to look at three different ways to model percentage growth using formulas.

First, lets start with a simple task of finding a percent. You should already know that a percent can be found from a fraction using the % symbols. Before moving on, however, you should remind yourself that you need to be sure that the spreadsheet solves problems the way you want. So, the formula for a percent is (part/whole)*100.
What percent is 5/10?

You probably know immediately that the answer is 50%, which is perfect because we are going to start using formulas that we know the answer before we trust the formula to work when we do not know the answer.

Please enter the following data the way it appears in the diagram. When you are finished, please press return to get your answer. 
You should now see 50 in your spreadsheet. This is the correct answer. However, this is now a program because you can change the values of your parts and whole to make new percents. For example, you could change the whole to 12 to find the percent of 5 out of 12. Let's guess that it is smaller because the whole got larger, which means five is less of the total than it was before.
Sure enough, 5/12 is 41.6667 %. We could continue this process using a guess and check technique to solve a pretty difficult problem, " 5 is 6% of what number?

You could do some fancy math to solve this problem, which we will do later today. Alternatively, you could guess and check very quickly using the spreadsheet. Since we know that the change from 10 to 12 made the percent smaller, we know the whole is going to have to get a whole lot larger to make 5 6% of something. So, lets experiment.
Try 20. Did you get 25%? You should have. This is still too large. Let's try 40. Did you get 12.5% We are getting close. Want to try 80? That gives 12.5%. So, try 81,82 and finally try 83. Which is the best?

You should see that 83 gives a percent of almost exactly 6%.
We can use the same program to solve this fairly difficult problem. What is 22% of 45?

We could start using a part of 5 and a whole of 45. This gives 11%. So, let's double that and try 10 for a part. This gives us 22.22% which is pretty good.
Did you know you can find the part if you know the percent and the whole?

Let's write another program called Find Part Percent Program.  Please enter the labels exactly as they are in the diagram. Please enter the formula "=B3*C3" into A1. Then, type in 10 for the whole and 10% for the percent. If you enter 10 by itself, the computer treats it as the number 10. However, when you add the %, the computer knows it is a percent. This allows it to solve problems for you.

When you have finished, please check to see that 1 is your answer. This is correct because you can find 10% of any number by shifting the decimal one place to the left.
Let's try a more interesting problem, like finding the part that matches 43% of 82. Did you get 35.26? That's great!  
Let's try something a little more challenging. Let's say that you must spend all of your money on a banquet that you are planning. You have $1000 to spend and you must spend it in the following ways. $350 on awards.  $400 on food and the rest on entertainment. What percent to you spend on each?

Solving this problem is similar to other problems we have done. First, enter the labels. Second, enter the values you know. For example, 350, 450 and the totals. Third, enter the formulas you need. Here, you can use edit fill down to save a little time because the percent column all uses the same formula. The only thing new is that the entertainment formula is a little tricky. There are multiple ways to write this formula. In this example, I wrote =1000-B3-B4. This will certainly tell me how much is left to spend on entertainment but I could write a more general program. For example, I could write a program to work for any whole amount. 
When you are writing a program to solve a general problem, you will want to have your cells refer to a reference place. In our case, you should change the total column from values of 1000 in C4 and C5 to =C3 in C4 and =C4 in C5.

Now, when you change the value in C3, C4 and C5 automatically update. Since the percents are based on the values in these cells, the percents also automatically update. Finally, move into cell B2 and replace the 1000 with C3 and you will have a fully updated spreadsheet that will work for any whole amount. The nice thing about this is that everything will automatically update!

Repeat these steps for a whole of $1250. What did you get?
You can also write this program to work backwards if you know the percents for each item in your budget and the total amount of your budget. You can solve this problem in a bunch of ways.

For example, this diagram illustrates one solution to the problem.
Now that we have reviewed some basic percents, we can consider some real world applications of percents. For example, imagine you work for the Gap and you are running a sale on 5 items: A dress that retails for $200, a skirt that retails for $75, some polo shirts that retail for $19.99, some khaki's that retail for $39.99 and a t shirt that sells for $14.99. How long would it take you to find 10% off each item?

It will not take long using excel. Set up a program so that you can find any discount and the new price for each item.

Class exercises  
Imagine that you are working for yourself and you want to pay yourself 45% of the profits and your employees get 35% of the profit. You spend the rest on supplies. How much profit would you need to make to ensure that you earn $782 per week?
Imagine that you are working for a school that has a budget of $1,000,000. You have 20 teachers that each make $40,000. You want to know the percent of the budget that is for teachers salaries. How could you find out?

The teachers want a pay raise of 5%. Can you do it?

 

What is the largest amount of raise you could afford to give the teachers?


Homework Problems

magine that you are working for a small company and that you have 10 employees who earn, on average, $42,500 a year. Your employees health benefits costs and average of $6,500 per year. You want to earn a profit of 100,000 per year for yourself. Your annual profits are $623,421. Health care benefits are going to rise in price next year by 6%. Can you still earn your $100,000 and pay the increase in health benefits?

What is the maximum percent you could afford to pay and still earn your salary and pay all of your employee salaries?

 
Helpful Hints:

This problem is about spending more than 100% of your earnings. This means that you need check that you are not spending more than 100% of what you are earning.

You can do this using a short cut or you can enter a formula that adds up the individual percents. Another word for adding up a list is sum. Use help feature to get assistance with this command.

Finally, the best solution to this problem requires you to think about rounding.

In business, rounding can mean a great deal. You may find, as you solve this problem, that you answer relies heavily upon and understanding of the important of rounding. Can you see why?

 

 

Day 4. Percentage Growth Problems

Today you are going to learn about percentage growth problems. We are going to start with an easy problem and work our way toward more difficult problems.

To start, lets see how we can model a 10% growth for two steps. This process is what accountants use to predict the growth of businesses.

You can set up this spreadsheet like the one in the figure.
Please pay special attention to cell B4. This is the start amount for the first step. It is 10% larger than the original amount. This is the goal of our process. To make this updateable, I set the value of cell B4 as cell E3, because this is the end amount from step 1.

Once this has been established, you can highlight the cells from C3 through cells E3 and then select edit fill down.
You can make this dynamic by linking cell C4 to cell C3 so that whatever percent you enter into C3 will work for every other cell. Try this experiment, find the percent increase you would need to use to turn 100 into 200 in two steps.
A fun thing about this program is that we can extend it. For example, highlight the cells from B4 through E4 and draw the selection down for five more steps. Then, select Edit Fill down. What happened?

How much money would there be at the end of the steps?

Did you get 1121 too?
For an experiment, try different values for the percent to make the money double at every step. What percent did you have to use?

Hint: Make the 100 double to 200 and the problem is solved!

 
Let's try a real world problem. Let's say that you had a goal of earning 40,000 before you worked for 10 years. Let's also say that you started with a salary of $20,000. What is the smallest percent raise that will ensure that you make your 40,000 before your 10th year of work?

Hint--Year 1 is the year you start so you have not worked for a year until year 2!

Ten years of work is at the end of the 10th year, not the beginning!
Try this problem. Imagine that you invest in a business that grows at 10% for 30 years. What would a $1,000 investment be worth in 30 years at this growth rate?

Can you believe it? That measly $1,000 would be worth almost $17,500. That's an increase of 17.5 times!

 

Homework

Use your program to model an easy financial plan. Imagine that you can invest $1000 per year into a retirement plan that grows at 7% per year. How much money will you have at the end of 30 years with this simple plan? Hint...where you add the $100 and use edit fill down will make this a snap or really difficult!