Excel Final Exam (Fall 2022)
Complete the attached spreadsheet and answer the multiple choice questions on Blackboard. Please complete and submit your responses and completed Excel file to Blackboard within 2 hours. You will need to submit your completed Excel spreadsheet showing your work in order to receive full credit.
Tab 1 – Basic Excel Formulas
Complete the worksheet in Tab 1 in the attached Excel spreadsheet using the relevant Excel formulas we learned in class to calculate for all three locations: (i) total revenues, expenses, and profits; and (ii) average revenues, expenses, and profits. (12 pts.)
Q#1: What are the average profits across all three stores (Shanghai, Madrid, and New York)? (4 pts.)
Q#2: What are the total profits for the store located in Shanghai? (4 pts)
Tab 2 – Pivot Tables
Go to Tab 2 in the attached Excel spreadsheet. Create a pivot table that organizes the data provided for only the Milan location during the month of January 2015. Organize the row labels into two categories titled “Accessories” and “Clothing” with the products (bags, dress, sunglasses, etc.) as sub-category headings that depicts the $ amount sold per category and product. (10 pts.)
Q#3: What are the total sales of sunglasses for the Milan location during the month of January 2015? (5 pts)
Q#4: What are the total sales of clothing sold in the Milan location during the month of January 2015? (5 pts)
Tab 3 – Income Statements
Change the entire spreadsheet to accounting format, then complete the income statement using the relevant Excel formulas we learned in class. Calculate gross profits, operating expenses, net income, etc. (10 pts)
Q#5: What is the net income if the price is changed to $20 per unit? (5 pts)
Q#6: What are the earnings per share? (Assume the price is $20 per unit.) (5 pts)
Tab 4 – Breakeven Analysis
Go to Tab 4 in the attached Excel spreadsheet. Build the worksheet using the relevant Excel formulas we learned in class. (10 pts)
Q#7: How many units must be sold before the business becomes profitable if each unit is priced at $19 per unit? (5 pts.)
Q#8: How many units must be sold before the business becomes profitable if each unit is instead priced at $20 per unit? (5 pts.)
Tab 5 – Lookup and Countif Formulas
Go to Tab 5 in the attached Excel spreadsheet. Complete the worksheet using the relevant Excel formulas we learned in class to classify each employee as follows: “Below Average” (defined as having an average review score under 5), “Average” (defined as having an average review score falling between 5 to 7), and “High Performer” (defined as having an average review over 7). (16 pts)
Q#9: How many employees were classified as a “High Performer”? (2 pts)
Q#10: How many employees were classified as “Average”? (2 pts)
