Excel is used in the business world. (More precisely, the spreadsheet is the standard tool, and Excel is currently the de facto standard brand. Most of this text can easily be used with other spreadsheets.) However, we do not assume that the student has worked with Excel previously. Throughout the course we will introduce those features of Excel we need to do mathematics and model the business problems we encounter.
While introducing Excel, we will also introduce rules of “Good Excel practice.” In a business environment, spreadsheets should be written so that someone else can easily understand the worksheet, and maintain it for future use. You should assume those same standards when submitting work in Excel.
From data we would like to compute the figures for the quarter (add the three months), the monthly profit (revenues-cost), and the monthly unit costs (costs/ units sold).
With the formula ribbon, we go to show and select Show Formulas. Since we want the worksheet to be readable by others, we add labels for the quantities we are computing, and in each cell we enter the formula for the quantity. The formula bar tells us which cell has been selected and the formula for that cell. It is generally easier to edit a formula by using the formula bar.
In this example, we have used several different ways of writing the formula. In cells E2, B6, and B7 we simply typed in the equation like we would on a calculator. Thus the profit for January is Revenues — Costs, or 3600-2700. Since we want Excel to compute this value, we put an equals sign at the start of the formula.
In cells E3, C6 and C7, instead of typing the values, we use a reference to the cell where the value is kept. This allows us to change the raw data and have Excel automatically recompute the quantities that were derived from those numbers.
In cells E4 and E5 we use Excel’s SUM command. In cell E4, we are taking the sum of the values in the cells from B4 through D4. We will come back to commands in Excel later in the section.
We want to finish our assignment by computing the Profit and Per Unit Costs for March and for the Quarter. However, we would prefer not to type any more formulas. (Typing in four more cells is not so bad, but we can imagine being told to do this for several years of data.) We will use a process called Quick Fill, that tells Excel to repeat the same formula, with the cell references appropriately modified.
We look back at the formulas and see that Excel has produced formulas where cells are in the same relative position. Profit is the value from the cell 4 rows higher minus the value of the cell three rows higher.
There is a last detail to fix in our report. The quantities in profit and Per Unit Cost are in money, so we want them formatted correctly. (They should start with a dollar sign, have a decimal point, and stop at two decimal places or cents.) We do this by selecting the cells and then formatting the cells as currency.
If we use the quick fill on a pair of numbers, Excel produces an arithmetic sequence. A pair of cells containing 1 then 4 becomes the start of a sequence 1, 4, 7, 10, … .
One of the reasons that spreadsheets are so useful for doing mathematics in a business setting is that businesses often do a relatively simple computation for a large number of cases. That means we should pay attention to formulas with cell references and the process of copying a formula from one case to another. In the example above, all of the values change from one month to the next. It is not hard to imagine a calculation where some values remain the same for many cases. Thus we want to look at the idea of absolute and relative cell references. This is a very important topic and an Excel feature we will be using for the rest of the term.
Consider the following example: Your rich uncle, Fred, decided to give you 10 shares of Google stock (GOOG) on January first 2009, with the option of receiving instead the same value in either Microsoft (MSFT) or Apple stock (AAPL). You would like to see the monthly change in value of the portfolios over a three-year period.
We start by going to finance.yahoo.com and collecting the monthly prices of the stocks, downloading the answers into a spreadsheet. When we look up historical prices from Yahoo, we are interested in the adjusted closing price. (They adjust the price to account care of splits and dividends.) That produces a spreadsheet like the one below.
Next we want to compute the number of shares for each stock. This is 10 times the closing price of Google divided by the closing price of the stock we selected.
In the formula for the number of shares of MSFT, we used $B$3 for the initial price of GOOG. This is an absolute cell reference. When we copy the formula from cell C1 to cell D1, the formula changes from =10*$B$3/C3 to =10*$B$3/D3. This formula in cell D1 asks for 10 times the value in cell B3, divided by the value in the 2 rows below the cell of the formula.
We continue our example by computing the change in value of our GOOG portfolio in the first month. That will be the share price at the beginning of the next month minus the share price at the beginning of the month, times the number of shares. For January 2009, for GOOG this becomes =(B4-B3)*B$1.
Since we have properly used relative and absolute references, we can now copy this formula to complete the chart, and Excel will modify the formula appropriately.
We note that the rows and columns can be independently made absolute or relative. Thus if we are looking at a formula in cell A1, and see a reference to B2 it means the cell one below and to the right of the location of the formula. If we see $B2 it means the cell in column B that is one row down from the formula. If we see B$2 it means the cell in row two that is one column to the right of the formula.
When we convert back to see the values, we see that an original investment of $3,385.30 would have made a profit of $3,073.70 in GOOG stock, $2,128.02 in MSFT stock and $11,826.60 in AAPL stock. Once again we use the SUM function and a cell range to add the values in the column. We also use the split screen icons in the scroll bars to be able to see the correct rows and columns.
By default, Excel names each cell by its row and column. We can use the name cell in the upper left corner of the Excel sheet to change the name from the letter/number format into a descriptive name.
The more descriptive name can be useful when constructing and documenting the process we are using for our computations. Consider the previous example with the rich uncle. In cells B1, C1, and D1, we had the number of shares of Google, Microsoft, and Apple we could have had in the portfolio. Better names for those cells would then be SharesGOOG, SharesMSFT, and SharesAAPL. We can name a cell by editing the name box at the left side of the formula bar.
One of the ways that doing mathematics with a program like Excel differs from working with a calculator is that computer programs have help features. It is worthwhile pointing out two that come with Excel. We illustrate both with the SUM function we have used a number of times.
When we call Help from the top menu, we are given a pop up window for Excel Help. It has a number of topics listed by default. It also has a bar for searching topics.
A second kind of help is the formula builder from the formula ribbon. It gives a more concise help when you do not remember the exact syntax of a command.
A third source of help is simply to do a web search for Excel help. To find how to do a computation with an exponential functions you can search for “Excel formulas exponential.”
Excel is a rich and complex tool. We will be looking at more features as we go through the course. There are several that are worth pointing out explicitly at this point.
The order of operations used by Excel differs from the traditional order of operations when it comes to taking powers of negative numbers. The problem is illustrated in evaluating , which has a negative sign and an exponentiation. In all math classes you have taken this is interpreted as or , with exponentiation done first. In Excel, this is interpreted as or 9, with negation done first. When in doubt, use parenthesis to make the order of operations explicit.
Excel also has the other mathematical functions you have used before. The functions for square root, log base 10, log base , and to the power of, are respectively, SQRT, LOG, LN, and EXP.
Excel has a number of very useful operations on collections of numbers. We start with easy ones where the name is self explanatory, like SUM, AVERAGE, COUNT, MIN, and MAX.
1.Reading check, Functions in the Business Setting.
This question checks your reading comprehension of the material is section 1.3, Introduction to Excel Spreadsheets, of Business Calculus with Excel. Based on your reading, select all statements that are correct. There may be more than one correct answer. The statements may appear in what seems to be a random order.
Exercises1.3.7Exercises Introduction to Excel Spreadsheets
1.
Produce a spreadsheet where the first 100 rows are used. The cell in row n and column A should have value n. The cell in row n and column B should have value 2*n. You should be able to do this by typing in the value of 4 cells and using quick fill.
If you want to check the formulas used you go to the “Formula” menu and choose “Show formulas”. This feature is good for debugging any problem that may happen.
Produce a spreadsheet where the first 100 rows are used. Column A should contain the first 100 odd numbers. Column B should contain multiples of 7 starting with 21.
Start with the worksheet given. Complete the worksheet in such a way that if the values of x, y, and z are changed, the other values are automatically recomputed.
We do not need to use absolute references in this case, but when we are computing something involving the , and values we should refer to cells B1, B2, and B3 respectively.
Produce a spreadsheet where the first 101 rows are used. Row 1 should be used for labels. Column A should contain integers from 1 to 100. Columns B through F should contain the squares, cubes, square roots, logs base 10 and natural logs of the entries in columns A.
Any entry that is absolute (with a $) will be fixed. The relative references (without the $) will change all the Bs to Ds because we moved over 2 columns.
Let’s consider the entries =A1, =$A1, =A$1, and =$A$1 in row 2. Do quick fill below to fill in 3 more rows and see what happens. Clearly in the first row these cells all now point to cell A1 and the value returned is 1. After the first row we get a mixture of values. Why?
Complete the spreadsheet section below so that columns A through C are complete for numbers 1 to 100. (The value for a should be a random number generated by the formula in cell E1.)
The random number will differ from person to person, but the Excel entries are as follows. Note that we need to use an absolute reference for the value of a!
In this version the random number settled on (yours is likely different) and the first part of the table looks as follows. The actual answer has all 100 rows completed.
Using the help functions to check syntax, write a formula for cell B2, that looks at the value for cell A2, and if it is negative, returns the square of it, and if positive returns its square root.
Using your favorite source on the web create a spreadsheet that has the closing price of your favorite stock on the first day of the month for the past 5 years. Compute the change in adjusted stock price for each month and identify which month had the greatest increase. (http://finance.yahoo.com/ 3
Finding the month can be done by inspection as this point (there are more sophisticated ways to do this, but that requires rather advanced Excel commands). In this instance the greatest change took place in December 2013.
Using your favorite source on the web create a spreadsheet that has the closing price of your favorite stock on the first day of the month for the past 5 years. Compute the percentage change in adjusted stock price for each month and identify which month had the greatest increase.
at the Bureau of Labor Statistics.) Compute the percentage change between January and July for each year. Which year had the greatest percentage change in the first half of the year?
