Can it be done
Assignment 2: Excel Credit Weight: 25% of your final grade Due Date: after you complete the readings and learning activities for Lessons 5, 6, and 7. Please review the Submitting Assignments page before you begin this assignment. You must download data files to complete each textbook exercise. Refer to the Assignment 2 Dropbox page to locate these files. Instructions: General Each exercise will require you to create or modify one or more files. The assignment instructions state exactly how the file names for the results should be constructed. For example, for Assignment 2, the instructions require the input file Exc1TicketSales.xlsx. Save this file as Exc1TicketSales_LastFirst.xlsx Replace LastFirst with your name, e.g., Exc1TicketSales_SmithJane.xlsx Each exercise will specify which created or modified files should be submitted for marking. Excel Exercise 1: Waterfront District Theatre You work in the accounting division at Waterfront District Theatre where Broadway plays and musicals are performed. You had previously started a worksheet that lists the number of seats in each section (orchestra and tiers) and the number of seats sold for each specific performance date. You will now complete the worksheet by calculating the percentage of sold and unsold seats, gross revenue, and summary calculations by seating section. You will format the worksheet to improve readability and copy the final worksheet to use as a template to enter data for the next day’s performance. Format the Title and Enter the Date Your first task is to format the title by centering it over the data columns, enlarging the font size, and applying a different font colour. Next, you will enter and format the performance date in the next row. Open Exc1TicketSales.xlsx and save it as Exc1TicketSales_LastFirst.xlsx. Merge and centre the title over the range A1:G1, change its font size to 20, and apply Purple (RGB: 112,48,160) font colour. Type Friday, April 22, 2022 in cell A2, apply the Note cell style, and then merge and centre the date over the range A2:G2. Format Seating Labels Section labels have already been included for Orchestra Front and Tiers to identify the seating sections. You will insert a new row for Orchestra Back, indent the specific seating sections to distinguish these labels from the main labels, and adjust the column width. Insert a new row above row 9 (above Left and below Right). The added row will now be row 9. Copy the data from cell A5 to cell A9 and change the data in cell A9 from Orchestra Front to Orchestra Back. Indent twice the data in the ranges A6:A8, A10:A12, and A14:A17. Change the width of column A to 18. Format Labels, Replace Text, and Check Spelling Purple fill and bold was previously applied to the label in cell A4. You will use the Format Painter to copy the format to the other column labels in row 4. Then you will apply other alignment settings to the labels, replace words, and check spelling. Use Format Painter to copy the formats in cell A4 to the range B4:G4. Wrap text and horizontally centre the labels in the range A4:G4 and set the height of row 4 to 30. Find all occurrences of Purchased in the worksheet and replace them with Sold. Check spelling in the worksheet and correct all spelling errors. Insert Formulas and Apply Number Formats You are ready to enter formulas in the last three columns to calculate the percentage of seats sold at the performance, percentage of unsold seats (i.e., empty), and the gross revenue for the sold seats. Calculate the Percentage Sold in cell E6 and copy this formula to the range E7:E17. Delete the formula in cells E9 and E13 because those are empty rows. Calculate the Percentage Not Sold in cell F6 and copy this formula to the range F7:F17. Delete the formula in cells F9 and F13 because those are empty rows. Calculate the Gross Revenue in cell G6 and copy this formula to the range G7:G17. Delete the formula in cells G9 and G13 because those are empty rows. Apply Accounting Number format with zero decimal places to the ranges D6:D17 and G6:G17. Apply Percentage style with one decimal place to the range E6:F17. Move a Column, Adjust Alignment, and Add Borders After reviewing the data, you will move the Price Per Seat information to the left of the Gross Revenue data. You will also centre the values in the Seats in Section and Seats Sold columns. Insert a new column G. Select and move the range D4:D17 to the range G4:G17. Delete the empty cells in column D. Centre (horizontally) the data in the range B6:C17. Apply Align Right and indent twice the data in the range D6:E17. Apply Outside Borders to each of the following ranges, one range at a time: A4:G4, A5:G8, A9:G12, and A13:G17. Calculate Summary Statistics You will insert formulas to calculate the values in the Summary section and format these values the same way as in the detailed section. Insert a blank row between the detailed section and the Summary section. Change the text in cell A22 to bold, 16 pt font. Format row 23 with the same settings as those in row 4. Use the data in the range B6:F17 to calculate the Seats in Section, Seats Sold, Seats Not Sold, and Gross Revenue amounts in the Summary section. Sum these amounts in row 27. Insert a formula in cell E27 to calculate the average percentage of seats sold that day. Align Centre the range B24:D27. Display the values in range E24:E27 as percentages with one decimal and right indent these values twice. Display the values in range F24:F27 as currency with zero decimals. Underline the values in range B26:F26 and apply bold format to the total line. Extend the Outside Border to the entire Summary table. Format the Worksheet To finalize the worksheet, you will set a larger top margin, centre the worksheet between the left and right margins, and insert a footer. Finally, you will rename the sheet tab, copy the worksheet, and delete some data so that this new worksheet can be used as a template for the next day’s performance. Set a 1" top margin and centre the worksheet horizontally between the left and right margins. Insert a footer with your name on the left side, the sheet name code in the centre, and the file name code on the right side. Rename Sheet1 as 4-22-2022. Copy the worksheet, place the duplicate worksheet to the right, and rename it 4-23-2022. Change the date in cell A2 to Saturday, April 23, 2022. Delete the values in the range C6:C17. Save and close the file. Submit Exc1TicketSales_LastFirst.xlsx for marking. Excel Exercise 2: Best Appliances You are an account manager for Best Appliances, a local appliance store that also provides financing, delivery, and installation. As part of your daily tasks, you create an Excel workbook that reports sales, payment plan information, and summary statistics. Insert Current Date To ensure proper documentation, you will insert the current date into the worksheet. 1. Open the Exc2Appliances.xlsx workbook and save it as Exc2Appliances_LastFirst.xlsx. 1. Insert a function in cell B2 to display the current date and format it as a Long Date. 1. Set column B’s width to Autofit. Create Item Name and Price Lookups Your first task is to use a lookup function based on the data in the range A18:C23 to determine the name of the item purchased and the corresponding price based on the provided SKU number. Insert a function in cell C5 to display the item name based on the provided inventory lookup information. Copy the function from cell C5 down through C13 to complete column C. Set column C’s width to 12.5. Insert a function in cell E5 to display the item price based on the provided inventory lookup information. Copy the function from cell E5 down through E13 to complete column E. Determine Delivery Fee You will calculate the total due for each customer’s order. The total is the purchase price plus an optional delivery charge. Insert an IF function in cell F5 to calculate the total due. If the customer has chosen home delivery, there is an additional delivery charge located in cell B25. Copy the function from cell F5 down through F13 to complete column F. Insert a function in cell F14 to calculate the total due for all orders in column F. Calculate the Monthly Payment Your next step is to calculate the periodic payment for each customer’s purchase. The payments are based on the years financed in column G and the annual interest rate in cell B26. All accounts are paid monthly. Insert a function in cell H5 to calculate the first customer’s monthly payment. Copy the function down the column to cell H13. Insert a function in cell H14 to calculate the total monthly payment for all orders in column H. Calculate Statistics You will perform some basic statistical calculations on the sales orders. Insert a function in cell H18 to calculate the total number of orders. Insert a function in cell H19 to calculate the lowest monthly payment in column H. Insert a function in cell H20 to calculate the average monthly payment in column H. Insert a function in cell H21 to calculate the highest monthly payment in column H. Insert a function in cell H22 to calculate the median monthly payment in column H. Apply Currency format with two decimals to the values in range H19:H22. Finalize the Workbook You will finalize the workbook with formatting and page setup options. You will also create a new worksheet that displays the cell formulas. Apply Currency format with two decimals to the monetary values in range E5:H14. Underline the values in cells F13 and H13. Bold the totals in cells F14 and H14. Centre the values in the Delivery and Years columns. Review the worksheet to ensure that appropriate relative and absolute cell references were used in the functions in the range B5:H13. Insert a footer with your name on the left side, the sheet name code in the centre, and the file name code on the right side. Change the worksheet settings so that it will display and print on a single page. Save the workbook. Copy the Sales worksheet and move the new worksheet to the right of the Sales tab and rename it Formulas. Display the cell formulas on the Formulas worksheet and change it to a landscape orientation. Adjust the column widths so that the formulas will display and print on a single page. Use the Page Setup settings in the Page Layout tab to print gridlines and column headings. Save and close the workbook. Open the workbook again to see whether the formulas appear on the Formulas worksheet. If not, turn on the display cell formulas feature again and take a screenshot of the worksheet contents. This screenshot should be displayed in a new worksheet tab labelled Screenshot. Save and close the workbook again. Submit Exc2Appliances_ LastFirst.xlsx for marking. Excel Exercise 3: Better You Fitness Centre You and a business partner opened a fitness centre three years ago. Your partner manages the operations of the gym, looking after equipment, maintenance, fitness classes, and fitness staff. You manage the business aspects, such as marketing, finance, and general personnel issues. As the business nears the end of its third year, you have put together the financial reports. Now you want to create charts and insert sparklines that show the trends so that you can discuss them with your partner. Having the data displayed more visually will make more sense to your business partner. Create and Move a Basic Chart You want to focus on just the expenses for the current year. Creating a pie chart will help your partner visualize the breakdown of all operating expenses for that year. After you create the chart, you will move it to a new chart sheet and add a meaningful title. 1. Open Exc3Gym.xlsx and save as Exc3Gym_LastFirst.xlsx. 1. Insert a 2-D pie chart using the ranges A11:A19 and D11:D19 on the Income worksheet and move this chart to a new chart sheet named Expenses. Move the chart sheet to the right of the Membership sheet. 1. Change the chart title to Expenses for Year 3. Make the title bold and the font size 20. Add and Format Chart Elements You will format the pie chart with data labels and remove the legend because there are too many categories for the legend to be effective. You will also add a gradient fill colour to the chart area. Remove the legend from the pie chart. Add Percentage and Category Name data labels and choose the Best Fit position for the labels. Change the data labels’ font size to 10. Explode the Education & Training slice by 12%. Add a Light Gradient – Accent 2 fill colour (RGB: 254,248,245) to the chart area. Add the Alt Text Displays percentage of expenses for Year 3. (Include the period). Create a Chart and Apply Filter Seeing how Payroll and Cost of Sales make up most of the expenses, you will create a chart to focus on the other expenses. You will create a clustered bar chart, filter out Payroll and Cost of Sales, add a title, and move the chart to the Summary worksheet. Insert a clustered bar chart using the ranges A4:D4 and A11:D19 on the