FORMULAS, FILLS, AND FUNCTIONS IN EXCEL

LAB #2 – FORMULAS, FILLS, AND FUNCTIONS IN EXCEL
BIA 3620 – Introduction to Business Analytics
Objectives
When you are done with this activity, you should be able to:
 Create basic formulas using mathematical operators and commonly-used Excel functions
 Copy data and formulas
 Use AutoFill to create series of labels and copy formulas
 Understand and use relative and absolute references in cell formulas
 Apply basic formats to cells with numbers
 Logically design and create a spreadsheet model
 Reason through basic business calculations as described in the lab
 Understand and use parameterization when creating models
Scenario
We will continue with the Digital Work, Inc. problem described in the previous lab:
Digital Works, Inc. operates a chain of 21 stores in the Northwest that sells and supports electronic products ranging from computers to smartphones. Their mantra is that they want to provide business professionals with the means to improve their productivity and communication at their place of employment. While their primary customers have been individuals in the past, they have been working on developing a business to business (B2B) model for sales to companies and institutions. As part of Digital Works’ annual analysis of product sales, management prepares a report of their monthly sales by products. Management is keenly interested in the sales of tablets in three different configurations (32GB, 64GB, and 128GB) at their stores and the amount of cash these sales provide to operations. Gross margin is a measure of this contribution, as it is the difference between revenue and cost of a product. Management has also requested some basic statistics on the changes in sales from month to month over the past year.
Final Result
When you have completed this activity, your Excel model should look very similar to the following jpg file:
Instructions
Open your Excel spreadsheet
Open the Excel spreadsheet you completed in the prior lab.
Part 1 – Constructing the Unit Sales Section
Purpose
The intent of the Unit Sales section is to aggregate sales over the different tablet configurations and provide rudimentary statistics for management. Note that sales is expected to have significant seasonality.
Video Instructions
Section Labels Video Link
Section Formulas 1 Video Link
Section Formulas 2 Video Link
Section Formulas 3 Video Link
Step by Step Instructions
1) Section Labels
a. Labels need to be created prior to formulas as you want to have a general layout of this section prior to complicating things with a bunch of numbers. That is not to say that things will not get moved around as you improve the communicability of future models.
b. Since this model is going to include monthly figures for a year, we are going to need monthly titles for 12 columns. Create month column label in cell C13 by typing in “Jan”. After pressing Enter, move the cursor back on C13 and position your mouse on the lower right
corner of cell C13. Did you notice the change in the mouse pointer? It changes to crosshairs (what looks like a plus sign). Left click on the bottom right of the cell C13
and drag to the right until you cover all the way to cell N13. Let go of the mouse button. Did you see what happened? Excel has figured out that you want the months of the year as labels from your first entry, and has populated the labels to the right for you. So that you are familiar with the lingo later, the bottom right of the cell (or group of highlighted cells) is called the fill handle and Excel’s ability to complete the labels is called using AutoFill.
c. To the right of the last month label in cell N13, we want labels for two aggregate
calculations for the total sales for the year and the average monthly sales. Create labels in cells O13 and P13 for the annual
total and monthly average.
d. While a label for the total unit sales each month is already shown in cell B17, we have two
calculations that have been requested for analysis of the results. Management would like to see the change and percentage change in sales from month to month. Create labels in cells B18:B19 for these calculations. By the way, have you noticed how
we are starting the labeling in column B while titles are in column A? That is intentional as it will help us later move from section to section with ease … and besides, it looks good.
2) Section Formulas 1
a. Formulas for model calculations need to be consistent and easy to understand. Formulas that are not clear require comments to explain the purpose of the formula to the user.
b. We need to total the sales of each tablet configuration for the year in cells O14:O16. Let’s start with O14. Formulas in Excel start with an “=”; nothing else is needed. In this case, we are going to use an Excel function, SUM, to do the mathematics for us. Excel function names are always followed by a pair of parentheses to identify what is needed in the function. In this case, we want to SUM the cells C14:N14, so our entry in cell O14 is “=SUM(C14:N14)”.
Typing formulas is error-prone, so we will use the mouse to help us. In cell O14, type “=SUM(“. Now use your mouse to highlight the cells that you wish to be summed, namely cells C14:N14. Complete the formula
by typing “)” (to close the parentheses) and pressing Enter. You should see the total for the 12 months of sales.
c. While we could repeat the above process for cells O15 and O16, it is good form to use an existing formulas that is known to be working properly. Utilizing the procedure we used earlier, position the cursor on cell O14 and grab the fill handle in the bottom right of the cell. Click and drag the mouse down two cells to cover O15:O16 and release the mouse. As you can see, AutoFill also works for formulas and actually moves the cell references in the formula. This is the preferred approach to creating additional formulas in a model. If you can copy a formula, do so. It prevents errors.
d. Note that the SUM formulas in cells O15 and O16 reference different cells than O14. The cells referenced in each of these formulas is relative to the position of the formula. The Sum in cell O14 references cells in row 14, so the sum in cell O15 references cells in row 15. The cells do not have to be in the same row, but as you moved the formulas, the cell references moved with it. This is a great example of what we call relative referencing. We will look later at absolute referencing in formulas.
3) Section Formulas 2
a. Using the same process as you did in creating the summations in cell O14, create an average calculation in cell P14. The Excel function is AVERAGE, and the formulas should look something like “=AVERAGE(C14:N14)”. Remember, start the formula with “=AVERAGE(“ and then use the mouse to highlight the cells to be averaged. Do not include the total cell
in O14. Press Enter and check your work. Did you note that I forgot to tell you to close the parentheses? Sometimes you can skip that part
if it is clear to Excel that you are intending to finish the formula. Generally this is bad form, but you can use it with simple formulas.
b. Copy the formula from P14 down to the cells P15:P16 using the fill handle.
c. We are going to need monthly totals in row 17. Using your newfound knowledge, create
a formula in cell C17 that sums cells C14:C16. Your formula should look like “=SUM(C14:C16)”. Using the fill handle, copy the formula over to cells D17:P17. Note that this includes the total and average calculations to the far right and provides us with aggregate sales for the year as well as aggregate
monthly sales.
d. The results of these formulas are not always aesthetic. We really only need to show the integer part of these results, so we are going to apply a format to the cells we have already calculated. This is an important point … formatting a cell does not change the number in a cell, only the way the number is presented. If the value 94.5 is formatted to not show any numbers after the decimal, it will look like 95 … but Excel will use the true value of 94.5 in any formula in which that cell is referenced. Never forget this as it is a common mistake among individuals with novice spreadsheet skills.
e. A good way to format noncash numbers is by using the Comma Style format from the Home menu. Using your mouse, highlight all of the cells in C14:P17. Go to the Home menu and choose the Comma Style format button. All cells now have two decimal places showing, the default for the Comma Style format. With the cells still highlighted (or highlight them
again if necessary), choose the Decrease Decimal button which is near the Comma Style
button. Every time you press the Decrease Decimal button, the number of digits to the right of the decimal lessens. Press it once more to make all of the numbers show as integers … although we know that some of them are not really integers.
4) Section Formulas 3
a. To create a change in monthly sales formula, we need to determine the difference in sales for any two months. Since we do not have a number for December of the prior year, this formula will have to start with February. In cell D18, start your formula with an “=”. The change in sales is the current month less the previous month, so using your mouse, point on cell D17. Your formula now looks like “=D17”. Press “-“ (the minus key) and then choose C17 for January. Press Enter. Your formula now looks like “=D17-C17”. The result is
negative, but that is correct as sales went down from January to February.
b. Using the fill handle, copy the formula from
cell D18 to cells E18:N18. If necessary, format the results in the cells in row 18 with the Comma Style format with no numbers after the decimal (Excel may have already guessed the formatting you needed.)
c. To calculate the percentage change in sales from month, we will divide the change in sales for a month by the total sales for the prior month. In other words, to get the percentage change in sales for February, we will divide the change in sales for February by the total
sales for January. In cell D19, starting with “=”, choose cell D18. Press the division key (“/”) and then choose cell C17. Your formula should look like “=D18/C17”.
d. Before we copy it, let’s add a format. Instead of using the Comma Style format, choose the
Percent Style format in the button next to it. You will not have to alter the decimals in this case.
e. Copy the formatted cell with the formula (cell D19) over to cells E19:N19 using the fill handle. Did your notice that formats copy over with
formulas? You can also choose specifically what you wish to copy and paste using Excel’s Paste Special, but that is a subject for a later lab.
f. Hopefully you are getting the hang of this formula creation, formatting, copying, and pasting. It is not difficult with practice.
Part 2 – Constructing the Gross Margin Section
Purpose
The intent of the Gross Margin section is to calculate the amount of money that tablet sales are contributing to the operation of the store. Gross margin is defined as the revenue from sales less the cost of the actual good sold and thus does not include other significant expenses like the cost of running the store, personnel, etc.
Video Instructions
Section Labels Video Link
Section Formulas 1 Video Link
Section Formulas 2 Video Link
Section Formulas 3 Video Link
Section Formulas 4 Video Link
Final Details Video Link
Step by Step Instructions
1) Section Labels
a. While the month and aggregate calculation labels for the columns are present in row 13, they are in another section of the model and far enough away that it makes sense to repeat them here in this section. There are two reasonable ways to recreate these labels, copy them from above, or recreate them as we did earlier. To try something new, we will go with the former.
b. Highlight cells C13:P13 with your mouse. Now we have two options to make a copy. We
can use the Ctrl C key combination or we can right mouse click somewhere inside the highlighted range and choose Copy from the popup menu. Now place your cursor on cell C23. To paste, either use the Ctrl V key combination, or right mouse click on the cell and evaluate the Paste Options. The first choice under the Paste Options is the standard paste, which is what we need. Mission accomplished.
c. We are going to need the configurations from the unit sales
section and the easy way to get them is to copy additional cells and write over them. Using either of the procedures from the previous step, highlight and copy cells B13:B17 to cells
B23:B27. Type over the entries in cells B23 and B27 to show them as revenue and total
revenue.
d. Complete the labels for the calculations following total revenue. We need labels for cost of goods sold, gross margin, and year to date (YTD) margin in cells B28:B30.
2) Section Formulas 1
a. The formulas in this section are a bit more advanced, but are easily accomplished with some patience. We are going to handle the revenue calculations separately for the individual tablet configurations, but combine all of the configurations into a single cost of goods sold calculation. This is not necessarily the best approach, but it does give you some experience with more complex formulas.
b. Our first formula for revenue in cell C24 comes with complications. This is a simple formula, but it must be altered in order to make use of the fill handle for copying. Remember, copied formulas are better and less prone to error. The formulas itself is easy. We need to multiply cell C14 (32GB tablet unit sales in January) by cell C7 (unit sales price for the 32GB tablet). So, the formula in cell C24 is simply “=C14*C7”. However, recall what we discussed about relative referencing. If we copy this formula from cell C24 to cell D24, the formula will adjust to “=D14*D7”. That formula is incorrect as the sales price is still in cell C7. We need to lock down the reference to cell C7 in the original formula so that it stays there when copied. To lock down a cell reference, we can place a “$” in front of the column reference, the row reference, or both in the formula cell, depending on what we want to stay constant. Since we want the “C” in C7 to stay “C”, we will want to place a ”$” in front of it in the formula. So the formula would now look like “=C14*$C7”. But hold off on putting that in.
c. Let’s take this a step further. It would be ideal if we did not have to recreate revenue calculations for every different configuration. Let’s consider our formula so far and see what would happen if we copy it down from C24 to cell C25. “=C14*$C7” becomes “=C15*$C8” as a copy down one cell will adjust the row references down once cell. Is this correct? Check it.
d. “=C15*$C8” is indeed a good formula for cell C25, so it appears that by simply locking down the column reference in the original formula, we have created a formula that can be used in all revenue calculations. We still have three things to do here … 1) create the formula, 2) copy the formula, and 3) check the copy to see if it was successful.
e. To create the formula, put your cursor on cell C24. Starting your formula with “=”, use the mouse and the “*” sign to create the formula “=C14*C7” … BUT DON’T PRESS ENTER. Now, press the F4 key and watch what happens. Since the C7 is the last cell reference in the formula, dollar signs appear in front of the C and 7. That would be okay if we wanted to lock down both the row and column reference, but we want $C7, not $C$7. Press F4 again. Now the $ is in front of the 7, which is still not what we want. Press F4 again. There, the formula now looks like our requirement
“=C14*$C7”. Press Enter to keep this formula. By the way, if pressed F4 again, it would go back to the original C7 reference. F4 simply rotates through the different ways to absolute a cell reference. Now you now the term. Absolute referencing in cell formulas is simply a way of saying that we are locking down a cell reference in some way, either the row, column, or both row and column.
3) Section Formulas 2
a. Now that we have C24 complete, use the fill handle to copy the formula down to cells C25:C26. Now highlight all three cells (or leave it as is as they are likely already highlighted) and drag the fill handle over to column N. When you let go of the drag, each of these cells should populate. You have done your first multi-cell copy.
b. One really important part of creating spreadsheet models is double-checking them. Can you believe that nearly 90% of spreadsheet model in industry have errors? A good habit to have is double-checking your formulas, and this allows you to see another handy Excel tool, the edit mode.
c. Put you cursor on cell E25. Press the F2 key. Do you see the color shading of the formula and the same colors coding the cells that you referenced in the formula? Are the cells correctly referenced? If the copy was successful, the formula in cell E25 is “=E15*$C8” and the correct values for the 64GB tablet sales price and unit sales in March are shaded.
d. You are going to need formulas to sum up the year’s revenue in cells O24:O26. You are also going to need formulas to determine the average monthly revenue in cells P24:P26. Use the SUM and AVERAGE functions to create and copy those formulas in the appropriate cells using the technique
we discussed earlier. No absolute referencing needed here.
e. The total revenue calculations in row 27 should be easy by now. Create the formula in cell C27 using the SUM function and copy it over to cells D27:P27.
4) Section Formulas 3
a. Cost of goods sold is a much more complicated formula that will require multiple cell references and some absolute referencing. In this one formula, we are going to multiply the unit sales for each tablet configuration by its unit cost in such a way that the formula can be copied over to each month. Here we go …
b. Let’s start the formula in cell C28 by concentrating on the 32GB tablet. To calculate the cost of goods sold, we simply need to multiply cells C14 and D7. Our formula starts like “=C14*D7”, but don’t press Enter. To that we need to add the cost of goods sold for the 64GB tablet, so the formula expands to “=C14*D7+C15*D8”. Finally adding the 128GB tablet completes the formula at “=C14*D7+C15*D8+C16*D9”. Now you can press Enter. This formula will work fine in this cell, but it will not copy. Do you understand why?
c. Consider what would happen if we copied this formula over to the next cell, D28. We would get “=D14*E7+D15*E8+D16*E9”, but E7:E9 refer to empty cells. We need those reference back on column D. To lock down those cell
references, go back to cell C28 and press the F2 button. You can now see the formula and cell references in color. Move your mouse over the reference to D9 and press F4 three times. Without pressing Enter, do the same for D8 and D7. Now press Enter. Your formula should look like “=C14*$D7+C15*$D8+C16*$D9”. This formula will copy correctly. Copy it to cells D28:N28, but not under the total or average aggregate calculations in columns O and P.
d. The calculation of gross margin will be a cakewalk compared to the previous formula. It is simply the difference in the total revenue and cost of goods sold. In cell C29, enter the formula “=C27-C28”.
Copy it over to cells D29:N29.
5) Section Formulas 4
a. The year to date margin is a different animal as the formula is not the same in all months. In January, the calculation only includes January, hence the formula
in cell C30 is simply “=C29”. In February and subsequent months, the formula takes the
prior month’s year to date total and adds the current month. In cell D30, it is “=D29+C30”. Create the formula
and copy it to cells E30:N30. We are almost done.
b. Rows 28 and 29 could use total and average monthly figures, but the correct formulas already exist in rows 24:26 but not in row 27. Highlight cells O24:P24 and press Ctrl C (or right click and choose Copy). Highlight the whole group of cells in O28:P29 (two rows and two columns) and paste using Ctrl V (or right click on the highlighted group and choose the first Paste Option). Notice that Excel figures out what you want where and pastes the formulas in the right places. The calculations are complete.
c. Wait, did you notice the little green flags that popped up in cells O27:P27? If you click on one of those cells, you will get a little box with an exclamation mark to the left. Click on it and you will see whay Excel has flagged you. It belives you have made an error because your formulas are inconsistent. The formulas in rows 24, 25, 26, 28, and 29 are the same in columns O and P, but the
formula in row 27 is different. Excel thinks you have a problem, but do you? Check your logic.
d. Once you realize that the formulas are correct, you can click on each of the little boxes with exclamation marks and choose Ignore Error to clear the flag, but realize it will likely show up again next time you open the saved file.
e. Format all of the cells in the Gross Margin section using the Comma Style format with zero decimal places as we did earlier.
6) Final Details
a. There are still a couple minor details that makes a spreadsheet model more aesthetic … one is to remove the gridlines. Although gridlines are nice for lining up column and row references, they are cluttering to a user of the model. Removing gridlines is simple. Go to the View menu selection and take the check off the Gridlines
checkbox under the Show subsection. That is it.
b. The second minor detail is to name your worksheet tab. This is also simple. Go to the bottom
of the Excel window and look for the tab labeled “Sheet1”. You can either right click on the tab and choose Rename, or double ckicl on the tab and type
over the Name. Let’s use the tab name “Gross Margins on Tablets”.
c. You can also change the color of the tab, which can help differentiate different parts of the Excel workbook. A workbook can include multiple worksheets, each of which is a tab in the
workbook. If you haven’t noticed, you save your file as an Excel worbook by default. To change the color of a tab, right mouse click on the tab you renamed, and choose Tab Color. Apply the “Blue, Accent 1, Lighter 40%” shade to the tab. It is the fourth shade of blue in the fifth column.
d. Don’t forget to save your work and submit it. Your saved file is the only way to show that you have worked successfully through the lab. You must submit it in the appropriate class dropbox in order to receive credit. The existing filename (from the previous lab) is sufficient. Don’t forget where you put it on the computer so that you can find it when submitting it via D2L. Also, email yourself another copy for safe keeping … and for the next lab.
Lynda.com Videos
To prepare for the next lab, you will need to work through the videos linked on the course website. Remember that working through the videos is very different than watching the videos. If you simply watch the videos, you are not developing any skills.