Question: Lab 1, Part 2: Independent Application - Expense Analysis and Summary (Excel) In Progress NEXT UP: Submit Assignment Unlimited Attempts Allowed Click here to download
Lab 1, Part 2: Independent Application - Expense Analysis and Summary (Excel)
In Progress
NEXT UP: Submit Assignment
Unlimited Attempts Allowed
Click here to download a printable PDF version of these instructions
Overview: Why Excel Matters
Excel is a foundational tool in accounting and data analysis. It helps businesses organize, analyze, and visualize financial information quickly and accurately. In this lab, you'll practice essential Excel skills used in real accounting tasksfrom basic formulas to PivotTables and charts.
Getting Started: Open the Excel File
Before you begin, make sure to download the Excel file titled Lab1_Part2.xlsx Download Lab1_Part2.xlsxand save it somewhere easy to find, like your Downloads folder or Desktop.
- Open Microsoft Excel on your computer.
- Open the Lab1_Part2 file you just downloaded
You'll be working directly in this Excel file as you complete the steps outlined in the instructions below.
Business Context:
You've been given Q1 purchase records and asked to identify small purchases, classify them, and summarize spending trends for future budgeting decisions.
Sheet 8: Analyze Purchases
- Rename Sheet8 to Your_Turn_Analysis
- User-Entered Formula with Mixed Referencing:
- In the After-tax Amount column, use a formula to calculate the total amount including tax for each row
- The tax rate is stored in a single cell (cell K1)use an absolute reference to lock that cell in the formula
- Ensure your formula uses a relative reference for the row's amount
- Nest the after-tax formula you wrote inside of the ROUND function to ensure the values are stored to the hundredths place
- Use IF logic in the Needs Aggregation? column to flag amounts under $200. If the after-tax amount is under $200, the IF function should output "Yes", otherwise it should output "No"
- Format Columns:
- Format the Pre-tax Amount and the After-tax Amount columns with Comma Style, showing 2 decimal places
- Format the Michigan Use Tax Rate value using the Percentage Style and round to the nearest tenth of a percent
- Use SEARCH + OR + ISNUMBER + IFS logic in the Expense Type column to assign by searching for keywords:
- "Supplies" keywords: notebooks
- "Printing" keywords: toner, paper
- "Mailing" keywords: postage, envelopes
- Else "Other"
- Assign Account Numbers using XLOOKUP from the M1:N5 table
- Conditional Formatting:
- Highlight any After-tax Amount values under $50 in red font (from the filtered data set)
- Apply Filters:
- Show dates before 3/1/2024
- After-tax Amounts 750
- Sort: By After-tax Amount (Smallest to Largest)
Sheet 9: Build PivotTable Summary
- Rename Sheet9 to Your_Turn_Pivot
- Select cells A1:H22 (this selection should include one blank row) in the Your_Turn_Analysis worksheet. (Be careful to not select the cells containing the Michigan Use Tax rate and label)
- With the cells in step 2 selected, insert a PivotTable into cell A1 of the Your_Turn_Pivot worksheet
- Rows: Expense Type Values: Sum of After-tax Amount
- Filter the PivotTable so that it does not display the row labeled "(blank)".
- Add Calculated Field named "Large Amount". Use this logic for the calculation: if the After-tax amount is greater than $2,000, display the number 1, else display the number 0. Display the Large Amount Amount field in the pivot table and change the label in Cell C1 to "Large Amount?" Display the 1's and 0's in this column with zero decimal places.
Sheet 10: Visual Analysis
Business Context: You're reviewing monthly data on machine usage and overhead spending. Management wants to understand whether factory overhead costs are related to machine hours worked.
- Rename Sheet10 to Visual_Analysis
- Review the data: This sheet includes two numeric columns:
- Machine Hours (X-axis)
- Total Factory Overhead (Y-axis)
- Create a Scatter Plot: Insert a chart that visualizes the relationship between these two variables
- Format the Chart:
- Update the chart title to something meaningful (e.g., Factory Overhead vs. Machine Hours)
- Add axis titles: label the X-axis and Y-axis appropriately
- Change the chart style to one you feel is clean and easy to read
Reflection Sheet Questions
Use the Reflection worksheet (two textboxes provided) to answer the following:
- In your own words, what is the benefit of using functions like IF, XLOOKUP, or conditional formatting when reviewing a large transaction file?
- How could visualizationslike the ones you builthelp managers make smarter budget decisions? Give one specific example from the lab where a chart or summary might reveal a trend.
- Suppose you had access to 10,000 transactions instead of a few dozen. Which Excel tools or skills from this lab would help you manage that much data efficientlyand why?
- Look at the scatter plot you created. Did the data seem to follow a linear pattern? A linear pattern means that as one value increases, the other tends to increase (positive slope) or decrease (negative slope) in a way that could be described as looking like a straight-line. Did your chart show a positive or negative relationship between the two-variables? What does that mean in this case? Would machine hours be a good predictor of total overhead costs based on what you saw? Why or why not?
Submission Checklist
- All worksheets renamed correctly
- All columns autofit and formatted
- All formulas, logic, and charts complete
- Reflection questions answered
| Date | Vendor | Description | Pre-tax Amount | After-tax Amount | Needs Aggregation? | Expense Type | Account # | Michigan Use Tax Rate | 6% | Expense Type | Account # | |||||||
| 2024-02-03 | FedEx | desk chair | 45.00 | Supplies | 6001 | |||||||||||||
| 2024-01-24 | Amazon Business | mailing envelopes | 96.00 | Mailing | 6010 | |||||||||||||
| 2024-01-10 | FedEx | USB cables | 885 | Printing | 6025 | |||||||||||||
| 2024-01-01 | Amazon Business | desk chair | 127.00 | Other | 6099 | |||||||||||||
| 2024-01-19 | FedEx | printer toner | 131.00 | |||||||||||||||
| 2024-02-12 | Amazon Business | copy paper | 146.00 | |||||||||||||||
| 2024-02-22 | OfficeMax | notebooks | 239.00 | |||||||||||||||
| 2024-01-15 | Staples | copy paper | 295.00 | |||||||||||||||
| 2024-02-26 | OfficeMax | postage stamps | 355.00 | |||||||||||||||
| 2024-01-05 | Staples | postage stamps | 460.00 | |||||||||||||||
| 2024-02-17 | FedEx | desk chair | 491.00 | |||||||||||||||
| 2024-02-07 | Staples | mailing envelopes | 639.00 | |||||||||||||||
| 2024-01-29 | OfficeMax | printer toner | 725.00 | |||||||||||||||
| 2024-03-02 | USPS | copy paper | 483 | |||||||||||||||
| 2024-03-07 | USPS | postage stamps | 112 | |||||||||||||||
| 2024-03-12 | Staples | whiteboard markers | 397 | |||||||||||||||
| 2024-03-16 | FedEx | notebooks | 124 | |||||||||||||||
| 2024-03-21 | FedEx | notebooks | 896 | |||||||||||||||
| 2024-03-26 | USPS | copy paper | 688 | |||||||||||||||
| 2024-03-31 | FedEx | notebooks | 155 | |||||||||||||||
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help