Technology Next Generation Excel Pdf


Sunday, May 12, 2019

Next Generation. Excel. Modeling in Microsoft Excel for Analysts and MBAs (for Windows and Mac OS). Second Edition. ISAAC GOTTLIEB. John Wiley & Sons. Rutgers professor, Dr. Isaac Gottlieb demonstrates an array of advanced financial and accounting functions in this practical Excel modeling. In this new Second Edition of Next Generation Excel, Isaac Gottlieb shows financial analysts how to harness the full power of Excel to move.

Next Generation Excel Pdf

Language:English, Spanish, Dutch
Genre:Academic & Education
Published (Last):31.05.2016
ePub File Size:27.39 MB
PDF File Size:11.53 MB
Distribution:Free* [*Regsitration Required]
Uploaded by: LEONOR

Next Generation Excel Modeling In Excel For Analysts And Mbas For Ms to the next level in accounting and financial modeling in this new second edition. Next Generation Excel: Modeling in Excel for Analysts and MBAs. Home · Next Generation Excel: Modeling in Views 42MB Size Report. DOWNLOAD PDF. Take Excel to the next level in accounting and financial modeling. In this new Second Edition of Next Generation Excel, Isaac Gottlieb shows financial analysts .

We filled in the information in the sheet. The example illustrates the use of the AutoFill feature in Excel. Select the first two values in column B B2 and B3 , click on the lower right drag handle of cell B3, and pull the drag handle down toward cell B Dragging down the information created the desired AutoFill effect of continuing with the same series of numbers: Try to drag down the information shown in columns C and D.

You will create the information shown in Figure 1. After you experiment with a couple of columns, try a more efficient way: Release the mouse for a moment. Go to the drag handle. This time—do not drag—just double-click. Excel will drag it for you.

It will complete filling for you to the end of the adjacent column on the left. You may try double-clicking with more than one column selected at a time. Double-click works only in one direction: You may want to try it yourself. As shown in Figure 1. In Figure 1. I will also explain the concept of Time in Excel in Chapter 3.

Creating Custom Lists in Excel enables you to use these lists as demonstrated with the AutoFill function. Custom Lists let you use them when you sort in Excel. In addition to sorting in numerical or alphanumeric order, you can also sort with these Custom Lists or with the ones you create.

You can use the list created here later to sort a database. I used a list of the 10 largest cities in the USA where your company may be doing business as an example. Refer to Figure 1. The result is shown in Figure 1. Now all you have to do to make the list of the ten largest cities in the US, sorted by population size, part of your Excel Custom List, is click on the empty cell on the menu to the left of the Import button and select the cells on the sheet.

Click on the Import button and the list is now part of your Custom Lists. When you click on the icon, it will allow you to choose one of the options. The AutoFill option recognizes days and dates.

When you click on the menu with a list of dates or days of the week, Excel provides you with the additional options of choosing days, weekdays only—without weekends—or even spacing the list out, incrementing the dates by months or years. Right-Drag AutoFill When you right-click and drag a numeric series, the menu offers you additional features as shown in Figure 1.

The additional feature that could help us more than the others on the list is the Growth Trend feature. The following are two examples that can illustrate the power of this element in Excel. If you need to create an exponential list of 22, all you to do is type the first two terms in the series 2 and 4 and the Growth Trend feature in the list will create the series as show in Figure 1.

It will result in 2, 4, 8, 16, and so on. Using this idea, we can create a Figure 1. If you want a growth factor of 10 percent a year, you can type 1. There are two examples shown in this chapter that are slightly different in Excel than in Excel Both examples deal with the procedure to access the Custom Lists menu in Excel In the , version we used the Office icon to get to the Excel Options menu and the Custom Lists menu.

All the other features of importing custom lists are the same as in Excel —as described above. The chapter problems sheet of Chapter 1 has the following data: Use the double-click AutoFill feature to fill up the rest of the table resulting in a complete table. Use your Excel Options menu to create a custom list of the 10 largest suspension bridges in the world: Select the two first figures 5 and 6. Click on the grab handle and drag down until you see the value Select the rest of the table as you see in the figure.

Double-click on the grab handle. Click on the Office icon. Select Excel Options at the bottom of the menu. Click on Edit Custom Lists in the middle of the menu. On the resulting menu, select the range K K19 and click on Import. If you select a small range, it may be more effective to hold the Shift key down and use the arrow keys to select the range.

In many instances, you have to select large ranges of data. This chapter will describe a number of techniques to select this data in a more efficient way. See Figure 2. Selecting a Section To select any continuous data or section, click on the first cell of the section, then press Shift and click on the last cell of the section.

You can also reverse the process, click on the last cell, then press Shift and click on the first cell of this section to select, keeping the Shift key pressed down. With larger spreadsheets you may want to go back to the beginning of the sheet or to the end of the data on the sheet. To go to the first cell of a row, press the Home key. Extend and Add mode: After you select a starting cell, you can press F8 to enable the Extend mode indicated on the right of the status bar by the letters EXT in Excel or Extend Selection in Excel Now you can use the arrows and select from that point on by repeatedly pressing on the arrow keys in the direction you want to select.

Figure 2. You can then use the mouse and select additional regions. The Add mode in Excel or previous versions of Excel is indicated on the right side of the bottom of the screen. On Sheet 3 of the workbook Chapter 2, select the entire database using a shortcut: On the same sheet select C2: Use a shortcut to select the two columns: Start in cell G Use a shortcut to select the region C Select C2: I will show simple and advanced ways to create formulas and functions more efficiently.

The focus is on speed, accuracy, and the ability to duplicate and repeat the functions and the formulas—saving valuable time and effort. In Figure 3.

The sheet is named Simple Payroll Before. To calculate the salary, Hours worked x Rate in cell E3: When you press the Enter key, the formula will calculate the result. Note how the cursor will skip down to cell E4. Once you are in cell E4 and you want to duplicate the result for all other employees, you have to select E3 again. This is an extra step. In order to save this additional step you can do one of two things: The cursor will remain in the selected cell. See Figure 3. We do not copy and paste, and though you can drag down, it is not the most efficient way.

Imagine if you had employees! Figure 3. Relative and Absolute Addressing You will notice that copying or dragging a formula does not copy the values down. You copied the formula only. This time it is different. If you use the same procedure, you will run into a problem. G1 is the only cell that contains the tax rate information. The cells G2, G3, and the following cells are empty. See the results and the problem in Figures 3.

Revealing the formulas in Figure 3. The other cells are all empty and they cause the problem encountered in Figure 3. To overcome this relative addressing problem, we have to be able to refer to cell G1 in all the formulas in the cells in column F. This concept, using a specific cell and not a range, is called Absolute Addressing; we want all formulas in the series to refer to one specific cell only.

As shown in Figures 3. Before doing anything else, press the F4 key on the keyboard. See Figures 3. Other Functions In addition to using formulas, Excel has a variety of different useful functions. I will demonstrate here the use of a few of them.

A commonly used function in Excel is the AutoSum function. In addition to all the conventional ways to insert functions, AutoSum has a special icon, the Greek capital Sigma S. If you like, you could sum the Hours, Salary, and Taxes columns on the same simple payroll function sheet. Start in cell D11 summing the Hours column D3: When you select the D11 cell—all you have to do is click on the Insert Sum icon S and see that Excel will attempt to calculate the sum of that column as shown in Figure 3.

A much more efficient way is to just double-click the AutoSum icon. Try it. The result can be seen in Figure 3. The Function menu will appear on the screen.

Click on the drop-down menu and choose Statistical. On the menu, select Average and click OK. In the first cell of the Average menu cell, select D3: D10 and click OK. It will result in the average. Once you have the two functions as shown in Figure 3. D12 and place your mouse pointer at the drag handle as shown in Figure 3. The results and formulas are shown in Figure 3.

This chapter introduced formulas, functions, and the addressing issues in Excel. We showed how to use these formulas in a simple way. We also demonstrated how to take advantage of the shortcuts and the dragging to duplicate the results for entire columns and rows. The focus is on speed, accuracy, efficiency, and the ability to duplicate the functions and formulas. We will use these techniques again throughout the rest of the book. Calculate the Total Sales in cell D2.

Double-click on the drag handle in D2 to calculate the rest of the values in column D. Calculate the Sales Tax in column E, using I1 as an absolute address. Calculate the lowest, highest, and average for each of the columns. It is much easier to use Average Sales than Average B Another example is when using names in a meaningful way in a formula: I prefer to use names in Excel when I calculate statistics on a large range of numbers. Excel allows you to define a name or names in a variety of ways.

Once you adopt the practice of using names in your workbooks, you can easily update, audit, and manage these names. I will show you that naming cells or a range of cells can save you precious time and effort when you use them for calculations, statistics, and decision-making applications.

I will also demonstrate that names are helpful in presenting your calculations to an audience or when you copy your formulas to other applications such as Word and PowerPoint. All the cells in Excel are automatically given a specific name that places them in a specific column and row position—the address. In many situations, you may want to rename a cell or a range of cells to make it easier to refer to the data, which those cells or ranges contain.

The following demonstrates the concept with a simple example. Naming a Single Cell In the example in the workbook Chapter 4, in the sheet called Names Concept I would like to use a tax rate of 15 percent. See Figure 4. Type the word Tax and click Enter.

From now on, you may use this new name anywhere in this workbook. For example, if you type a formula in any cell: Using the Name box is only one way to name or rename cells or ranges. There are a number of other ways. To illustrate again how to use the name in the above example, try calculating the tax for all the entries in column F.

One additional advantage of using the name concept is eliminating the use of the absolute addressing concept. The above example illustrates the simplest way of creating a name for a single cell. I will show a number of other examples and techniques to name a range of cells and to use more efficient ways to create names.

Naming a Range of Cells If you have, for example, a set of 20, numbers in a range and you want to obtain different calculations for the range, you may not want to select the range or type the range address in the function more than once. I would like to calculate for the range the average, sum, standard deviation, and the median. I clicked on the Name box, typed the word Data, and clicked Enter. I changed the name of the range and the range B1: E is now called Data.

Figure 4.

Phishers unleash simple but effective social engineering techniques using PDF attachments

E Afterward, it is possible to use the name for the range to obtain the calculations mentioned above. I did not have to select B1: E or type B1: I just typed in the word Data. I used the function fx icon to calculate all the other functions.

See the result in Figure 4. In this example, I will call the range C3: C8 Sales, D3: D8 Cost, and the range E3: E8 Profit. The other ranges will also be named. E3 will be named Jan, C4: E4 will be named Feb, C5: C6 will be named Mar, and so on.

As you select the range B2: E8, go to the Formulas ribbon and click on Create from Selection. When you click on the Create Names from Selection the menu shown in Figure 4. Now that you have created these names, you can utilize them in formulas and functions on the same sheet or other sheets in this workbook. To locate the different named ranges you have created, go to the drop-down menu next to the Name box and select any name; the range that it represents will be highlighted on the data range itself.

Now you can take advantage of these names and ranges. For example, you can calculate the sum of Sales. Try something else: This will result in the in the intersection of the Cost and Feb: Just click on the Name Manager and experiment with this menu. See figure 4. With the Name Manager, you can either click on the Edit button or edit the name or delete any of the names you want to delete.

To paste the list of names, simply press the F3 key and choose Paste List. It will list all the names on your spreadsheet. This is a very powerful feature of Excel that many people fail to take advantage of.

In the following example, I will use the Excel menu to create names. In the sheet Ranges of the Excel Chapter 4 workbook, I would like to create names for the different ranges. In this example, I would like to call the range C3: E5 will be named Mar, and so on.

The header in row 2 and the information in column B will be used to create the range names.

E8 use: Insert Name Create. When you click on Insert Name Create, the menu shown in Figure 4. To change, edit, delete, or modify the names in Excel or a previous version, Insert Name Define. The Define Name menu will allow you to add, edit, or delete any of the names as shown in Figure 4. Insert Name Create from the menu Figure 4. On the sheet Review of Chapter 4, name the region H4: J11 Statistics.

Using the Names menu name C4: C7 Quantity, D4: D7 Price, E4: E7 Revenue, C4: Use the names you just created in review problems 1 and 2 to: Calculate the sum of the region H4: Calculate the sum of all the Quantities C4: Add the Revenues for the second and third quarters using the intersection feature of the name. Select the region H4: See figure.

Select B3: E7 and use the Create from Selection menu shown in the figure to create names. Once you created the names, you can use the names in the formulas. Charts make it easier to see what is hiding behind the numbers.

In this chapter, you are going to find out how to generate and format Excel charts. I will demonstrate how to use some of the different types of charts and the common techniques to deal with all Excel charts. E8 and press the F11 key. When you use the F11 key, it generates a clustered column chart the default chart on a new sheet. You will find it on the sheet Simple Chart in the Excel file Chapter 5.

If you wish to change the default chart type after Excel created it, right-click on an empty space inside the chart, and then select Change Chart Type in the local menu.

A window with chart options will pop-up and allow you to select a different chart type. See Figure 5. An alternate way to select a different chart type: When you create a chart, Chart Tools appear on the ribbon. Creating a Chart Using the Menu Using the Insert ribbon to insert charts will allow the creation of a variety of charts in a controlled approach.

This can be explained with a simple example. The easiest way to create a chart is by arranging the reference data in the way that Excel expects it; namely, position the data or names you want on the X-axis the horizontal axis in the first column.

This is done by entering the data you want to appear on the X-axis on the left-most column of the reference data. You may arrange it in different ways—but doing so, you may have to go through additional steps to create the desired chart.

The example I chose, which you can see in Figure 5. Using the same data as for the first example, I can create a column chart, the very first chart on the menu. I chose to create a chart using only the first and second columns of the data. Select the first two columns in the data range B2: C8 and click on Columns in the Insert ribbon, as shown in Figure 5. Excel will generate the chart shown in Figure 5.

You can also access the Chart Tools ribbon at any time when you select a chart. Try some of the features on the ribbon, for example the design part. The detailed procedure is illustrated in Figure 5. The redesigned chart is shown in Figure 5.

Figure 5. The ribbon can assist you in changing the design, layout, and chart type; selecting different data; or moving the chart to a different location. It is also important to understand the chart elements. When you need to create or modify charts, identifying these elements enables you to create, amend, or design a specific part of the chart. After a chart is created, any of its elements can be individually modified. You may want to change the way that axes are displayed, amend the chart title, move the legend, or display other chart elements: For the axes: You can specify the scale and adjust the interval between the values or categories.

You can also add tick marks to make it easier to look at the chart and its scale. For the titles: You may add titles and data labels to a chart. This displays the information in your chart. You can add titles to the chart, axis, and the data labels. For the legend or data table: You may show or hide a legend, change its location, or even change the legend entries.

You can display a data table the data used to build the chart. The legend keys can be formatted to have the appearance of your chart elements. I will discuss these and other chart options when charts will be used in upcoming chapters.

I will discuss them in the context of trend lines, formatting, and other situations. For reference, it is illustrated once more in Figure 5. The chart was created from the two first columns of the data. The Months in the first column are used to show the time progression on the X-axis.

The Sales information is in the second data column. It was used for the Y values of the chart. Once you have created a desired chart, you may want to add part or all of the spreadsheet data to the existing chart.

In this example, you will want to add the Cost and Profit columns. Select the data D3: Alternatively, it is possible after data selection to use the mouse right-click, copy, select a chart area, then paste using the local menu after another right-click. The data will be added to the chart. Selecting a Series All Excel charts have some common characteristics. All data series in our example, the sets of columns in the charts can be selected the same way. If you click on a single column once, all of the columns of that particular series will be marked.

For example, all of the sales columns will be marked if you click on a single column of the sales series. If you click on the same column a second time, it marks the individual column only. Use the rule: First click marks all of the points columns in a series—the second click marks the individual point or a single column.

Marking a series this way first click for all the points and second click for the individual point works in all charts in Excel. The advantage of being able to mark individual columns rather than the entire series is the possibility of formatting the individual column versus the entire series. If you wish to format the individual column say Sales for Jun , you may do so after selecting that column by itself the second click.

If you right-click after the individual selection, it is possible to format that column by itself. This is simple.

When an individual column is marked second click , you can format the individual column by itself. On the local menu, use Format Data Point. To format the entire series, click only once to select the series and repeat the procedure explained in Figure 5. The complete series will be formatted as shown in Figure 5. I chose to demonstrate only a few charts that are frequently used in the business world.

I will discuss the difference between a line chart and a scatter chart. I will also demonstrate the use of three-dimensional charts. Scatter Charts and Line Charts Scatter charts and line charts are very similar especially when you create a scatter chart with lines connecting the points—these charts look and feel like a line chart. However, there is a fundamental difference between them.

Scatter charts are used to display and compare numeric values, such as statistical data. These charts are helpful to show the relationships among the numeric values of several data series.

You can plot two groups of numbers as one series of XY coordinates. Line charts are used to display continuous data over time against a common scale. They are good for showing trends in data at equal intervals. Choose between the two options using this rule: The following example will clarify the fundamental difference.

On the sheet named Line vs. Scatter of the Excel Chapter 5 file, I selected sales data from to I created a line chart and a scatter chart for the same data. The line chart in Figure 5. Look at the X-axis values: They are not spaced proportionately. You can see the real picture—underscoring the difference between the line chart and the scatter chart.

See a Problem?

The latter displays the data as it should, since the X-axis data is not continuous in our example. Three-Dimensional Charts—Column and Pie Returning to the data used to create the first chart on the sheet Simple Chart, I will create a three-dimensional column chart, as well as a pie chart. The first example, a 3-D column chart, is shown in Figure 5. The figure shows the steps to create and move the chart to a new sheet. Right-click on an empty area in the chart and choose the 3-D Rotation.

The menu enables you to rotate and change the way the chart looks. There are many different formatting possibilities and options. Here is one example where I want to compare apples and bananas. I will use the same type of chart just created, however, you can apply this formatting technique to any chart type.

We can copy and paste the pictures in a Figure 5. See the spreadsheet with the data and pictures in Figure 5. I will create a 3-D clustered column chart, and will use the pictures selected to obtain a stronger visual effect for this chart.

The chart in Figure 5. We will use the pictures in Figure 5. To enter the apple picture as the Apples data point, I had to select the individual column in the chart. I clicked the first time to select the series and then clicked again on the Apples column to select the individual column. I reverted to the sheet with the pictures, and selected and copied the picture of the apple. After the apple image was copied, I went back to the sheet with my chart, clicked on the Apples column and clicked on the paste icon.

The result is in Figure 5. I wanted the picture stacked to scale. To do so, I right-clicked on the column and chose the Format Data Point to create the desired effect. Picture or texture fill, Stack and Scale. What is left now is to copy and paste the picture of the banana to the second column format it to be stacked to scale , copy the fruit store picture and paste it on the wall using the Paste icon, and copy the grass picture and paste it on the floor using the Paste icon.

See the result in Figure 5. I right-clicked on the chart and moved it to a new sheet. The resulting chart is in Figure 5.

The pie chart—as all the other Excel charts created from a series—can be selected using the same rule we used before: In the example of Figure 5.

I clicked on it a second time—so as to select the individual data point. I was then able to drag it out to put more emphasis on the June sales for my presentation. Right-click and use the 3-D rotations. Also, for ease of reference, each subject-specific chapter is self-contained, affording you the option of focusing on only those features of immediate interest to you. Demystifying the obscure and simplifying the complex, Dr. Gottlieb walks you, step by step, through all of Excel 's advanced business and finance functions with explanations for users of Excel , , and previous versions, as needed , including:.

Not just another general how-to Excel guide, this fully updated and expanded Second Edition of Isaac Gottlieb's internationally acclaimed Next Generation Excel shows you how to harness the full power of Excel to help you to perform an array of advanced business decision making, financial, and accounting functions. You'll learn how to quickly create models that deliver accurate, relevant information to boost efficiency, improve your forecasting ability, and perform an array of other crucial business and reporting functions.

In his newest book, Isaac showcases how the reader can engage with Excel to the 'next level of usability. Isaac takes day-to-day Excel challenges and provides the reader with elegant ease of use solutions.

With this newest edition, Isaac propels practical information even further. Next Generation Excel will revolutionize how you do your work and make you a better manager, at any career level. Gottlieb's book not only describes the most important Excel functions, but also dramatically increases the productivity of Excel users. My favorite parts of the book include the sections on Solver, Pivot Tables, and Sensitivity Analysis.

Each of these areas is critical to the work done by advanced financial analysts. Companies are expecting more from their MBA hires today, and this book can give recent graduates a leg up on the competition. The second edition of Next Generation Excel improves on an already excellent first edition. New materials like multi regression simplified and the new Excel features make this an invaluable resource for the Excel user.

Mac users a growing group will like the chapter appendices dedicated to making their lives easier. Would you like to tell us about a lower price? If you are a seller for this product, would you like to suggest updates through seller support?

Take Excel to the next level in accounting and financial modeling In this new Second Edition of Next Generation Excel , Isaac Gottlieb shows financial analysts how to harness the full power of Excel to move forward into the new world of accounting and finance.

Fully updated and revised to include support for Apple users Written by a professor of management and statistics who has taught the discipline for fifteen years Appropriate for professional financial analysts, as well as MBA students For professionals and students whose responsibilities or studies include a full understanding of financial modeling, Next Generation Excel, Second Edition offers comprehensive training.

Read more Read less. Frequently bought together. Total price: Add both to Cart Add both to List.

Subscribe to RSS

These items are shipped from and sold by different sellers. Show details. Buy the selected items together This item: Next Generation Excel: Ships from and sold by Lotus Trading. The Fifth Discipline: Ships from and sold by Amazon. Customers who viewed this item also viewed. Page 1 of 1 Start over Page 1 of 1. Isaac Gottlieb. Financial Modeling and Valuation: Paul Pignataro. From the Inside Flap Most Excel users never realize just how powerful the program really is.

Next Generation Excel: Modeling in Excel for Analysts and MBAs

Gottlieb walks you, step by step, through all of Excel 's advanced business and finance functions with explanations for users of Excel , , and previous versions, as needed , including: Financial tools, including NVP and IRR Data mining using Pivot Tables Charting, sparklines, and other advanced charting options Data analysis tools What-if analysis Statistics for non-statisticians Accounting functions, including amortization tables and depreciation functions Not just another general how-to Excel guide, this fully updated and expanded Second Edition of Isaac Gottlieb's internationally acclaimed Next Generation Excel shows you how to harness the full power of Excel to help you to perform an array of advanced business decision making, financial, and accounting functions.

Read more. Product details Hardcover: Wiley; 2 edition March 4, Language: English ISBN Start reading Next Generation Excel on your Kindle in under a minute. Don't have a Kindle? Try the Kindle edition and experience these great reading features: Share your thoughts with other customers. Write a customer review. Top Reviews Most recent Top Reviews. There was a problem filtering reviews right now. Once you pick your folder, the macro will save each table to a PDF with the table name conveniently appearing in the title of the PDF.

Path If. Worksheets For Each tbl In sht. Range tbl. We append the PDF copies of our spreadsheets to our design calculations. These PDFs used to be converted to microfiche and vaulted for long-term retention.

You know, in case the apocalypse happened. Worksheets If sh.Regardless of which worksheet your chart is on, it will be grabbed and saved to a PDF. Part VII: Shopbop Designer Fashion Brands. This book deserve 5 stars. Jenny rapid tortoise marked it as to-read Aug 02, Part III: From the Inside Flap Most Excel users never realize just how powerful the program really is. The recipient of numerous excellence-in-teaching awards, Dr.