Business Analytics with Microsoft Excel

The learning goals:

1. 

   1. Utilize Excel for Data Analysis
      Develop proficiency in analyzing data sets through Excel.
   2. Integrate Qualitative and Quantitative Insights for Decision-Making
      Make informed business decisions by synthesizing qualitative information with statistical and quantitative analyses.
   3. Effectively Present Data Analysis Findings
      Convey conclusions drawn from data analysis through both verbal communication and visual representations.

Creating a Budget in Excel

We're going to deepen our expertise in Excel, I would like to create a basic spreadsheet to review some fundamental features of Microsoft Excel, and I thought we could accomplish this by developing a budget. Everyone earns income, and everyone has expenses. The money you save at the end of the day is referred to as your personal savings. I'll use salary of $50,000 for this example. Let's establish some tax brackets; for this example, we'll assume a rate of 22 percent. I'd like to calculate what 22 percent of $50,000 amounts to. Whenever you want Excel to perform a calculation, you begin with the equal sign. Other factors I might want to take into account include medical expenses, such as a dental and some people put in their retirement. Once again, all of these factors will differ based on individual circumstances, such as whether you have a high-cost medical plan or seek the best dental care available. Additionally, some individuals, reflecting their personal philosophy, prioritize contributing as much as possible to their retirement savings. You have the salary as the income, then you can subtract your taxes, medical, dental and retirement. What about your rent, food, transportation, loan payment and what I intend to do is subtract my expenses from my income.

Formatting, Formulas, and Functions in Excel

Working with Numbers - Keep Numbers Clean

When inputting numbers into a spreadsheet, place each number in its own cell. Enter the numbers without any symbols—such as dollar signs or punctuation. This ensures that Excel accurately recognizes the values, allowing you to use them effectively in formulas. 

Once you have entered your numbers, you can format them in various ways, such as currency, percentage, date, and more. You can also adjust the number of decimal places shown to enhance the readability of your spreadsheet. To make these changes, go to the Numbers group on the Home tab.

Writing Formulas - Use Cells References

One of the most valuable features of Excel is its ability to function as a calculator through the use of formulas.

1. Starting with an Equal Sign: All formulas in Excel begin with an equals sign (=). This signals to Excel that you are entering a formula rather than just text.
2. Performing Simple Calculations: You can perform basic calculations by directly typing numbers into the formula. For example, to add two numbers, you would write:

   text
   =5 + 3

3. Using Cell References: Instead of hardcoding numbers, you can reference cells that contain the values you want to calculate. For instance, if you want to add the values in cells A1 and A2, you would write:

   text
   =A1 + A2
   

   You can insert cell references either by typing them or by clicking on the desired cell.
4. Combining Numbers and Cell References: You can mix direct numbers and cell references in your formulas. For example:

   text
   =A1 + 10
   

   This formula adds 10 to the value in cell A1.
5. Using Functions: Excel has a variety of built-in functions that can simplify calculations. For example, to sum a range of cells from A1 to A5, you would use:

   text
   =SUM(A1:A5)
   

6. Order of Operations: Excel follows the standard mathematical order of operations (parentheses, exponents, multiplication and division, addition and subtraction) when evaluating formulas. For example:

   text
   =5 + 2 * 3
   

   In this case, Excel multiplies 2 by 3 first and then adds 5, resulting in 11.
7. AutoSum Feature: To quickly sum a column or row of numbers, you can use the AutoSum feature. Select the cell next to the numbers you want to sum, click on AutoSum in the Home tab, and press Enter.

Use cell references in your formulas whenever possible to enhance the dynamism of your spreadsheet. By doing this, any changes made to the values in those referenced cells will automatically reflect in all related formulas. This approach not only reduces manual updates but also facilitates easy experimentation with various inputs.

You can incorporate the fundamental mathematical operators listed in the table below when creating a formula.

Lastly, you can integrate functions from Excel's vast library into your formulas. Throughout the course, you will explore a variety of different functions.

Here are a few functions you’ve encountered:

• SUM: Accepts a list of values and returns their total. It is primarily used to calculate the sum of a range of cells.
• MAX: Takes a list of values and provides the highest value as the output.
• MIN: Accepts a list of values and returns the lowest value.

In these notes, functions are presented in uppercase for emphasis, but it's important to note that Excel does not require case sensitivity when entering functions.

If you're uncertain about which function to utilize, you can explore Excel's function library. Functions are organized into categories such as Math & Trig, Financial, Logical, and more.

You can also explore the function library directly within Excel. The categories of functions are located in the Formulas tab. Alternatively, you can access the function library by clicking the fx button next to the formula bar without navigating to the Formulas tab

Formatting Spreadsheets - Aim for Readability

To improve the readability of your spreadsheet, consider implementing the following strategies:

1. Organize Related Data: Group similar information together, such as keeping costs and revenues in distinct sections of the spreadsheet.
2. Utilize Titles and Headings: Add a main title for your entire spreadsheet along with specific headers for its sub-sections.
3. Highlight Changing Values: Use colors or other visual indicators to mark cells containing adjustable values
4. Display Formulas Next to Calculated Cells: Employ the FORMULATEXT function alongside computed cells. This feature shows the underlying formula used to generate the cell value, allowing others to quickly grasp the logical structure of your spreadsheet model.

By incorporating these techniques, you can significantly enhance the clarity and usability of your spreadsheets. 

Source: Cousera