Learn MS Excel and Data Analysis

Every excel spreadsheet contains multiple rows and columns. When working with data, you will have to know how to add, delete, and modify both rows and columns.

Excel offers plenty of formatting options. A quick way of reducing and adding decimal places can be formed under Number tab, where you can add or deduct with just one click. In excel you can have various information such as date and time and those cells has to be formatted properly so that excel can read and calculate formulas easier. There are formulas that can calculate date range for you. For example, how many days left in a year. You can set date and time Depending on your region and you can set those under more number formats.

Your spreadsheets need to look professional so that everyone can read them easily. In the home tab you can find all the things you need in order for your spreadsheet look professional. You can change the font, font size, colors, alignment, and styles.

In order to copy a cell or a range of cells, you have two options: 1) Right-click on the cell and select “Copy” 2) Select the cell and use the “Ctrl + C” shortcut (preferred). Once you have copied the content of a cell, you can paste it into another cell. You can either do it with a right-click and then select paste or use the Ctrl + V shortcut, which is much faster.

The Paste Special dialog box (which you can open by using the “Alt + E + S” shortcut) provides many pasting options. For example, you can choose to paste only the formulas of the copied cells, paste the values of the copied cells, or paste only the format of the copied cells

What are macros in Excel? An Excel macro is a series of instructions. After you've created a macro, Excel will execute those instructions, step-by-step, on any data that you give it. For example, you can have macro for formatting your spreadsheet - always start your worksheet with the same formatting.

Every formula in excel can be started with an equal or plus sign.

Freeze panes let you view your data in an organized way when you have many rows and columns.

SUM formulas in Excel are the easiest and most commonly used. They let you sum the cells and sum only if certain conditions are met.

The COUNT function counts the number of cells that contain numbers and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

The average function returns the average (arithmetic mean) of the arguments. You can average the numbers if conditions are met with average if functions.

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. Check if a certain condition is true or false with IF function.

Use VLOOKUP and HLOOKUP when you need to find things in a table or a range by row / column. Mostly commonly used in finance to find product by name or code.

INDEX and MATCH are great VLOOKUP replacements. INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups.

Goal seek is the art of what-if analysis tool. You can find this function under Data, Forecast, and then what-if analysis.

With goal seek you can find the answers or desired result for a given parameter by changing other parameters related to it.

Pivot tables are dynamic and interactive tables, and pivot tables can summarize thousands of rows of data in just a few seconds.

Choose function returns a value from an array corresponding to a supplied index number (position). This is a perfect function for financial modeling.

Min and max functions are very simple and quite intuitive.

Min returns the smallest number in a set of values, while max returns the highest number in a set of values.

Conditional formatting can help you add colors, icons, and visuals to your table and numbers.

It will help you read data faster as well.

Relative references change when a formula is copied to another cell.

On the other hand, absolute references remain constant no matter where they are copied. Fixing cell references make your job much easier because copying is much faster and help you organize your worksheets.

We can insert a chart by selecting the cells we want to present and then clicking on the insert tab….

If you are not sure which chart is the best for your data, you can click on the recommended chart.

Excel will recommend an excellent chart to showcase your data. Then you can pick which graph is the best fit.

We often want to combine numbers into the same chart and then analyze them. That is why we need a secondary axis in our charts.

Use the Find and Replace features in Excel to search for something in your workbook or worksheet, such as a particular number or text string.

With the DATE function, you can manipulate data and extract a certain number of days, years, months. And this is possible because excel stores date as numbers.

When deciding, we need to discount all future cash flow to see if the project is feasible or not. Why we do that is because the dollar in the future is not worth the same as the dollar today.

NPV is defined as the PV of the cash flows from an investment minus the initial investment. The discount rate is the interest rate used to determine the present value of future cash flows in a discounted cash flow (DCF) analysis.

The dividend discount model (DDM) is a quantitative method used for predicting the price of a company's stock based on the theory that its present-day price is worth the sum of all of its future dividend payments when discounted back to its present value.

When evaluating our investment, we can also have other criteria to consider. And this would be the internal rate of return or IRR.

IRR is simply the discount rate at which the net present value, NPV, of the project equals zero. The NPV rule states that you invest in any project which has a positive NPV when its cash flows are discounted at the opportunity cost of capital.

The IRR rule states that you invest in any project offering a rate of return that exceeds the opportunity cost of capital.

PMT function tells you what your monthly loan payment is. With this table, you can see how your interest payment has decreased over the years.

With the mortgage loan amortization table, the amount going toward principal starts small and gradually grows more extensive month by month.

Scenario analysis is a process of examining and evaluating possible events or scenarios that could take place in the future and predicting the various feasible results or possible outcomes. Sensitivity analysis is the study of how the uncertainty in the output of a model (numerical or otherwise) can be apportioned to different sources of uncertainty in the model input.

Ratios standardize numbers and facilitate comparisons.

Ratios are used to highlight weaknesses and strengths.

Ratio comparisons should be made through time and with competitors.

Ratios are very useful to see how the company is doing. However, you always need to compare ratios of one company with other companies in the same industry and also with the industry average. And you can do this for the last 3 or 4 years.

The price to book ratio is important because it can help investors understand whether a company's market price seems reasonable compared to its balance sheet. Any value under one is considered a good value, indicating an undervalued stock.