Looking for:
- Microsoft Excel Data Analysis and Business Modeling [Book] |
Microsoft excel 2016 data analysis and business modeling free. Microsoft Excel 2016 : data analysis and business modeling
The workbooks and templates are organized in folders named for each chapter. The answers to all chapter-ending problems in the book are also included with the sample files. Each answer file is named so that you can identify it easily. Acknowledgments I am eternally grateful to Jennifer Skoog and Norm Tonina, who had faith in me and first hired me to teach Excel classes for Microsoft finance. Jennifer in particular was instrumental in helping design the content and style of the classes on which the book is based.
Ed Price was also the most thorough and best technical editor I have ever had. Many of them have taught me things I did not know about Excel. Finally, my lovely and talented wife, Vivian, and my wonderful children, Jennifer and Gregory, put up with my long weekend hours at the keyboard. If you need additional support, email Microsoft Press Book Support at [email protected] microsoft. Please note that product support for Microsoft software and hardware is not offered through the previous addresses.
Free ebooks from Microsoft Press From technical overviews to in-depth information on special topics, the free ebooks from Microsoft Press cover a wide range of topics.
Introduction xxv We want to hear from you At Microsoft Press, your satisfaction is our top priority, and your feedback our most valuable asset.
Your answers go directly to the editors at Microsoft Press. No personal information will be requested. Thanks in advance for your input! How can my local coffee shop determine how changes in price and unit cost affect profit? For many people, the barrier to entry in becoming proficient at Excel is in understanding how Excel formulas work. In this chapter, we will develop several simple spreadsheet models that should get you ready to master the complexities of Excel. In the file Wagestemp.
You want to determine the amount paid to each employee, as well as total hours worked and average hourly wage. Excel and later have 1,, rows! You could also copy the formula in E4 to the range E5:E11 by pointing at the tiny square in the lower-right corner of E4 and, after observing the cursor change to a thin black crosshair, press the left mouse button and drag the formula to the range E5:E In each cell where the formula is copied, Excel will multiply the two values to the left of the cell in column E.
You could also compute in cell C12 as I did in the file Wagesfinal. How can I efficiently determine how much a bakery owes each supplier? In the file Bakery1temp. Determine the amount paid to each supplier for sugar, flour, and butter. Also determine the total amount paid. As shown in Figure , in cell E23 we compute the cost paid to Supplier 1 for sugar by multiplying the cost per pound that Supplier 1 charged for sugar by the amount of sugar bought from Supplier 1.
Select cell E23 and, after the cursor changes to the crosshair, drag the formula to FG Then drag the range EG23 to EG A quicker way to create our totals is to select the range HH28 and after holding down the Ctrl key select the range EG Then simply click the AutoSum button in the Editing group on the Home tab of the ribbon, as shown in Figure Basic spreadsheet modeling 3 The AutoSum command guesses not always correctly, so be careful!
In this case, AutoSum might save you five seconds! In the file Bakery2temp. Again compute the total amount paid to each supplier for each product and the total amount the bakery pays each supplier. Unfortunately, in rows 24 through 28, the row references 12 and 14 will both change. As the formula is copied, we want the 14 to change but not the 12, because we always need to pull the unit cost for each product from row This is called absolute addressing or locking the row.
When a row is preceded with the dollar sign in a formula and the formula is copied, the row number remains unchanged. An easy way to insert dollar signs in a formula is to use the F4 key. If you select part of a formula and repeatedly hit F4, Excel cycles through adding a dollar sign to the row and column, then just the row, then just the column, and finally shows no dollar sign.
How can I predict the number of customers a new health club will have in 10 years? Our answer is in file Chapter1customer. Here we need to set up a model from scratch. Spreadsheet models have inputs or assumptions that we use to calculate desired outputs. We entered values for these inputs in C2:C4. Separating spreadsheet inputs from outputs makes it easy to determine how changing the inputs affects the spreadsheet outputs.
Basic spreadsheet modeling 5 Note that the numeral 3 must be preceded with the dollar sign; otherwise, when the formula in D8 is copied, the 3 would change, giving incorrect results. Preceding the letter C with a dollar sign is a matter of choice because we are not copying the formula across a column. The number of customers quitting each month is the starting customers multiplied by the churn rate.
The ending value for customers for each month is obtained by adding starting customers and new customers and then subtracting quitting customers. The astute reader might argue that we really do not know the churn rate and number of new customers each year. She is absolutely right. In Chapter 17, you will learn how to use data tables to perform such a sensitivity analysis.
Complex Excel formulas often involve a variety of complex mathematical operations, such as exponentiation, multiplication, and division. This formula causes Excel to compute the percentage expressed as a fraction increase in sales. The final answer. The key to understanding how alterations of price affect profit is the proper estimation of a demand curve.
A demand curve shows how changes in price change product demand. See Chapters 84 through 86 for a further discussion of demand curve estimation. In the file Coffee. Our references to F5 and F4 are absolute because we do not want the row or column to change as we copy our formulas. Our reference to price cell F9 requires using dollar signs or locking the row because we always need the price pulled from row 9. Our reference to unit cost E10 requires the column to use the dollar sign because we always need the unit cost pulled from column E.
Note for each unit cost, the price that maximizes profit is highlighted. You will learn in Chapter 24 how conditional formatting is used to create this nifty highlighting. Problems 1. In my class, I give five homework assignments, each worth 25 points, and three exams, each worth points. Set up a spreadsheet to calculate the final grade for a student that allows you to change the weight given to exams. Set up a spreadsheet to compute the Fibonacci sequence.
Show that for large N, the ratio of successive Fibonacci numbers approaches the Golden Ratio 1. The famous butterfly effect states that if a butterfly flaps its wings in Tahiti, that small event might cause a hurricane to hit Texas. How do your calculations illustrate the butterfly effect? A lake is currently stocked with 12, fish. Each year, the number of births per fish is 1. Show that if 6, fish per year are harvested, the number of fish in the lake will stay constant.
I have started defining range names for many of the workbooks I have developed at the office. However, the range names do not show up in my formulas. How can I paste a list of all range names and the cells they represent into my worksheet? For each day of the week, we are given the hourly wage and hours worked.
How can I create named ranges? To see the Name box, you need to display the Formula bar. To create a range name in the Name box, simply select the cell or range of cells that you want to name, click the Name box, and then type the range name you want to use. Clicking the arrow beside the Name box displays the range names defined in the current workbook. You can display all the range names in a workbook by pressing the F3 key to open the Paste Name dialog box. When you select a range name from the Name box, Excel selects the cells corresponding to that range name.
Range names are not case sensitive. For example, suppose you want to name cell F3 east and cell F4 west. See Figure and the file Eastwest. Simply select cell F3, type east in the Name box, and then press Enter. Then select cell F4, type west in the Name box, and press Enter. This means that whenever you see the reference east in a formula, Excel will insert whatever is in cell F3.
Suppose you want to assign the name data to a rectangular range of cells such as A1:B4. Simply select the cell range A1:B4, type data in the Name box, and press Enter. See the file Data.
Sometimes you want to name a range of cells made up of several noncontiguous rectangular ranges. For example, in Figure and the file Noncontigtemp. Now release the Ctrl key, type the name noncontig in the Name box, and press Enter.
Figure shows a subset of this data. We would like to name each cell in the range B6:B55 using the correct state abbreviation. Excel now knows to associate the names in the first column of the selected range with the cells in the second column of the selected range.
Note that creating these range names in the Name box would have been incredibly tedious! Click the arrow beside the Name box to verify that these range names have been created. The New Name dialog box shown in Figure opens. Suppose you want to assign the name range1 range names are not case sensitive to the cell range A2:B7. The New Name dialog box will now look like Figure If you open the Scope list, you can select the option Workbook or any worksheet in your workbook. You can also add comments for any of your range names.
In Excel there is an easy way to edit or delete your range names. You will now see a list of all range names. For example, for the file States. To edit any range name, simply double-click the range name or select the range name and click Edit.
Then, in the Edit Name dialog box, you can change the name of the range, the cells the range refers to, or the scope of the range. Range names 13 To delete any subset of range names, first select the range names you want to delete.
If the range names are listed consecutively, simply select the first range name in the group you want to delete, hold down the Shift key, and select the last range name in the group. If the range names are not listed consecutively, you can select any range name you want to delete and then hold down the Ctrl key while you select the other range names for deletion. Then press the Delete key to delete the selected range names.
The latter formula is, of course, much easier to understand. As another illustration of how to use range names, look at the file Historicalinvesttemp. Some rows are hidden in this figure; the data ends in row After selecting the cell range B1:D89 and clicking Create From Selection from the Formulas tab, I created names in the top row of the range.
I would be remiss if I did not mention the exciting AutoComplete capabilities of Excel You can simply double-click Tbills to complete the entry of the range name and then type the closing parenthesis. What does a formula like Average A:A do? If you use a column name in the form A:A, C:C, and so on in a formula, Excel treats an entire column as a named range.
Using a range name for an entire column is very helpful if you frequently enter new data into a column. For example, if column A contains monthly sales of a product, as new sales data is entered each month, our formula computes an up-to-date monthly sales average. What is the difference between a name with workbook scope and one with worksheet scope? The file Sheetnames. When you create names with the Name box, the names have workbook scope.
For example, suppose you use the Name box to assign the name sales to the cell range E4:E6 in Sheet3, and these cells contain the numbers 1, 2, and 4, respectively. Anywhere in the workbook where you refer to the name sales which has workbook scope , the name refers to cells E4:E6 of Sheet3.
Next you open the Name Manager, give the name jam to cells E4:E6 of Sheet1, and define the scope of this name as Sheet1. Then you move to Sheet2, open the Name Manager, and give the name jam to cells E4:E6, and define the scope of this name as Sheet2.
The Name Manager dialog box now looks like Figure Because those cells contain 4, 5, and 6, you obtain Thus, prefacing a worksheet-level name by its sheet name, followed by an exclamation point! I really am getting to like range names. How can I make recently created range names show up in previously created formulas? See Figure We would like these range names to show up in the cell F4 and cell F7 formulas. To apply the range names, first select the range where you want the range names applied in this case, F4:F7.
Highlight the names you want to apply, and then click OK. A list of range names and the cells each corresponds to will be pasted into your worksheet, beginning at the current cell location. The file Last year. To begin, we use the Name box to name cell B3 growth.
Now comes the neat part! Then fill in the New Name dialog box as shown in Figure , which shows how, with the cursor in cell B7, we can create a range name lastyear that always refers to the cell one row above the current cell.
Because we are in cell B7, Excel interprets this range name to always refer to the cell above the current cell. Of course, this would not work if the cell reference B6 contains any dollar signs.
For each day of the week we are given the hourly wage and hours worked. As shown in Figure see the file Namedrows. You can simply select row 12 by clicking on the 12 and use the Name box to enter the name wage.
Then select row 13 and use the Name box to enter the name hours. Range names cannot begin with numbers or look like a cell reference. For example, 3Q and A4 are not allowed as range names. Because Excel has more than 16, columns, a range name such as cat1 is not permitted because there is a cell CAT1.
If you try to name a cell CAT1, Excel tells you the name is invalid. The file Stock. Name the ranges containing the monthly returns for each stock and compute the average monthly return on each stock. Given the latitude and longitude of any two cities, the file Citydistances. Define range names for the latitude and longitude of each city and ensure that these names show up in the formula for total distance.
The file Sharedata. Create a range name that averages the last five years of sales data. Assume annual sales are listed in a single column. I know the date the product was sold. In a vertical lookup, the lookup operation starts in the first column of a worksheet range. In a horizontal lookup, the operation starts in the first row of a worksheet range.
The brackets [ ] indicate optional arguments. Table range is the range that contains the entire lookup table. The table range includes the first column, in which you try to match the lookup value, and any other columns in which you want to look up formula results. Column index is the column number in the table range from which the value of the lookup function is obtained. Range lookup is an optional argument.
The point of range lookup is to allow you to specify an exact or approximate match. If the range lookup argument is True or omitted, the first column of the table range must be in ascending numerical order.
If the range lookup argument is True or omitted and an exact match for the lookup value is found in the first column of the table 21 range, Excel bases the lookup on the row of the table in which the exact match is found. If the range lookup argument is True or omitted and an exact match does not exist, Excel bases the lookup on the largest value in the first column that is less than the lookup value. If the range lookup argument is False and an exact match for the lookup value is found in the first column of the table range, Excel bases the lookup on the row of the table in which the exact match is found.
Note that a range lookup argument of 1 is equivalent to True, whereas a range lookup argument of 0 is equivalent to False. The following example shows how a VLOOKUP function works when the first column of the table range consists of numbers in ascending order. Suppose that the tax rate depends on income, as shown in the following table. The numbers in the first column of the table range are sorted in ascending order.
I began by entering the relevant information tax rates and break points in cell range D6:E9. I named the table range D6:E9 lookup. If you do so, you need not remember the exact location of the table range, and when you copy any formula involving a lookup function, the lookup range will always be correct.
To illustrate how the lookup function works, I entered some incomes in the range DD Note that because the column index in the formula is 2, the answer always comes from the second column of the table range. Often, the first column of a table range does not consist of numbers in ascending order. For example, the first column of the table range might list product ID codes or employee names. In these situations, you need to remember only one simple rule: use False as the value of the range lookup argument.
In the file Lookup. How do you write a formula that takes a product ID code and returns the product price? However, note that when you omit the fourth argument the range lookup argument , the value is assumed to be True. By the way, you can see in Figure that columns A—G are hidden. To hide columns, you begin by selecting the columns you want to hide. Click the Home tab on the ribbon. Suppose the price of a product depends on the date the product was sold. How can you use a lookup function in a formula that will pick up the correct product price?
More specifically, suppose the price of a product is as shown in the following table. See the file Datelookup. This formula tries to match the dates in column B with the first row of the range B2:D3. The file Hr. The file Assign. The suitability of each worker for each group on a scale from 0 to 10 is also given. Write a formula that gives the suitability of each worker for the group to which the worker is assigned.
Lookup functions 25 3. You are thinking of advertising Microsoft products on a sports telecast. As you buy more ads, the price of each ad decreases as shown in the following table. Write a formula that yields the total cost of purchasing any number of ads. You are thinking of advertising Microsoft products on a popular TV music program. You pay one price for the first group of ads, but as you buy more ads, the price per ad decreases as shown in the following table.
Hint: You probably need at least three columns in your table range, and your formula might involve two lookup functions.
The annual rate your bank charges you to borrow money for 1, 5, 10, or 30 years is shown in the following table. Because 15 years is one quarter of the way between 10 years and 30 years, the annual loan rate would be calculated as follows:. The distance between any two US cities excluding cities in Alaska and Hawaii can be approximated by the following formula: The file Citydata.
Create a table that gives the distance between any two of the listed cities. In the file Pinevalley. The file Lookupmultiplecolumns. The file Grades. The file Employees. The file also gives the job to which each worker is assigned. Suppose one dollar can be converted to 1, yen, 5 pesos, or 0.
Set up a spreadsheet in which the user can enter an amount in US dollars and a currency, and the spreadsheet converts dollars to the entered currency.
The file Qb The file NBAplayers. Lookup functions 27 Column F of the file Hardware. Columns M—O list the quantity and price a hardware store paid for various products. How do I write a function that returns the distance between, for example, Seattle and Miami? Is there a way I can write a formula that references the entire column containing the distances between each city and Seattle?
This entry is the one in cell C2. The range CJ17, which contains the distances, is named distances. Suppose that you want to enter in a cell the distance between Boston and Denver. Because distances from Boston are listed in the first row of the array named distances, and distances to Denver are listed in the fourth column of the array, the appropriate formula is INDEX distances,1,4. Seattle and Miami are 3, miles apart.
At the conclusion of the road trip, the Seahawks return to Seattle. Can you easily compute how many miles they travel on the trip? The formula in D21 computes the distance between Seattle and Phoenix city number 7 , the formula in D22 computes the distance between Phoenix and Los Angeles, and so on. The Seahawks will travel a total of 7, miles on their road trip. To illustrate, suppose you want to total the distances from each listed city to Seattle.
In either case, you find that the total distance from Seattle to the other cities is 15, miles, as you can see in Figure How many miles will they travel on this road trip? The file Product. The file NBAdistances. Suppose you begin in Atlanta, visit the arenas in the order listed, and then return to Atlanta.
How far would you travel? For example, how much of Product 2 did I sell during June? How about the player with the fifth-highest salary?
You need to find the name John Doe, which you know appears somewhere and only once in the list. The MATCH function enables you to find within a given array the first occurrence of a match to a given text string or number. You should use the MATCH function instead of a lookup function in situations in which you want the position of a number in a range rather than the value in a particular cell. The lookup range must be a row or a column.
The MATCH function then returns the row location in the lookup range relative to the top of the lookup range that contains the largest value in the range that is less than or equal to the lookup value. The MATCH function returns the row location in the lookup range relative to the top of the lookup range that contains the last value in the range that is greater than or equal to the lookup value. This is the situation you usually face.
The file Matchex. The asterisk is treated as a wildcard, which means that Excel searches for the first text string in the range B4:B11 that begins with Pho. Incidentally, this same technique can be used with a lookup function. The file Productlookup. I named the range B4:G7, which contains sales data for the dolls, as Sales. I entered the product I want to know about in cell A10 and the month in cell B The file Baseball.
The data is not sorted by salary, and we want to write a formula that returns the name of the player with the highest salary, as well as the name of the player with the fifth-highest salary. Player number has the highest salary.
Not surprisingly, we find that Alex Rodriguez was the highest paid player in To find the name of the player with the fifth-highest salary, you need a function that yields the fifth-largest number in an array.
Proceeding as before, we find that Derek Jeter is the player with the fifth-highest salary. The file Payback. We expect cash flows to grow at 10 percent per year. How many years will pass before the project pays back its investment? The number of years required for a project to pay back an investment is called the payback period. In high-tech industries, the payback period is often used to rank investments. This calculation gives you the payback period. I gave the cells in B1:B3 the range names listed in A1:A3.
To compute the Year 0 cumulative cash flow, I used the formula B5 in cell C5. This calculation always gives you the payback period.
For example, if the last row in C5:C20 that contains a value less than 0 is the sixth row in the range, that means the seventh value marks the cumulative cash flow for the first year the project is paid back. Because our first year is Year 0, the payback occurs during Year 6.
If any cash flows after Year 0 are The MATCH function 37 negative, this method fails and yields an error message because the range of cumulative cash flows would not be listed in ascending order. Problems 38 1. Using the distances between US cities given in the file Index. The file Matchtype1. The file Matchthemax. The file Buslist. Write a formula that, for any arrival time after the first bus, gives the amount of time you have to wait for a bus. For example, if you arrive The file Salesdata.
Create a formula that returns the units sold by a given salesperson. In the file Baseballproblem7. Write a formula in cell K2 that returns the value of the statistic for the selected team.
The file Footballproblem8. How can I put all the product descriptions in column A, all the product IDs in column B, and all the prices in column C? Every day I receive data about total US sales, which is computed in a cell as the sum of East, North, and South regional sales.
How can I extract East, North, and South sales to separate cells? At the end of each school semester, my students evaluate my teaching performance on a scale from 1 to 7. I know how many students gave me each possible rating score. How can I easily create a bar graph of my teaching evaluation scores? I have downloaded numerical data from the Internet or a database. How can I solve this problem? What are Unicode characters?
For example, in sales data you download, dates and sales amounts might be in the same cell, but you need them to be in separate cells. How can you manipulate data so that it appears in the format you need? The answer is to become good at using the Microsoft Excel text functions. Finally, you will learn about the vast set of Unicode characters available in Excel Text function syntax The file Textfunctions.
Excel returns Regg. The TRIM function also removes spaces at the beginning and end of the cell. Because the trimmed result in cell C5 has two spaces removed, cell C5 contains two fewer characters than the original text in A3. FIND is case sensitive. This formula yields Rennie Miller. You can identify the value 31 in cell A15 as text because it is left justified.
Similarly, you can identify the value 31 in cell B15 as a number because it is right justified. A partial listing is shown in Figure The CLEAN function As you can see in Figure , certain characters, such as character number 10 which represents a line feed , are invisible. Later in this chapter, we will examine how to remove troublesome characters such as CHAR from a cell. The last argument is optional. Often, the key to solving a problem is to combine multiple text functions into a single formula.
Text functions 43 I have a worksheet in which each cell contains a product description, a product ID, and a product price. In this example, the product ID is always defined by the first 12 characters, and the price is always indicated in the last 8 characters with two spaces following the end of each price.
The only excess spaces in column A turn out to be the two spaces inserted after each price. To see this, put the cursor in cell A4 and press F2 to edit the cell. If you move to the end of the cell, you will see two blank spaces. To capture the product ID, you need to extract the 12 leftmost characters from column B. This formula extracts the 12 leftmost characters from the text in cell B4 and the following cells, yielding the product ID, as you can see in Figure To extract the product price, we know that the price occupies the last six digits of each cell, so we need to extract the rightmost six characters from each cell.
By examining the data, you can see that if we begin our extraction with the thirteenth character and continue until we are six characters from the end of the cell, we can get the data we want.
LEN B4 returns the total number of characters in the trimmed text. Now suppose you are given the data with the product ID in column C, the price in column D, and the product description in column E.
Can you put these values together to recover our original text? The concatenation formula starts with the product ID in cell C4. Next, you add the product description from cell E4. You can easily create a free account. Your Web browser is not enabled for JavaScript. Some features of WorldCat will not be available. New WorldCat. Create lists, bibliographies and reviews: or. Search WorldCat Find items in libraries near you.
Advanced Search Find a Library. Your list has reached the maximum number of items. Please create a new list with a new name; move some items to a new or existing list; or delete some items. Your request to send this item has been completed. APA 6th ed. Note: Citations are based on reference standards. However, formatting rules can vary widely between applications and fields of interest or study.
The specific requirements or preferences of your reviewing publisher, classroom teacher, institution or organization should be applied. The E-mail Address es field is required. Please enter recipient e-mail address es. The E-mail Address es you entered is are not in a valid format.
Please re-enter recipient e-mail address es. You may send this item to up to five recipients. We've made every effort to ensure the accuracy of this book and its companion content. Any errors that have been confirmed since this book was published can be downloaded below.
Download the errata. If you find an error, you can report it to us through our Submit errata page. Sign in. Your cart. This eBook includes the following formats, accessible from your Account page after purchase: EPUB The open industry format known for its reflowable content and usability on supported mobile devices. Downloads Follow the instructions to download this book's companion content. Click the Download button below to start the download. If prompted, click Save. Locate the.
Right-click the file, click Extract All, and then follow the instructions.
Comments
Post a Comment