Sanjit has a worksheet that details the sales of individual items from the Big Red Wraps menu. He asks you to format the sales figures and design a layout for the printed sheet as you did for the Sales Report workbook. Complete the following:

1. Open the Menu workbook located in the Excel2  Review folder included with your Data Files, and then save the workbook as Menu Sales in the location specified by your instructor.

2. In the Documentation sheet, enter your name in cell B4 and the date in cell B5.

3. Make the following formatting changes to the Documentation sheet:

a. Set the background image to the Background2.png file located in the Excel2  Review folder. b. Format the text in cell A1 in red 26-point bold Calibri Light. c. Format the text in cell A2 to red 10-point italic Calibri Light. Change the text string “Big Flavor” to 14 points. d. Apply the Accent2 cell style to the range A4:A6. e. Change the font color of range B4:B6 to red and change its fill color to white. f. Format the date in the Long Date format and left-align the cell contents.

4. Use the Format Painter to copy the formatting in the range A1:A2 in the Documentation sheet and paste it to the same range in the Menu Sales worksheet. (Hint: You must increase the size of the text “Big Flavor” manually.)

5. Apply the Title cell style to the titles in cells B4, B12, and A20.

6. Make the following changes to the Units Sold table in the range B5:F10:

a. In cell C6, calculate the total number of wraps sold by the company (found in the range C22:N31). In cell C7, calculate the total number of soups. In cell C8, calculate the total number of sides. In cell C9, calculate the total number of salads. b. In cell C10, calculate the sum of the range C6:C9. Copy the formula to cell D10. c. In the range E6:E10, calculate the difference between the 2015 and 2014 values. In the range F6:F10, calculate the percent change from 2014 to 2015. d. Apply the Accent2 cell style to the headings in the range B5:F5. Center the headings in the range C5:F5. e. Apply the Comma style to the values in the range C6:E10. Do not display any numbers to the right of the decimal point. f. Apply the Percent style to the values in the range F6:F10 and show two decimal places. g. Add a top border to the values in the range B10:F10.

7. Make the following changes to the range B13:F18:

a. In cells C18 and D18, calculate the totals of the 2014 and 2015 sales. In the range E14:F18, calculate the change in sales and the percent change. b. Copy the format from the range B5:F10 and paste it into the range B13:F18. c. Change the format for the values in the ranges C14:E14 and C18:E18 to Accounting format with no decimal places.

8. Make the following changes to the Units Sold per Month table in the range A21:O46:

a. In the range O22:O45, calculate the total units sold for each menu item. In the range C46:O46, calculate the total items sold per month and overall. b. Format the headings in the range A21:O21 with the Accent2 cell style. Center the headings in the range C21:O21. c. Format the units sold values in the range C22:O46 with the Comma style and no decimal places. d. Change the fill color of the subtotals in the range O22:O45 and C46:N46 to White, Background 1, Darker 15% (the first color in the third row of the theme colors). e. Merge each of the menu categories in the range A22:A45 into single cells. Rotate the text of the cells up. Increase the font size to 18 points and middle-align the cell contents. f. Format cell A22 with the “Wraps” label in a white font on a Gray-25%, Background 2, Darker 50% fill. Format cell A32 with the “Soups” label in a white font on Blue, Accent 1, Darker 25% fill. Format of cell A37 with the “Sides” label in a white font on a Gold, Accent 4, Darker 25% fill. Format cell A42 with the “Salads” label in a white font on a Green, Accent 6, Darker 25% fill. g. Add a thick box border around each category of menu item in the ranges A22:O31, A32:O36, A37:O41, and A42:O45.

9. Create a conditional format for the subtotals in the range O22:O45 highlighting the top five selling items with a yellow fill and dark yellow text.

10. Create a legend for the conditional format. Enter the text Top 5 Sellers in cell O48. Add a thick box border around the cell, and then use a conditional format that displays this text in dark yellow text on a yellow fill.

11. Set the following print formats for the Menu Sales worksheet:

a. Set the print area to the nonadjacent range A1:F19;A20:O48. b. Remove any automatic page breaks in the large Units Sold table. Insert a manual page break to separate the June and July sales figures. The printout of the Menu Sales worksheet should fit on three pages. c. Scale the printout to 70 percent of normal size. d. Define the print titles to repeat the first three rows at the top of the sheet, and the first two columns at the left of the sheet. e. Increase the left margin of the printout from 0.7 inch to 1 inch. f. Create headers and footers for the printout with a different header for the first page. g. For the first page header, print Prepared by your name in the right section. For every other page, print Filename: file in the left section and date in the right section, where file is the name of the workbook file and date is the current date. (Hint: Use the buttons in the Header dialog box to insert the filename and date.) h. For every footer, print Page page of pages in the center section, where page is the page number and pages is the total number of pages in the printout.

12. If you are instructed to print, print the entire workbook in portrait orientation. Verify that the company name and slogan appear on every page of the Menu Sales worksheet printout, and that the menu category and menu item name appear on both pages with the Units Sold table.

13. Save and close the workbook.