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
- 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).
- 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%.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Enter the following data:
B5. Taste of Golden Grove
C5. Pot-o-Golden Grove
D5 Golden Mile 5K Run
- 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.
- 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.
- Use a function in cells B10:D10 and E6:E10 to display the totals.
- In cell A12 type Golden Grove 2007 Population. In cell C12 Type 450000. Format C12 with Comma style, no decimals.
- In cell A13 Type Projected 2010 Population. In cell C13 type 645000. Format C13 with Comma style, no decimals.
- 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.
- Copy A1:A3 to A16:A18. Replace the date in cell A18 with the word Projected. Copy A16:A18 to A30:A32.
- In cells A20 and A34, type Projected giving increase Merge and Center both cells across columns A:B.
- Type 10% in cell C20 and 8% in cell C34. Left align the contents of C20 and C34.
- Fill A20, C20, A34, and C34 with Olive Green, Accent 3, Lighter 60%.
- Copy B5:E5 to B22:E22 and B37:E37.
- 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.
- 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.
- Use a function to display the totals in B27:D27 and E23:E27.
- Format B23:E23 and B27:E27 with Accounting Number Format. Format B24:E26 with Comma style.
- 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.
- 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.
- Create a footer for the worksheets and the chart with the following information:
Left side: file name; Right side: current date.
- 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)