project 10 – a second excel workbook | IT 119 | Northern Virginia Community College

Project 10 is exercise of creating and modifying an EXCEL worksheet. Data from a file is formatted for the project document. Formulas are entered to compute values instructed by the project.

The following is the step by step instructions how to complete Project 10

Note: Workbooks submitted for Project 10  must include formulas for various computations required as part of the solution

1. Open the Excel workbook titled e10_PCPT_Data that was      downloaded from Canvus / Modules/ Project 10. Save the workbook as 10_PCPT_Firstname_Lastname      (replace Firstname_Lastname with your first and last name).
2. Rename the Sheet1 tab Taste of Golden Grove and apply      Blue, Accent 1, Darker 50%. Rename the Sheet2 tab Pot-o-Golden Grove and apply      Red, Accent 2, Darker 25%. Rename the Sheet3 tab 5K Run and apply Orange,      Accent 6, Darker 25%.
3. Select all three worksheets and enter the date 12/31/2007      in cell A3. Format the cell so that the year displays as two digits.
4. Clear the contents and formatting of cell B5:D5 on      the Pot-o-Golden Grove and the 5K Run worksheets. Copy B5:D5 from the      Taste of Golden Grove worksheet to B5:D5 on the Pot-o-Golden Grove and 5K      Run worksheets.
5. In cell D6 on each of the worksheets create a formula      that results in the revenue for Quarter 1. Copy the formula to D7:D9 on      each worksheet.
6. Use a function in cell B10 on each of the worksheets      to total the donations. Copy the function to cells C10:D10 on each      worksheet.
7. Apply the Comma style to cells B7:D9 on each      worksheet. Apply Accounting Number Format to cells B6:D6 and B10:D10 on      each worksheet.
8. Insert a new worksheet. Title it Summary and      apply Olive Green, Accent 3, Darker 50%. Move the Summary worksheet to the      left of the Taste of Golden Grove worksheet.
9. Copy A1:A3 from the Taste of Golden Grove worksheet      to cells A1:A3 on the Summary worksheet. Copy A6:A10 from the Taste of      Golden Grove worksheet to cells A6:A10 on the Summary worksheet. Keep the      source column widths. Merge and Center A1:A3 across columns A:E.
10. Enter the following data:

Cell.                                                          Data

B5.                                             Taste of Golden Grove

C5.                                               Pot-o-Golden Grove

D5                                                Golden Mile 5K Run

E5.                                                          Total

1. Apply the following format to cells B5:E5: Arial, 10      point, Bold, Italic, word wrap, middle align, and center. Adjust the width      of columns B:E to 14.
2. In cell B6:D6 create formulas that result in the      total revenue for Quarter 1 for each of the events. Copy the formulas to      B7:D9.
3. Use a function in cells B10:D10 and E6:E10 to display      the totals.
4. In cell A12 type Golden Grove 2007 Population. In      cell C12 Type 450000.      Format C12 with Comma style, no decimals.
5. In cell A13 Type Projected 2010 Population. In cell      C13 type 645000.      Format C13 with Comma style, no decimals.
6. In cell A14 type Percentage Increase. Create a      formula in cell C14 that results in the percentage increase in population      from 2007 to 2010. Format cell C14 with the Percentage style.
7. Copy A1:A3 to A16:A18. Replace the date in cell A18      with the word Projected.      Copy A16:A18 to A30:A32.
8. In cells A20 and A34, type Projected giving increase  Merge and Center both cells across      columns A:B.
9. Type 10%      in cell C20 and 8%      in cell C34. Left align the contents of C20 and C34.
10. Fill A20, C20, A34, and C34 with Olive Green, Accent      3, Lighter 60%.
11. Copy B5:E5 to B22:E22 and B37:E37.
12. Copy A6:A10 to A23:A27 and A38:A42. In cell B23      create a formula that results in the projected revenue for the Taste of      Golden Grove with the stated 10% increase over the 2007 total. Copy the      formula to B24:B26.
13. Create formulas in cells C23 and D23 that result in      the projected revenue for the Pot-o-Golden Grove and the Golden Mile 5K      Run. Copy the formulas to C24:D26.
14. Use a function to display the totals in B27:D27 and      E23:E27.
15. Format B23:E23 and B27:E27 with Accounting Number      Format. Format B24:E26 with Comma style. 
16. Use what-if analysis to determine the projections      with an 8% increase. Display these figures in cells B38:E42. Format      B39:E41 with Comma style. Format B38:E38 and B42:E42 with Accounting      Number Format.
17. Create a 2D Line with Markets chart to display the      10% increase for all the quarters. Apply Style 2 and Layout 3. Title the      chart 10%      Giving Increase. Display the chart in its own worksheet titled 10% Increase.      Move the chart to the right of the Summary worksheet.
18. Create a footer for the worksheets and the chart with      the following information:
         Left side: file name; Right side: current date.
19. Change the margins on each worksheet and the chart to Narrow. Display the Summary worksheet and move A37:E42 to A36:E41.

20. Submit the file 10_PCPT_Firstname_Lastname for      grading in the BlackBoard drop box for Project 1

The following workbook must be downloaded to the computer or a USB so it can be accessed and copied as part of the document creation process.

( I attached the file for that)

Preparing your Project 10 document for submission:

1.     Create the EXCEL workbook as instructed by the Project 10 requirements.

2.      Save the workbook created and submit via the CANVAS Project 10   submission.

3.      Save the workbook as a .xls or .xlsx file

4.     Attach the file to your submission for credit.

The following document are examples of solutions for Project 10.

(Also attached it)