SC_EX19_EOM2-2 (solution)

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Starpex Communications

FORMAT WORKSHEETS

GETTING STARTED

  • Open the file SC_EX19_EOM2-2_FirstLastName_1.xlsx, available for download from the SAM website.
  • Save the file as SC_EX19_EOM2-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • With the file SC_EX19_EOM2-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

    PROJECT STEPS

  1. Rebecca Fox is the budget director for Starpex Communications, a company based in Dover, Delaware, that provides broadband and cellular service throughout the country. Rebecca has started to create an Excel workbook that provides a snapshot of the annual budget for a satellite office in Tacoma Park, Maryland, and lists charitable contribution suggestions from employees. She asks you to update the workbook to make it easier to use.
    Go to the Annual Budget worksheet. In the range C5:C17, use Conditional Formatting Highlight Cells Rules to format duplicate values in Light Red Fill with Dark Red Text. Delete the row containing the first duplicate value.
  2. In cell F5, insert a formula without using a function that subtracts the actual Advertising amount (cell D5) from the budgeted Advertising amount (cell E5). Fill the range F6:F16 with the formula in cell F5.
  3. Clear the conditional formatting from the range E5:E16.
  4. Apply the Percent Style number format to the range G5:G17 and display one place after the decimal point.
  5. In the range F5:F16, create a Conditional Formatting rule that uses Gradient Fill Blue Data Bars to compare values.
  6. Unmerge cell B17, then check the spelling in the worksheet, and correct any spelling errors.
  7. Go to the Charitable worksheet. Fill the merged range B2:G2 with Blue, Accent 2, Lighter 80%. Hide column G.
  8. Apply the Currency number format to the range C5:C13 using a dollar sign ($) and 0 decimal places.
  9. In cell C15, enter a formula using the SUM function that totals the donations and sponsorships (the range C5:C13).
  10. In cell C16, enter a formula using the COUNT function that counts the number of donations and sponsorships (the range C5:C13).
  11. In cell C18, enter a formula without using a function that first subtracts the projected donation amount (cell C17) from the total donation amount (cell C15), and then divides the result by the projected donation amount.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.


 

 

 

 

 

 

 

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Starpex Communications

FORMAT WORKSHEETS

GETTING STARTED

  • Open the file SC_EX19_EOM2-2_FirstLastName_1.xlsx, available for download from the SAM website.
  • Save the file as SC_EX19_EOM2-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • With the file SC_EX19_EOM2-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

    PROJECT STEPS

  1. Rebecca Fox is the budget director for Starpex Communications, a company based in Dover, Delaware, that provides broadband and cellular service throughout the country. Rebecca has started to create an Excel workbook that provides a snapshot of the annual budget for a satellite office in Tacoma Park, Maryland, and lists charitable contribution suggestions from employees. She asks you to update the workbook to make it easier to use.
    Go to the Annual Budget worksheet. In the range C5:C17, use Conditional Formatting Highlight Cells Rules to format duplicate values in Light Red Fill with Dark Red Text. Delete the row containing the first duplicate value.
  2. In cell F5, insert a formula without using a function that subtracts the actual Advertising amount (cell D5) from the budgeted Advertising amount (cell E5). Fill the range F6:F16 with the formula in cell F5.
  3. Clear the conditional formatting from the range E5:E16.
  4. Apply the Percent Style number format to the range G5:G17 and display one place after the decimal point.
  5. In the range F5:F16, create a Conditional Formatting rule that uses Gradient Fill Blue Data Bars to compare values.
  6. Unmerge cell B17, then check the spelling in the worksheet, and correct any spelling errors.
  7. Go to the Charitable worksheet. Fill the merged range B2:G2 with Blue, Accent 2, Lighter 80%. Hide column G.
  8. Apply the Currency number format to the range C5:C13 using a dollar sign ($) and 0 decimal places.
  9. In cell C15, enter a formula using the SUM function that totals the donations and sponsorships (the range C5:C13).
  10. In cell C16, enter a formula using the COUNT function that counts the number of donations and sponsorships (the range C5:C13).
  11. In cell C18, enter a formula without using a function that first subtracts the projected donation amount (cell C17) from the total donation amount (cell C15), and then divides the result by the projected donation amount.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.


 

 

 

 

 

 

 

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Starpex Communications

FORMAT WORKSHEETS

GETTING STARTED

  • Open the file SC_EX19_EOM2-2_FirstLastName_1.xlsx, available for download from the SAM website.
  • Save the file as SC_EX19_EOM2-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • With the file SC_EX19_EOM2-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

    PROJECT STEPS

  1. Rebecca Fox is the budget director for Starpex Communications, a company based in Dover, Delaware, that provides broadband and cellular service throughout the country. Rebecca has started to create an Excel workbook that provides a snapshot of the annual budget for a satellite office in Tacoma Park, Maryland, and lists charitable contribution suggestions from employees. She asks you to update the workbook to make it easier to use.
    Go to the Annual Budget worksheet. In the range C5:C17, use Conditional Formatting Highlight Cells Rules to format duplicate values in Light Red Fill with Dark Red Text. Delete the row containing the first duplicate value.
  2. In cell F5, insert a formula without using a function that subtracts the actual Advertising amount (cell D5) from the budgeted Advertising amount (cell E5). Fill the range F6:F16 with the formula in cell F5.
  3. Clear the conditional formatting from the range E5:E16.
  4. Apply the Percent Style number format to the range G5:G17 and display one place after the decimal point.
  5. In the range F5:F16, create a Conditional Formatting rule that uses Gradient Fill Blue Data Bars to compare values.
  6. Unmerge cell B17, then check the spelling in the worksheet, and correct any spelling errors.
  7. Go to the Charitable worksheet. Fill the merged range B2:G2 with Blue, Accent 2, Lighter 80%. Hide column G.
  8. Apply the Currency number format to the range C5:C13 using a dollar sign ($) and 0 decimal places.
  9. In cell C15, enter a formula using the SUM function that totals the donations and sponsorships (the range C5:C13).
  10. In cell C16, enter a formula using the COUNT function that counts the number of donations and sponsorships (the range C5:C13).
  11. In cell C18, enter a formula without using a function that first subtracts the projected donation amount (cell C17) from the total donation amount (cell C15), and then divides the result by the projected donation amount.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.


 

 

 

 

 

 

 

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Shelly Cashman Excel 2019 | Module 2: End of Module Project 2

Starpex Communications

Starpex Communications

FORMAT WORKSHEETS

FORMAT WORKSHEETS

GETTING STARTED

GETTING STARTED
  • Open the file SC_EX19_EOM2-2_FirstLastName_1.xlsx, available for download from the SAM website.
  • Save the file as SC_EX19_EOM2-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • With the file SC_EX19_EOM2-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
  • Open the file SC_EX19_EOM2-2_FirstLastName_1.xlsx, available for download from the SAM website.
  • Open the file SC_EX19_EOM2-2_FirstLastName_1.xlsx, available for download from the SAM website.SC_EX19_EOM2-2_FirstLastName_1.xlsxFirstLastName
  • Save the file as SC_EX19_EOM2-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • Save the file as SC_EX19_EOM2-2_FirstLastName_2.xlsx by changing the “1” to a “2”.SC_EX19_EOM2-2_FirstLastName_2.xlsxFirstLastName
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically..xlsx
  • With the file SC_EX19_EOM2-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • With the file SC_EX19_EOM2-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.SC_EX19_EOM2-2_FirstLastName_2.xlsxFirstLastName
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

        PROJECT STEPS

        PROJECT STEPS   
    1. Rebecca Fox is the budget director for Starpex Communications, a company based in Dover, Delaware, that provides broadband and cellular service throughout the country. Rebecca has started to create an Excel workbook that provides a snapshot of the annual budget for a satellite office in Tacoma Park, Maryland, and lists charitable contribution suggestions from employees. She asks you to update the workbook to make it easier to use.
      Go to the Annual Budget worksheet. In the range C5:C17, use Conditional Formatting Highlight Cells Rules to format duplicate values in Light Red Fill with Dark Red Text. Delete the row containing the first duplicate value.
    2. In cell F5, insert a formula without using a function that subtracts the actual Advertising amount (cell D5) from the budgeted Advertising amount (cell E5). Fill the range F6:F16 with the formula in cell F5.
    3. Clear the conditional formatting from the range E5:E16.
    4. Apply the Percent Style number format to the range G5:G17 and display one place after the decimal point.
    5. In the range F5:F16, create a Conditional Formatting rule that uses Gradient Fill Blue Data Bars to compare values.
    6. Unmerge cell B17, then check the spelling in the worksheet, and correct any spelling errors.
    7. Go to the Charitable worksheet. Fill the merged range B2:G2 with Blue, Accent 2, Lighter 80%. Hide column G.
    8. Apply the Currency number format to the range C5:C13 using a dollar sign ($) and 0 decimal places.
    9. In cell C15, enter a formula using the SUM function that totals the donations and sponsorships (the range C5:C13).
    10. In cell C16, enter a formula using the COUNT function that counts the number of donations and sponsorships (the range C5:C13).
    11. In cell C18, enter a formula without using a function that first subtracts the projected donation amount (cell C17) from the total donation amount (cell C15), and then divides the result by the projected donation amount.
  • Rebecca Fox is the budget director for Starpex Communications, a company based in Dover, Delaware, that provides broadband and cellular service throughout the country. Rebecca has started to create an Excel workbook that provides a snapshot of the annual budget for a satellite office in Tacoma Park, Maryland, and lists charitable contribution suggestions from employees. She asks you to update the workbook to make it easier to use.
    Go to the Annual Budget worksheet. In the range C5:C17, use Conditional Formatting Highlight Cells Rules to format duplicate values in Light Red Fill with Dark Red Text. Delete the row containing the first duplicate value.
  • Rebecca Fox is the budget director for Starpex Communications, a company based in Dover, Delaware, that provides broadband and cellular service throughout the country. Rebecca has started to create an Excel workbook that provides a snapshot of the annual budget for a satellite office in Tacoma Park, Maryland, and lists charitable contribution suggestions from employees. She asks you to update the workbook to make it easier to use.
    Go to the Annual Budget worksheet. In the range C5:C17, use Conditional Formatting Highlight Cells Rules to format duplicate values in Light Red Fill with Dark Red Text. Delete the row containing the first duplicate value.Annual Budget
  • In cell F5, insert a formula without using a function that subtracts the actual Advertising amount (cell D5) from the budgeted Advertising amount (cell E5). Fill the range F6:F16 with the formula in cell F5.
  • In cell F5, insert a formula without using a function that subtracts the actual Advertising amount (cell D5) from the budgeted Advertising amount (cell E5). Fill the range F6:F16 with the formula in cell F5.D5E5
  • Clear the conditional formatting from the range E5:E16.
  • Clear the conditional formatting from the range E5:E16.
  • Apply the Percent Style number format to the range G5:G17 and display one place after the decimal point.
  • Apply the Percent Style number format to the range G5:G17 and display one place after the decimal point.
  • In the range F5:F16, create a Conditional Formatting rule that uses Gradient Fill Blue Data Bars to compare values.
  • In the range F5:F16, create a Conditional Formatting rule that uses Gradient Fill Blue Data Bars to compare values.
  • Unmerge cell B17, then check the spelling in the worksheet, and correct any spelling errors.
  • Unmerge cell B17, then check the spelling in the worksheet, and correct any spelling errors.
  • Go to the Charitable worksheet. Fill the merged range B2:G2 with Blue, Accent 2, Lighter 80%. Hide column G.
  • Go to the Charitable worksheet. Fill the merged range B2:G2 with Blue, Accent 2, Lighter 80%. Hide column G.Charitable
  • Apply the Currency number format to the range C5:C13 using a dollar sign ($) and 0 decimal places.
  • Apply the Currency number format to the range C5:C13 using a dollar sign ($) and 0 decimal places.
  • In cell C15, enter a formula using the SUM function that totals the donations and sponsorships (the range C5:C13).
  • In cell C15, enter a formula using the SUM function that totals the donations and sponsorships (the range C5:C13).SUMC5:C13
  • In cell C16, enter a formula using the COUNT function that counts the number of donations and sponsorships (the range C5:C13).
  • In cell C16, enter a formula using the COUNT function that counts the number of donations and sponsorships (the range C5:C13).COUNTC5:C13
  • In cell C18, enter a formula without using a function that first subtracts the projected donation amount (cell C17) from the total donation amount (cell C15), and then divides the result by the projected donation amount.
  • In cell C18, enter a formula without using a function that first subtracts the projected donation amount (cell C17) from the total donation amount (cell C15), and then divides the result by the projected donation amount.C17C15

    Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

    Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.




     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Answers