61FIN3FMO – FINANCIAL MODELING
LAB ACTIVITIES 1 – INTRODUCTION TO EXCEL SPREADSHEET
Concept to be learned: Understanding data, identifying objectives
Skills to be learned: Excel interfaces, Cell references, Excel functions: Average, Max, Min, Sum,
Sumproduct
Activity 1. Set up an environment
1. Create a new workbook:
• Open Microsoft Excel.
• Click on "File" in the top-left corner.
• Select "New" and then "Blank Workbook."
• Save the workbook with your tutorial chosen topic.
2. Set up the "Raw Data" sheet:
• Double-click on the default "Sheet1" tab at the bottom to rename it "Raw Data."
• Right-click on the "Raw Data" tab, choose "Tab Color," and set the color to "Blue."
3. Copy the tutorial data onto the "Raw Data" sheet:
• Open the original worksheet containing the data for Lab 1-3.
• Select the entire data range, including headers and values.
• Press Ctrl+C (or right-click and select "Copy").
• Switch to the "Raw Data" worksheet in the new workbook.
• Select cell A1 and press Ctrl+V (or right-click and select "Paste").
Activity 2. Using the "Filter" function in Excel:
1. Select the data range:
• Click on any cell within the dataset.
• Go to the "Data" tab in the Excel ribbon.
• Click on "Filter" in the "Sort & Filter" group.
2. Filter the data:
• Click on the filter icon (downward-facing triangle) in the header row of a column.
• Choose the criteria to filter by, such as specific values or conditions.
• Excel will display only the rows that meet the selected criteria.
Activity 3. Using Excel functions to analyze the data:
In this activity, we will answer the open questions using Excel functions.
1. What is the average value of your question?
• To calculate the average of a range of values, use the AVERAGE function.
• Example: =AVERAGE(A2:A100)
2. What is the maximum value of your question?
• To find the maximum value in a range of values, use the MAX function.
• Example: =MAX(A2:A100)
3. What is the minimum value of your question?
• To find the minimum value in a range of values, use the MIN function.
• Example: =MIN(A2:A100)
4. What is the sum for all of the values?
• To calculate the sum of a range of values, use the SUM function.
• Example: =SUM(A2:A100)
5. What is the total sum for the product of a particular subject?
• To find the total sum of the product of two or more columns, use the SUMPRODUCT
function.
• Example: =SUMPRODUCT(B2:B100, C2:C100)
Activity 4. Using cell references in Excel with an example of a Multiplication Table
1. Insert a new worksheet:
• Click on the tab located next to the existing tabs.
• Name the new worksheet "Multiplication Table”
• Format the worksheet color in dark orange
2. Make a row of continuous integers (i.e. 1,2,3...15) and a column of continuous integers
(i.e. 1,2,3...15)
3. Create a 20x20 multiplication table. For example: if you want to know what is 5 times
4, go to where column "5" crosses row "4", the cells say "20"that is the answer. Below is example
of a 5x5 multiplication table.
4. You will enter formula in B2 cell then Drag-and-drop copy it to other cells. In that
formula, try to use ABSOLUTE reference, RELATIVE reference, and MIXED reference. Which
one will work for you? Create a textbox and type your answers in that textbox.
5. Format the table in a way that looks good for you.
