Light red fill with dark red text excel

Data Files needed for the Review Assignments: Menu.xlsx, Background2.jpg

Carol created a workbook that tracks the sales of individual items from the Morning Bean menu to share at an upcoming conference. She has already entered most of the financial formulas but wants you to calculate some additional values. She also asks you to format the workbook so that it will look professional and be easy to read and understand. Complete the following:

1. 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. 2

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

3. 3

Change the theme of the workbook to Retrospect.

4. 4

Make the following formatting changes to the Documentation sheet:

1. Set the background image to the Background2 JPEG file located in the Excel2 > Review folder.

2. Format the text in cell A1 in a 26-point bold Calibri Light.

3. In cell A1, change the font color of the word “Morning” to the Orange, Accent 1 theme color and change the font color of the word “Bean” to the Brown, Accent 3 theme color.

4. Format the text in cell A2 in 18-point, italic, and change the font color to the Brown, Accent 3 theme color.

5. Format the range A4:A6 with the Accent 3 cell style.

6. Change the font color of the range B4:B6 to the Brown, Accent 3 theme color, and change the fill color to the White, Background 1 theme color.

7. In cell B5, format the date in the Long Date format and left-align the cell contents.

5. 5

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 Items worksheet. Change the font colors in cell A1 of the Menu Items worksheet to match the colors used in cell A1 of the Documentation sheet.

6. 6

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

7. 7

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

1. Apply the Accent3 cell style to the headings in the range B5:F5. Center the headings in the range C5:F5.

2. In cell C6, use the SUM function to calculate the total number of specialty drinks sold by the company (found in the range C22:N31 in the Units Sold per Month table). In cell C7, use the SUM function to calculate the total number of smoothies sold (in the range C32:N36). In cell C8, use the SUM function calculate the total number of sandwiches sold (in the range C37:N41). In cell C9, calculate the total number of soups sold (in the range C42:N45).

3. In cell C10, use the SUM function to calculate the total units sold from all menu types in 2017 (based on the range C6:C9). Copy the formula to cell D10 to calculate the total units sold in 2016.

4. In each cell of the range E6:E10, calculate the change in units sold between the 2017 and 2016 values. In each cell of the range F6:F10, calculate the percent change from 2016 to 2017. (Hint: The percent change is the net change divided by the 2016 value.)

5. Format the range C6:E10 with the Comma style and no decimal places.

6. Format the range F6:F10 with the Percent style and two decimal places.

7. Add a top border to the range B10:F10.

8. 8

Make the following changes to the Gross Sales table in the range B13:F18:

1. In cells C18 and D18, use the SUM function to calculate the totals of the 2017 and 2016 sales.

2. In the range E14:F18, enter formulas to calculate the net change and the percent change in sales.

3. Use the Format Painter to copy the formatting from the range B5:F10 to the range B13:F18.

4. Format the ranges C14:E14 and C18:E18 with Accounting format and no decimal places.

9. 9

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

1. In the range O22:O45, use the SUM function to calculate the total units sold for each menu item. In the range C46:O46, use the SUM function to calculate the total items sold per month and overall.

2. Format the headings in the range A21:O21 with the Accent3 cell style. Center the headings in the range C21:O21.

3. Format the units sold values in the range C22:O46 with the Comma style and no decimal places.

4. Change the fill color of the subtotals in the range O22:O45,C46:N46 to the White, Background 1, Darker 15% theme color (the first color in the third row).

5. Merge each of the menu categories in the ranges A22:A31, A32:A36, A37:A41, and A42:A45 into single cells. Rotate the text of the cells up, and middle-align the cell contents.

6. Format cell A22 with the Accent1 cell style. Format cell A32 with the Accent2 cell style. Format cell A37 with the Accent3 cell style. Format cell A42 with the Accent4 cell style. Change the font size of these four merged cells to 14 points.

7. Add thick outside borders around each category of menu item in the ranges A22:O31, A32:O36, A37:O41, and A42:O45.

10. 10

Use conditional formatting to highlight negative values in the range E6:F10,E14:F18 with a light red fill with dark red text to highlight which menu categories showed a decrease in units sold or gross sales from 2016 to 2017.

11. 11

Use conditional formatting to format cells that rank in the top 10 percent of the range C22:N45 with a green fill with dark green text to highlight the menu items and months that are in the top 10 percent of units sold.

12. 12

Create a legend for the conditional formatting you added to the worksheet. In cell O20, enter the text Top Sellers. Add thick outside borders around the cell, and then use conditional formatting to display this text with a green fill with dark green text.

13. 13

Set the following print formats for the Menu Items worksheet:

1. Set the print area to the nonadjacent range A1:F19,A20:O46.

2. Switch to Page Break Preview, and then remove any automatic page breaks in the Units Sold per Month 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.

3. Scale the printout to 70 percent.

4. Create print titles that repeat the first three rows at the top of the sheet and the first two columns at the left of the sheet.

5. Increase the left margin of the printout from 0.7 inch to 1 inch.

6. Create headers and footers for the printout with a different first page.

7. For the first page header, print Prepared by followed by your name in the right section. For every other page, print Filename: followed by the filename in the left section and the date in the right section. (Hint: Use the buttons in the Header dialog box to insert the filename and date.)

8. For every footer, including the first page, print Page followed by the page number and then of followed by the total number of pages in the printout in the center section.

9. Preview the printout to verify that the company name and description appear on every page of the Menu Items worksheet printout and that the menu category and menu item name appear on both pages with the Units Sold table. If you are instructed to print, print the entire workbook in portrait orientation.

14. 14

Save the workbook, and then close it.

Apply: Case Problem 1

Data File needed for this Case Problem: Green.xlsx

Green Clean Homes Sean Patel is developing a business plan for Green Clean Homes, a new professional home cleaning service in Toledo, Ohio. As part of his business plan, Sean needs to predict the company’s annual income and expenses. You will help him finalize and format the Excel workbook containing the projected income statement. Complete the following:

1. 1

Open the Green workbook located in the Excel2 > Case1 folder, and then save the workbook as Green Clean in the location specified by your instructor.

2. 2

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

3. 3

Display the date in cell B4 in the Long Date format and left-aligned.

4. 4

Change the theme of the workbook to Facet.

5. 5

Make the following formatting changes to the Documentation sheet:

1. Merge and center cells A1 and B1.

2. Apply the Accent2 cell style to the merged cell A1 and to the range A3:A5.

3. In cell A1, set the font size to 22 points and bold the text. Italicize the word “Clean” in the company name.

4. Add borders around each cell in the range A3:B5. Top-align the text in the range A3:B5.

5. Change the font color of the text in the range B3:B5 to Dark Green, Accent 2.

6. 6

In the Income Statement worksheet, merge and center the range A1:C1, and then apply the Accent2 cell style to the merged cell. Change the font size to 24 points and the text style to bold. Italicize the word “Clean” within the company name.

7. 7

Make the following changes to the Income Statement worksheet:

1. Format the range A3:C3 with the Heading 1 cell style.

2. Format the range A4:C4,A9:C9 with the 40% - Accent1 cell style.

3. Format cell B5 in the Accounting style with no decimal places.

4. Format cell B6 and the range B10:B17 in the Comma style with no decimal places.

8. 8

Add the following calculations to the workbook:

1. In cell C7, calculate the gross profit, which is equal to the gross sales minus the cost of sales.

2. In cell C18, calculate the company’s total operating expenses, which is equal to the sum of the values in the range B10:B17. Format the value in the Accounting format with no decimal places.

3. In cell C20, calculate the company’s operating profit, which is equal to its gross profit minus its total operating expenses.

4. In cell C21, calculate the company’s incomes taxes by multiplying its total operating profit by the corporate tax rate (cell G25). Format the value in the Accounting format with no decimal places.

5. In cell C22, calculate the company’s net profit, which is equal to the total operating profit minus the income taxes.

9. 9

Finalize the formatting of the Projected Income statement by adding the following:

1. Add a bottom border to the ranges A6:C6, A17:C17, and A20:C20. Add a single top border and a double bottom border to the range A22:C22.

2. Indent the expenses categories in the range A10:A17 twice.

10. 10

Format the Financial Assumptions section as follows:

1. Add borders around all of the cells in the range E4:G25.

2. Format the range E3:G3 with the Heading 1 cell style.

3. Merge the cells in the ranges E4:E7, E9:E13, E14:E15, E16:E18, and E20:E22.

4. Top-align and left-align the range E4:E25.

5. Change the fill color of the range E4:F25 to Green, Accent 1, Lighter 60%.

11. 11

Use conditional formatting to highlight the net profit (cell C22) if its value is less than $50,000 with a light red fill with dark red text.

12. 12

Change the value in cell G9 from 4 to 5. Observe the impact that hiring another cleaner has on the projected net profit for the company in cell C22.

13. 13

Format the printed version of the Income Statement worksheet as follows:

1. Add a manual page break between columns D and E.

2. For the first page, add a header that prints Prepared by followed by your name in the left section of the header and the current date in the right section of the header. Do not display header text on any other page.

3. For every page, add a footer that prints the workbook filename in the left section, Page followed by the page number in the center section, and the worksheet name in the right section.

4. Set the margins to 1 inch on all four sides of the printout, and center the contents of the worksheet horizontally within the printed page.

14. 14

If you are instructed to print, print the entire contents of the workbook in portrait orientation.

15. 15

Save and close the workbook.

Apply: Case Problem 1
Data File needed for this Case Problem: Green.xlsx

Green Clean Homes Sean Patel is developing a business plan for Green Clean Homes, a new professional home cleaning service in Toledo, Ohio. As part of his business plan, Sean needs to predict the company’s annual income and expenses. You will help him finalize and format the Excel workbook containing the projected income statement. Complete the following:

1. 1

Open the Green workbook located in the Excel2 > Case1 folder, and then save the workbook as Green Clean in the location specified by your instructor.

2. 2

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

3. 3

Display the date in cell B4 in the Long Date format and left-aligned.

4. 4

Change the theme of the workbook to Facet.

5. 5

Make the following formatting changes to the Documentation sheet:

1. Merge and center cells A1 and B1.

2. Apply the Accent2 cell style to the merged cell A1 and to the range A3:A5.

3. In cell A1, set the font size to 22 points and bold the text. Italicize the word “Clean” in the company name.

4. Add borders around each cell in the range A3:B5. Top-align the text in the range A3:B5.

5. Change the font color of the text in the range B3:B5 to Dark Green, Accent 2.

6. 6

In the Income Statement worksheet, merge and center the range A1:C1, and then apply the Accent2 cell style to the merged cell. Change the font size to 24 points and the text style to bold. Italicize the word “Clean” within the company name.

7. 7

Make the following changes to the Income Statement worksheet:

1. Format the range A3:C3 with the Heading 1 cell style.

2. Format the range A4:C4,A9:C9 with the 40% - Accent1 cell style.

3. Format cell B5 in the Accounting style with no decimal places.

4. Format cell B6 and the range B10:B17 in the Comma style with no decimal places.

8. 8

Add the following calculations to the workbook:

1. In cell C7, calculate the gross profit, which is equal to the gross sales minus the cost of sales.

2. In cell C18, calculate the company’s total operating expenses, which is equal to the sum of the values in the range B10:B17. Format the value in the Accounting format with no decimal places.

3. In cell C20, calculate the company’s operating profit, which is equal to its gross profit minus its total operating expenses.

4. In cell C21, calculate the company’s incomes taxes by multiplying its total operating profit by the corporate tax rate (cell G25). Format the value in the Accounting format with no decimal places.

5. In cell C22, calculate the company’s net profit, which is equal to the total operating profit minus the income taxes.

9. 9

Finalize the formatting of the Projected Income statement by adding the following:

1. Add a bottom border to the ranges A6:C6, A17:C17, and A20:C20. Add a single top border and a double bottom border to the range A22:C22.

2. Indent the expenses categories in the range A10:A17 twice.

10. 10

Format the Financial Assumptions section as follows:

1. Add borders around all of the cells in the range E4:G25.

2. Format the range E3:G3 with the Heading 1 cell style.

3. Merge the cells in the ranges E4:E7, E9:E13, E14:E15, E16:E18, and E20:E22.

4. Top-align and left-align the range E4:E25.

5. Change the fill color of the range E4:F25 to Green, Accent 1, Lighter 60%.

11. 11

Use conditional formatting to highlight the net profit (cell C22) if its value is less than $50,000 with a light red fill with dark red text.

12. 12

Change the value in cell G9 from 4 to 5. Observe the impact that hiring another cleaner has on the projected net profit for the company in cell C22.

13. 13

Format the printed version of the Income Statement worksheet as follows:

1. Add a manual page break between columns D and E.

2. For the first page, add a header that prints Prepared by followed by your name in the left section of the header and the current date in the right section of the header. Do not display header text on any other page.

3. For every page, add a footer that prints the workbook filename in the left section, Page followed by the page number in the center section, and the worksheet name in the right section.

4. Set the margins to 1 inch on all four sides of the printout, and center the contents of the worksheet horizontally within the printed page.

14. 14

If you are instructed to print, print the entire contents of the workbook in portrait orientation.

15. 15

Save and close the workbook.

Challenge: Case Problem 3
Data File needed for this Case Problem: Wait.xlsx

YuriTech Kayla Schwartz is the customer service manager at YuriTech, an electronics and computer firm located in Scottsdale, Arizona. Kayla is analyzing the calling records for technical support calls to YuriTech to determine which times are understaffed, resulting in unacceptable wait times. She has compiled several months of data and calculated the average wait times in one-hour intervals for each day of the week. You will format Kayla’s workbook to make it easier to determine when YuriTech should hire more staff to assist with customer support requests. Complete the following:

1. 1

Open the Wait workbook located in the Excel2 > Case3 folder, and then save the workbook as Wait Times in the location specified by your instructor.

2. 2

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

3. 3

Apply the Ion theme to the workbook.

4. 4

Format the Documentation sheet as follows:

1. Format the title in cell A1 using a 36-point Impact font with the Purple, Accent 6 font color.

2. Format the range A3:A5 with the Accent6 cell style.

3. Add a border around the cells in the range A3:B5. Wrap the text within each cell, and top-align the cell text.

5. 5

Copy the format you used in cell A1 of the Documentation sheet, and paste it to cell A1 of the Wait Times worksheet.

6. 6

Format the text in cell A2 with 14-point bold font and the Purple, Accent6 font color.

7. 7

In the range A14:H39, format the average customer wait times for each hour and day of the week data as follows:

1. Merge and center the range A14:H14, and apply the Title cell style to the merged contents.

2. Change the number format of the data in the range B16:H39 to show one decimal place.

3. Format the column and row labels in the range A15:H15,A16:A39 with the Accent6 cell style. Center the column headings in the range B15:H15.

8. 8

In cell B5, enter the value 22 as an excellent wait time. In cell B6, enter 28 as a good wait time. In cell B7, enter 47 as an acceptable wait time. In cell B8, enter 69 as a poor wait time. In cell B9, enter 84 as a very poor wait time. In cell B10, enter 90 as an unacceptable wait time.

9. 9

In the range A4:C10, apply the following formats to the wait time goals:

1. Merge and center the range A4:C4, and apply the Accent6 cell style to the merged cells.

2. Add borders around the cells in the range A4:C10.

10. 10

In cell E4, enter the label Average Wait Time (All Days). In cell E7, enter the label Average Wait Time (Weekdays). In cell E10, enter the label Average Wait Time (Weekends).

11. 11

Merge and center the range E4:F6, wrap the text in the merged cell, center the cell content both horizontally and vertically, and then apply the Accent6 cell style to the merged cell.

12. 12

Copy the format from the merged cell E4:F6 to cells E7 and E10.

13. 13

In cell G4, enter a formula to calculate the average of the wait times in the range B16:H39. In cell G7, enter a formula to calculate the average weekday wait times in the range C16:G39. In cell G10, calculate the average weekend rate times in the range B16:B39,H16:H39.

14. 14

Merge and center the ranges G4:G6, G7:G9, and G10:G12, and then center the calculated averages vertically within each merged cell.

15. 15

Add borders around the cells in the range E4:G12.

16. 16

Change the fill color of the range A5:C5 to a medium green, the fill color of the range A6:C6 to a light green, the fill color of the range A7:C7 to a light gold, the fill color of the range A8:C8 to a light red, and the fill color of the range A9:C9 to a medium red. Format the range A10:C10 with white text on a black background.

17. 17

Use conditional formatting to highlight cells with custom formats as follows:

1. Select the range G4:G12,B16:H39. Use conditional formatting to highlight cells with values less than 22 with a custom format that matches the fill color used in the range A5:C5.

2. Use conditional formatting to highlight cells with values greater than 90 in the range G4:G12,B16:H39 with a custom format of a white font on a black fill.

3. Use conditional formatting to highlight cells with values between 22 and 34 in the range G4:G12,B16:H39 with a custom format that matches the fill color used in the range A6:C6.

4. Use conditional formatting to highlight cells with values between 34 and 60 in the range G4:G12,B16:H39 with a light gold fill color that matches the cells in the range A7:C7.

5. Use conditional formatting to highlight cells with values between 60 and 78 in the range G4:G12,B16:H39 with light red, matching the fill color of the cells in the range A8:C8.

6. Use conditional formatting to highlight cells with values between 78 and 90 in the range G4:G12,B16:H39 with medium red, matching the fill color of the cells in the range A9:C9.

18. 18

In cell A41, enter the label Notes and then format it with the Title cell style.

19. 19

Merge the range A42:H50. Top- and left-align the contents of the cell. Turn on text wrapping within the merged cell. Add a thick outside border to the merged cell.

20. 20

Within the merged cell in the range A42:H50, summarize your conclusions about the wait times. Answer whether the wait times are within acceptable limits on average for the entire week, on weekdays, and on weekends. Also indicate whether there are times during the week that customers are experience very poor to unacceptable delays.

21. 21

Format the printed version of the Wait Times worksheet as follows:

1. Scale the sheet so that it fits on a single page in portrait orientation.

2. Center the sheet on the page horizontally and vertically.

3. Add the header Prepared by followed by your name in the right section.

4. Add a footer that prints the filename in the left section, the worksheet name in the center section, and the date in the right section.

22. 22

If you are instructed to print, print the entire contents of the workbook.

23. 23

Save and close the workbook.

Data File needed for this Case Problem: Pandaisia.xlsx

Pandaisia Chocolates Anne Ambrose is the owner and head chocolatier of Pandaisia Chocolates, a chocolate shop located in Essex, Vermont. Anne has asked you to create an Excel workbook in which she can enter customer orders. She wants the workbook to be easy to use and read. The final design of the order form is up to you. One possible solution is shown in Figure 2-47 .

Figure 2-47Pandaisia Chocolates order form

Enlarge Image

Complete the following:

1. 1

Open the Pandaisia workbook located in the Excel2 > Case3 folder, and then save the workbook as Pandaisia Order in the location specified by your instructor.

2. 2

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

3. 3

Insert a worksheet named Order Form after the Documentation worksheet.

4. 4

Enter the following information in the order form:

· The title and address of Pandaisia Chocolates

· The order date, order ID, and purchase order ID

· The date, sales representative, and account number for the order

· The billing address of the order

· The shipping address of the order

· A table listing every item ordered including the item’s product ID, description, quantity ordered, price, and total charge for the item(s)

· A comment box where Anne can insert additional information about the order

5. 5

Include formulas in the order form to do the following:

5. For each item ordered, calculate the cost of the item(s), which is equal to the quantity multiplied by the price.

5. Calculate the subtotal of the costs for every item ordered by the customer.

5. Calculate the sales tax for the order, which is equal to 5.2 percent times the subtotal value.

5. Calculate the total cost of the order, which is equal to the subtotal plus the sale tax.

1. 6

Format the order form by doing the following:

6. Apply a different built-in Excel theme.

6. Change the font colors and fill colors.

6. Format a text string within a cell.

6. Align content within cells.

6. Format dates with the Long Date format.

6. Apply the Percent, Accounting, and Currency formats as appropriate.

6. Add borders around cells and ranges.

6. Merge a range into a single cell.

1. 7

Pandaisia Chocolates includes a free complimentary truffle sample for every order over $100. Use conditional formatting to highlight the total charge in bold colored font when it is greater than $100.

1. 8

Test your order form by entering the data shown in Figure 2-47 . Confirm that the charge on your order matches that shown in the figure.

1. 9

Set up the print version of the order form so that it prints in portrait orientation on a single sheet. Add a header and/or footer that includes your name, the date, and the name of the workbook.

1. 10

If you are instructed to print, print the entire contents of the workbook.

1. 11

Save and close the workbook.