Acct 315 Excel 2 Financial Formulas In Class

Practice Using Financial Formulas in Excel: Instructions Objective: Learn to use Excel financial formulas to analyze a dataset with 100 rows of accounting data. Step-by-Step Instructions Step 1: Download and Open the Dataset 1. Download the dataset: Sample_Financial_Data.xlsxDownload Sample_Financial_Data.xlsx 2. Open the file in Excel to view the data. Step 2: Familiarize Yourself with the Dataset • Review the columns: Date, Transaction Type, Account, Amount, and Notes. • Observe the types of transactions (Revenue or Expense) and their associated accounts. Step 3: Add Calculations to Analyze the Data presented in a way that a user can easily understand Perform the following tasks in Excel: 1. Calculate Total Revenue and Expenses: o Add calculations to show Total Revenue and Total Expenses. o Use the SUMIF formula:  For Revenue: =SUMIF(B2:B101, "Revenue", D2:D101)  For Expenses: =SUMIF(B2:B101, "Expense", D2:D101) 2. Identify the Highest and Lowest Transaction Amounts: o Use the MAX formula in a new cell: =MAX(D2:D101) for the highest amount. o Use the MIN formula in another cell: =MIN(D2:D101) for the lowest amount. 3. Calculate the Average Transaction Amount: o Use the AVERAGE formula: =AVERAGE(D2:D101). 4. Categorize Transactions by Month: o Use the MONTH formula to extract the month from the Date column. o Add a new column titled Month and populate it with =MONTH(A2) (drag to fill down). o What is the total for each month within the data? 5. Analyze Revenue and Expense by Account: