Excel volume 1 grader project capstone

Chapters noted above.

ASSIGNMENTS Use these instructions for ALL your assignments. PLEASE ASK IF YOU NEED CLARIFICATION.

Submit the complete assignment to the assignment drop box in the Learning Hub www.learn.bcit.ca

Prior to submitting:

 All submitted assignments must be in good presentation order. Treat your work as if you are presenting to

a current or potential employer or client. Marks will be deducted from the overall assignments for

submissions not in a presentable format.

 Presentation format means it is suitable for distribution to an audience both on-screen and in print form.

Use print preview to show what your assignment will look like. Each previewed page must not have any

text, tables or charts cut off or orphaned. Orphan rows and columns must be fixed prior to submission.

 Ensure all assignments are placed in the order of the exercises stated and the sheets are labelled

accordingly. No additional sheets or files should be included. This will facilitate more timely feedback.

 Email of assignments will not be accepted. No exceptions. Only assignments submitted to the correct

Learning Hub drop box will be marked. No printing is required.

 Assignments are due as per the date in the drop box in the Learning Hub and on the course outline. All

assignments must be completed individually. Late assignments, if accepted, may only be considered for

marking with penalty unless otherwise previously agreed upon.

 Check the course outline for % associated with your assignments.

 Additional exercises from the textbook or instructor may be added to assignments as required for the needs

of the class. Additional work will be announced at the end of each session. Please ensure you are present

to note additional work assignments.

http://www.pearsonhighered.com/exploring
http://www.learn.bcit.ca/
2 ©Gerrard 2016 – amd Atha 2016/2017/2018/19 Assignment 1

Assignment 1A

1. Open e05b2games.xlsx and save it as Yourname_Assignment_1A.

2. Click on the Salary Data tab. Copy this worksheet three times. You will now have four worksheets with

the same data. Easiest way is to hold down the CTRL key and drag the sheet and release. This will copy

the worksheet.

3. Rename the second sheet to By Department and Title; the third sheet By Gender and the fourth My

Pivot. Change the tab colour of all four sheets to blue.

4. In the Salary Data worksheet, sort by Performance and subtotal the salary by Performance. 5. In the By Department and Title worksheet, sort by department and title and then calculate the average

salary by subtotalling by department and title. 6. In the Gender worksheet, do a count of the number of salaries paid by gender using a subtotal.

7. Using the data from the My Pivot worksheet create your own pivot table in the same worksheet to show

the salaries for each department by title and gender.

8. Copy the My Pivot worksheet twice. Rename one of the new worksheets as Pivot Chart and the second

one as Formatted Pivot.

9. In the Pivot Chart worksheet, create a pivot chart and then use the chart filters to limit the chart to the

Animation, Art, Game Design and Production departments by gender. Change the Y Axis to thousands.

The pivot chart works just like the ordinary charts with the exception of the filters.

3 ©Gerrard 2016 – amd Atha 2016/2017/2018/19 Assignment 1

10. In the Formatted Pivot worksheet, complete the following:

a. Change the report layout to Tabular form.

b. Change the style to Pivot Style Medium 15.

c. Change to summarize by Average Salary.

d. Insert a slicer for Department and select Animation.

11. Save the file.

Assignment 1B

1. Complete the Capstone Exercise at the end of Chapter 6 Page 451. The required file for Assignment

1B, e06c1Manufacturing, is included in the Chapter 6 folder in the content area in the Learning Hub.

2. Save your file as Yourname_Assignment 1B_C.xlsx.

There’s more………..flip over the page…..

4 ©Gerrard 2016 – amd Atha 2016/2017/2018/19 Assignment 1

Assignment 1C

1. Open Studio.xlsx. This is not the same as the one from the Chapter. Copy the Loan worksheet to your

Yourname_Assignment 1B_C workbook. Rename the worksheet as Assignment 1C.

2. Complete the worksheet by:

a. Entering the Beginning Balance in B13 by referencing the Input Area.

b. Entering the Monthly Payment in C13 by referencing the Input Area.

c. Calculating the Interest Paid, Principal Reduction and Ending Balance in row 13.

d. Calculating the Cumulative Interest and Cumulative Principle.

e. Auto filling the balance of the table.

3. Save the file to the Yourname_Assignment 1B_C workbook.

Ensure all required files are uploaded to the Learning Hub.

There are two files to be submitted:

Yourname_Assignment 1A and Yourname_Assignment 1B_C

Please ensure the files are named correctly.

Other files (i.e. jpg, png, docx) will not be reviewed/marked.