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.

2. Open Exc1TicketSales.xlsx and save it as Exc1TicketSales_LastFirst.xlsx.
4. Merge and centrethe title over the range A1:G1, change its font size to 20, and apply Purple(RGB: 112,48,160) font colour.
6. Type Friday, April 22, 2022 in cell A2, apply the Note cellstyle, 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.

2. 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.
4. Indent twice the data in the ranges A6:A8, A10:A12, and A14:A17.
6. 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.

2. Use Format Painter to copy the formats in cell A4 to the range B4:G4.
4. Wrap text and horizontally centre the labels in the range A4:G4 and set the height of row 4 to 30.
6. Find all occurrences of Purchased in the worksheet and replace them with Sold.
8. 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.

2. 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.
4. Calculate the Percentage Not Sold in cell F6 and copy this formula to the range F7:F17. Delete the formula in cells F9 and F13because those are empty rows.
6. 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.
8. Apply Accounting Numberformat with zero decimal places to the ranges D6:D17 and G6:G17.
10. 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.

2. Insert a new column G. Select and move the range D4:D17 to the range G4:G17. Delete the empty cells in column D.
4. Centre (horizontally) the data in the range B6:C17.
6. Apply Align Right and indent twice the data in the range D6:E17.
8. 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.

2. Insert a blank row between the detailed section and the Summary section. Change the text in cell A22 to bold, 16 pt font.
4. Format row 23 with the same settings as those in row 4.
6. 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.
8. Insert a formula in cell E27 to calculate the average percentage of seats sold that day.
10. 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.
12. 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.

2. Set a 1" top margin and centre the worksheet horizontally between the left and right margins.
4. 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.
6. Rename Sheet1 as 4-22-2022.
8. Copy the worksheet, place the duplicate worksheet to the right, and rename it 4-23-2022.
10. Change the date in cell A2 to Saturday, April 23, 2022. Delete the values in the range C6:C17.
12. Save and close the file.
14. Submit Exc1TicketSales_LastFirst.xlsxfor marking.