Exp19_Excel_Ch01_CapAssessment_Training 

 Exp19 Excel Ch01 CapAssessment Training 

  

Exp19_Excel_Ch01_CapAssessment_Training

Project Description:

You manage a company that provides corporate training workshops. Your two types of clients are business people and college professors. You want to complete a spreadsheet that calculates gross revenue for upcoming training workshops. Finally, you will improve the appearance of the worksheet by applying font, alignment, and number formats.

     

Start   Excel. Download and open the file named Exp19_Excel_Ch01_CapAssessment_Training.xlsx. Grader has automatically added   your last name to the beginning of the filename.

 

Your organization provides a   discount to educators. However, the worksheet contains wording that needs to   be changed to Education.
 

  Use Find and Replace to find Teacher and   replace it with Education.

 

The worksheet contains spelling   errors that need to be corrected.
 

  Check the spelling of the worksheet and correct the errors.

 

You canceled the Charting Basics   workshop because only one person registered for it.
 

  Delete the row containing the Charting   Basics workshop.

 

The Excel Analytics &   Forecasting training course filled, and you opened another course.
 

  Insert a new row between the Big Data   Foundations and Data Mining   rows. Copy row 5 and paste it on the empty row. Change the dates to May 7, 2021 in the range C7:D7. Enter 11 in cell G7 and 10 in cell H7.

 

 

Assigning a code to each   workshop provides a reference number. You will use 05 to indicate the fifth   month (May) and 001 for the first workshop.
 

  Type 05-001 in cell A5. Use AutoFill to   complete the range A6:A10.

 

Instead of displaying dates such   as 5/3/21, you want to display dates as 5/3/2021.
 

  Format the dates in the range C5:D10 with Short Date format (or *3/14/2012   from the Type list).
 

Note, Mac users, select 3/14/2012   as the format.

 

The worksheet contains columns   for the start and end dates. It would be helpful to insert a column to   display the number of days for each workshop.
 

  Insert a column to the right of the End Date column. Type # of Days in cell E4.

 

 

Row 3 contains # of Attendees and Gross Revenue main headings. Centering   these headings over their respective subheadings will help clarify the   details.
 

  Merge and center the cells H3 and I3. Merge and center the range J3:L3.

 

 

Applying a cell style will   improve the appearance of the headings on row 4. In addition, wrapping and   centering the text in the heading cells will further improve the appearance   of the worksheet.
 
  For the range A4:L4, apply the Good cell style, wrap text, and center   horizontally.

 

 

You are ready to calculate the   number of days for the workshops.
 

  In cell E5, enter a formula to calculate the number of days for the first   workshop. Add 1 to the results to include the total number of days, including   the start and end dates. The results may display in date format. Copy the   formula to the range E6:E10.

 

 

The number of days results show   dates rather than values. Applying the Number format will solve this issue.
 

  Apply Number format with zero decimal places and center the results   horizontally in the range E5:E10.

 

 

The gross revenue is calculated   by type of customer. Column J is designed to display the gross revenue for   corporate customers.
 

  In cell J5, enter a formula to calculate the gross corporate revenue for the   first workshop by multiplying the cost per attendee by the number of   corporate attendees. Copy the formula to the range J6:J10.

 

 

Column K is designed to display   the gross revenue for education customers.
 

  In cell K5, enter a formula to calculate the gross education revenue by   subtracting the education discount rate from 1, multiplying the result by the cost per attendee   and multiplying by the number of education attendees. Copy the formula to the   range K6:K10.

 

 

Now that you calculated the   gross revenue for corporate and education customers, you can calculate the   total gross revenue for each workshop.
 

  In cell L5, enter a formula to calculate the total gross revenue by adding   the Corporate Gross Revenue and the Education Gross Revenue. Copy the formula   to the range L6:L10.

 

 

The discount rates look better   when displayed with percent symbols and centered because the rates are all   two digits.
 

  Format the range G5:G10 as Percent Style and center the data horizontally.

 

 

The monetary values in the Cost   per Attendee, Corporate, Education, and Gross Revenue columns would look   better with dollar signs and commas.
 

  Format the ranges F5:F10, J5:L10 with Accounting Number Format with zero   decimal places.

 

 

Change the width of columns G,   H, and I to 9.
 

 

To further improve how the   number of attendees values display below their headings, you can change the   alignment and indent the data.
 

  Right-align data in the range H5:I10 and increase the indent two times.

 

Borders around the # of   Attendees and Gross Revenue sections would help distinguish data and improve   readability.
 

  Apply Outside Borders to the range H4:I10. Apply Outside Borders to the range   J4:L10.

 

 

You want to prepare the   worksheet so that your manager can print it. Because the worksheet data   consumes several columns but only a few rows, it would look better in   landscape orientation.
 

  Select Landscape orientation.

 

 

A larger top margin but smaller   left and right margins will help balance the worksheet data on a printout.   Centering the data between the left and right margins further balances   workshop revenue data on the page.
 

  Set a 1.5-inch top margin, 0.5-inch left and right margins, and center the   worksheet horizontally on the page.

 

 

Even with the smaller left and   right margins, the data does not fit on one page. Therefore, you should   reduce the scaling so that it fits.
 

  Change the scaling to 90%.

 

 

Insert a footer with the text Exploring   Series on the   left side, the sheet name code in the center, and the file name code on the   right side.

 

The worksheet is named Sheet1,   which is not meaningful. You want to add a meaningful sheet name so that it   will display in the footer. In addition, the Sheet2 worksheet is empty and   should be deleted.
 

  Rename Sheet1 as May and then delete Sheet2.

 

Save and close Exp19_Excel_Ch01_CapAssessment_Training.xlsx.   Exit Excel. Submit the file as directed