CFI-Excel-eBook

[Audio] Good morning everyone! We are here today to learn about Excel, one of the most powerful tools for any finance professional. We will be exploring the ways it can be used to simplify and streamline many of the most important financial processes. So, let's get started!.

[Audio] I'm here to tell you about Corporate Finance Institute, CFI for short. CFI is a world-leading provider of online financial analyst training programs. Their courses and certifications have been developed to teach job-based skills in corporate finance, investment banking, corporate development, treasury, financial planning and analysis, and accounting. CFI's courses are tailored to make the complex simple by condensing large amounts of information into an easy-to-follow format. These courses are suitable for students from a variety of professional backgrounds, with the aim of giving you the practical skills, templates and tools necessary to advance your career and set you apart from the competition. If you're looking to become a world-class financial analyst, Corporate Finance Institute is here to help..

[Audio] In this slide, we are discussing the Corporate Finance Institute and Excel, which comes with a copyright from CFI Education Inc. This book is intended to provide accurate information regarding the subject matter and any errors or omissions are not the responsibility of the author or publisher. At various points throughout the manual, a number of financial analysis issues are examined and the financial analysis implications of these issues are discussed. However, this should be treated as an opinion and no responsibility is taken for any action or inaction as a result of the financial analysis included in the manual..

[Audio] In this slide, we will cover the contents of the Corporate Finance Institute's Excel program. We'll start with Excel Shortcuts for both PC and Mac, followed by Editing Shortcuts, Formatting Shortcuts, Navigation Shortcuts, File Shortcuts, Ribbon Shortcuts, Paste Special Shortcuts, Clear Shortcuts, Selection Shortcuts and Data Editing Shortcuts. Additionally, we'll look at Other Shortcuts for data editing within cells. The benefits of using Excel Shortcuts will also be discussed, and we will offer a free Excel course, as well as examining basic Excel formulas, basic terms in Excel, five time-saving ways to insert data in Excel and seven basic Excel Formulas for your workflow. Moving on to Advanced Excel Formulas, we'll explore INDEX MATCH, IF combined with AND / OR, OFFSET combined with SUM or AVERAGE, CHOOSE, XNPV and XIRR, SUMIF and COUNTIF, PMT and IPMT, LEN and TRIM, CONCATENATE, CELL, LEFT, MID and RIGHT functions. Let's get started..

[Audio] In this slide we'll discuss the most useful Excel functions for financial modeling. We'll begin with examining date and time functions such as DATE, EOMONTH, TODAY and YEARFRAC. Moving on to financial functions we'll look at DURATION, NPV, PMT, PPMT, XIRR, XNPV and YIELD. Information functions like ERROR.TYPE, ISBLANK, and logical functions like AND, IF, IFS, OR and CHOOSE will also be covered. Lastly, we'll review math functions like ABS and SUMIF, statistical functions like AVERAGE, CORREL, COUNT, COUNTA, COUNTIF, RANK and SMALL as well as lookup functions like HLOOKUP, INDEX, MATCH, OFFSET and VLOOKUP. Let's start exploring..

corporatefinanceinstitute.com 6. The Corporate Finance Institute Excel.

[Audio] Excel shortcuts available on PC and Mac platforms can help users maximize efficiency when editing. Corporate Finance Institute's online courses can provide insight into the latest and most effective ways of using Excel and its tools. Upgrade skills with these courses and take your proficiency to the next level..

[Audio] I want to talk about some advanced Excel formatting shortcuts that can help you save time and improve the accuracy of your work. Modern Excel offers a wide range of features to assist in quickly and accurately formatting data. A few well-placed clicks and keystrokes can help save time and guarantee accuracy. We'll explore various formatting options for customizing workbooks to make them stand out. Let's begin!.

[Audio] Excel is essential for finance professionals, and the Corporate Finance Institute offers a comprehensive course for mastery. The course covers the fundamentals of Excel, from basic formulas, functions, and shortcuts to advanced topics like data visualization and analysis, financial modeling, and creating macros. This lesson reviews some of the most important navigation shortcuts for working with spreadsheets more efficiently..

corporatefinanceinstitute.com 10. The Corporate Finance Institute Excel.

[Audio] Excel is the number one software for building financial models and analyzing corporate finances, making it essential to know how to quickly and effectively use it. This slide looks at three shortcut types that can assist in becoming more efficient: Selection Shortcuts, Data Editing Shortcuts and Clear Shortcuts. Knowing these shortcuts will help save time and become an Excel expert..

corporatefinanceinstitute.com 12. The Corporate Finance Institute Excel.

[Audio] Gaining proficiency in Excel relies heavily on the mastery of keyboard shortcuts. Utilizing them can significantly boost productivity, speed, and efficiency when analyzing data or creating models. When working on complex tasks, a mouse can be sluggish, so for those aspiring for jobs in investment banking, equity research, finance, FP&A, and accounting, being knowledgeable in Excel's keyboard shortcuts can be of great value. The Corporate Finance Institute provides a free Excel crash course with a personal instructor to learn the shortcuts. They also have a section on Excel formulas as well as a cheat sheet to polish skills. Knowing how to use keyboard shortcuts proficiently in Excel can elevate one's career..

corporatefinanceinstitute.com 14. The Corporate Finance Institute Excel.

[Audio] A good understanding of Excel and its various formulas is essential for finance professionals. Microsoft Excel is widely used for data analysis and financial modeling by top industry specialists and investment bankers. This slide provides an overview and a list of basic Excel functions. With the fundamentals mastered, you can learn advanced Excel formulas and sharpen your skills. Online courses are available to help you gain comprehensive knowledge of the platform and its features. Take a look and you'll be ready to level up your finance knowledge..

[Audio] Excel is lauded for its versatility to produce a range of results. To make the most of the software, it is important to understand the different ways of inserting data into the program. There are five common ways to insert basic formulas, each of which carry their own advantages. These include typing a formula inside a cell, copying and pasting data, and more. We'll delve into each of these methods to create a preferred workflow..

[Audio] The Formulas tab in Excel contains an Insert Function Option that gives you full control over your functions. Utilize the Excel shortcut of ALT + M + F to quickly access the dialogue box, which contains all the functions needed for your analysis. This tool ensures nothing is missed during the process..

[Audio] Excel Formulas tab is a great tool to quickly access a large range of functions and formulas. Starting with the Formulas tab, you can select a group such as Financial, Lookup & Reference, or Math & Trig, and the list of functions will be displayed. If the needed formula doesn't appear, click on the More Function option. In addition, know some Excel formula shortcuts like ALT + M + R for Recently Used and ALT + M + I for Financial can come in handy when searching for the desired formula or function in the spreadsheet..

[Audio] As a teacher in Higher Education, you are likely aware of the importance of using Excel for various tasks and calculations. The AutoSum option is a great and quick tool that can be found in the Home tab, at the far-right corner, or by typing ALT + the = sign in a spreadsheet. It allows users to quickly and efficiently sum up a range of numbers while still ensuring accuracy. Consider how this function can help streamline your workflow..

[Audio] To access the "Quick Insert" feature within the Excel Program, simply click on the Formulas tab and select the "Recently Used" menu, which is located just next to the "AutoSum" option. This feature allows you to quickly insert frequently used tabs, thus making it much easier to complete your formula-based tasks. With this feature, you will be able to save time and energy when completing your tasks..

[Audio] SUM function is a fundamental formula you'll find in Excel. It allows you to aggregate values from various columns and rows, as well as combine different ranges. Using the =SUM(B2:G2) formula, you can calculate the sum of a row. Similarly, you can calculate the sum of a column using =SUM(A2:A8). Additionally, you can add up multiple ranges using =SUM(A2:A7, A9, A12:A15) or combine with other techniques, such as =SUM(A2:A8)/20. SUM function is useful for quickly and accurately calculating totals..

[Audio] We will discuss the AVERAGE function on Excel. This function is used to calculate the average of a certain group of numbers. You can write the AVERAGE function as =AVERAGE(number1, [number2], …). To illustrate, if you have a list of numbers from 1 to 10, you can calculate the average of those numbers by writing =AVERAGE(A1:A10). This is an easy and convenient way of obtaining the average of a list of numbers..

[Audio] We will be exploring the COUNT function in Excel. This function enables you to count the number of numeric values in a given range quickly and easily. Simply type the syntax =COUNT(value1, [value2], …). For example, COUNT(A:A) counts all the numerical values in the A column whereas COUNT(A1:C1) counts the number of rows in the A1 to C1 range. Give the COUNT function a try and see how it helps with your work efficiency!.

[Audio] COUNTA is an important function in Excel that counts all cells in a given range, regardless of cell content. The syntax for the formula is simple, requiring one or more value arguments. As an example, the formula =COUNTA(A:A) will count all cells in column A, regardless of value. It should be noted that the formula cannot be used to count rows..

[Audio] IF function is an incredibly useful tool that can make data processing in Excel much easier. It lets you set a logic and sort data according to it. It provides flexibility to embed formulas and functions inside. As an example, if the value at C3 is less than the value at D3, the cell value will be 'TRUE', else it will be 'FALSE'. It can also be used to compare and do arithmetic operations such as summing up cells and comparing the results..

[Audio] TRIM function is an important tool that helps ensure the accuracy of your spreadsheets. It removes any empty spaces from a single cell, helping eliminate errors due to unruly spaces. However, this can result in duplication of data in the spreadsheet as it only works on a single cell. The formula for the TRIM function is =TRIM (text). For example, TRIM (A4) would remove empty spaces from the value in cell A4..

[Audio] MAX and MIN functions in Excel are useful for quickly and accurately analyzing data. By using the =MIN(B2:C11) function, you can find the lowest number between a column B from B2 and a column C from C2 to row 11 in both column B and C. Similarly, with the =MAX(B2:C11) function, you can locate the largest number within the same columns. All in all, these functions are incredibly helpful when processing data in Excel..

corporatefinanceinstitute.com. The Corporate Finance Institute Excel.

[Audio] Using Microsoft Excel is an essential skill for financial analysts, and if they want to be successful they need to understand the most important and advanced Excel formulas. Our online courses provide the necessary knowledge to help take your financial analysis and financial modeling to the next level. We cover the powerful combination of the INDEX and MATCH functions, an advanced alternative to the VLOOKUP or HLOOKUP formulas that can look up and return a person's height based on their name. Join our online courses for further skill development in Excel and financial analysis, and let us guide you in your journey..

[Audio] In this slide, we are introducing a powerful combination of the IF function and the AND/OR function to simplify financial models. This combination allows for easy auditing of formulas and makes them more understandable for other users. The example included shows how these functions are used together to create a more advanced formula. For further information, the Corporate Finance Institute's free guide on using IF with the AND/OR function is a great resource..

[Audio] Exploring how to combine the OFFSET function with other functions, such as SUM and AVERAGE, can result in a dynamic formula. This formula can sum a variable number of cells and is adjustable by cell reference. To illustrate, a SUM formula in cell F7 can start at B4 and then OFFSET from B4, continuing by the value in E2 minus one. This can create a sum of 3 years of data. This powerful formula can take your spreadsheets to the next level. For those interested, an advanced Excel course is available..

[Audio] CHOOSE is a powerful tool for financial modelling, allowing you to select between a specific number of options and return the one you select. For example, you can use it to compare different revenue growth assumptions such as 5%, 12%, and 18%. Through the CHOOSE formula, you can return 12% when selecting choice #2. To learn more about this tool, and how to use it for scenario analysis in Excel, view our Advanced Excel Formulas course for a video demonstration..

[Audio] XNPV and XIRR are essential formulas for analysts working in investment banking, equity research and financial planning & analysis. They allow analysts to apply specific dates to each individual cash flow, making them incredibly powerful. To learn more about the formulas, our IRR vs XIRR formula guide and XNPV guide offer detailed information. It's also important to note the significance of the language we use..

[Audio] We'll talk about SUMIF and COUNTIF, two advanced formulas in Microsoft Excel. These formulas are great for adding and counting cells that meet certain criteria. For instance, to know how many bottles of champagne needed for a client event, you can use COUNTIF to count all cells that are greater than or equal to 21 by using the formula =COUNTIF(D5:D12, “>=21”). Our Corporate Finance Institute Excel course goes even more in-depth on how to use these powerful features, so make sure to check it out if these topics interest you..

[Audio] PMT formula and IPMT make calculating monthly payments for any fixed rate loan a breeze. Shifting gears, let us look into machine learning and how it can be harnessed to analyze data..

[Audio] Excel is a powerful tool for financial analysts, and these two formulae, LEN and TRIM, are invaluable for organizing and manipulating large amounts of data. Extra white space at the beginning and end of cells can be an issue - LEN and TRIM help resolve that and make it easier to work with, keeping data organized and accurate..

[Audio] Concatenate is an incredibly beneficial tool for financial analysts. It merges data between separate cells, creating more dynamic worksheets. In the provided example, "New York" is coupled with ", " and "NY" to form "New York, NY." This is an effective way of constructing active titles and labels in worksheets, eliminating the need for manually revising cells. This is especially useful with large datasets, reducing time and effort required..

[Audio] This slide is about the advanced Excel functions CELL, LEFT, MID and RIGHT and how they can be combined to create complex formulas. These functions offer a range of options beyond basic data manipulation. The CELL function can provide us details about the contents of a cell, the LEFT function will return text from the beginning of a cell, the MID function can return text from any start point of the cell, and the RIGHT function will return text from the end of the cell. In our advanced Excel formulas class, we will dig into these functions and discover how to use them together for optimum results..

[Audio] Mastering Excel is essential for any financial analyst. The Corporate Finance Institute's Excel courses are tailored to bridge any existing gaps in understanding of the software. Functions in Excel are of utmost relevance: they allow users to perform calculations, operate data, and interact with other formulas and values. This section will tackle the most vital functions for financial modeling..

[Audio] We will be talking about the DATE Function in Excel, which is categorized under Date/Time Functions. This function is very useful for financial analysts as it helps them calculate dates in Excel for their financial models. It takes the year, month and day values from different cells into one function and creates a valid date from it. This can help dynamic links to be created in a financial model. Using the DATE Function as inputs for other functions like SUMIFS or COUNTIFS lets you easily assemble a date using year, month and day values. Let's dive further into the details and usage of the DATE Function in Excel..

[Audio] Using the Date function in Excel, we can quickly and accurately determine dates. For instance, to find the first day of the current year and month, the formula DATE(YEAR(TODAY()), MONTH(TODAY()), 1) can be used, the result being January 11, 2017. To subtract 15 days from a specific date, DATE(2017, 5, 20)-15 can be used for the same, resulting in May 5, 2017. This is a useful way to calculate dates..

[Audio] We will be discussing the best way to use Excel to calculate the percentage remaining in a year based on a given date. The YEARFRAC function is a formula used to get a result that displays the percentage of the year that is still left to go, which is very useful when it comes to analyzing financial data and making decisions. Let's take a closer look at how this formula works..

[Audio] DATE function in Microsoft Excel has a few important points to keep in mind. An argument less than zero or greater than or equal to 10000 will produce the #NUM! error. Non-numeric arguments will lead to the #VALUE! error. Cells may display 41230 instead of a date even when the function returns the correct value; this happens due to the cell formatting, which displays the date serial number instead of the formatted date. To format a cell as a date, select it, then click the Date cell formatting option from the 'Number' group on the Home tab of the Excel ribbon..

[Audio] EOMONTH Function is a great tool to calculate the last day of the month in the past or future. Inputting the start date and number of months either before or after gives the desired result. For example, a due date can be calculated by inputting the current date and a number of months equal to the length of payment agreement. Similarly, analyzing revenue generated from an organization is easier as the last day in the given month can be accurately extrapolated from the input data..

[Audio] In this slide, we will examine how the EOMONTH function in Excel operates. This function allows you to add or subtract a number of months to a date and return the last day of the target month. To illustrate, the following data is used: Row 2 contains the result of the EOMONTH function adding 9 months to a date to return the last day of December. Row 3 shows the function subtracting 9 months to return July 31, 2015. Row 4 simply returns January 31, 2017. Row 5 adds 12 months and returns the last day in January. Lastly, row 6 applies the EOMONTH function with the TODAY function, taking the current date of November 18, 2017, and adding 9 months to it to return August 31, 2018. Bear in mind that the EOMONTH function will output a serial date value. This is how Excel stores dates internally, and is the amount of days since January 1, 1900. That is all for the EOMONTH function in Excel..

[Audio] Combining the powerful Excel functions EOMONTH and SUMIFS makes for an easy way to calculate the total revenue achieved for any month. EOMONTH returns the last day of the month with an offset of a specified number of months and SUMIFS applies a sum to rows in a range based on a given criteria. Together, they can quickly sum total revenue for a specified month..

[Audio] SUMIFS is a powerful function in Excel which allows us to analyze data and add up values based on criteria provided. For example, to sum up sales that occurred in January, we can provide two criteria to the SUMIFS formula, two dates that match the start and end of the month. The first criterion is to match dates greater than or equal to the first day of the month and similarly, to match dates less than or equal to the last day of the month. In this slide, the SUMIFS formula added up all sales that occurred in January to give 24 as the result. It is important to remember when adding date criteria, we must first type 1/1/2017 and then using a custom format, change it to a custom date format. This ensures that the formula works correctly..

[Audio] EOMONTH is an incredibly useful function in Excel for quickly calculating the end of any month, given a start date and number of months. Keep in mind: if either the supplied start date is not a valid Excel date or the supplied start date plus the number of months is not a valid Excel date, a #NUM! error will be given. Similarly, any non numeric arguments will result in a #VALUE! error. If a decimal value is provided for months, the EOMONTH function will only add the integer portion to the start_date..

corporatefinanceinstitute.com 49. The Corporate Finance Institute Excel.

[Audio] The Corporate Finance Institute offers a wide range of courses to help you manage your financial data. Excel is a powerful software tool that can be used to great effect to unlock the potential of your data. In this slide, we will take a look at one example of how we can use Excel to automatically update data based on the current date. By applying a function to the worksheet and entering specific formulas, we can generate varied results depending on the date the worksheet is opened. Thanks to this example, you can easily understand how Excel can be used to your advantage and manage your financial data more efficiently. That concludes this presentation. Thank you for your attention..