Excel – Assignment
Mod4-2- Excel Assignment
Basic Spreadsheet (15 points): Continuing with the class or family reunion, create a basic
spreadsheet outline the costs and potential income for this reunion. Include the following:
Labels to identify the values in the cells. For example, cost for the dinner, river cruise, tour of the
Warhol museum, name tags, production of updated contact information for all members, etc.
Estimated income…is anyone giving a donation to help with the costs or will it all be included in
the cost to attend? Will there be separate fees or will it be one cost for all regardless of not
attending some events?
Format it to look nice.
Use appropriate formulas and functions
Create a second sheet with a list of attendees, phone number, and whether they submitted their
payment for attendance.
Create a pie chart on a separate sheet showing the breakdown of the costs.
Set the spreadsheet to landscape and include page number of number of pages.
Save the file Mod4-2Reunion-YourlastName.
Multiple Sheet Workbook (5 points)
Use the Multiple (https://laroche.instructure.com/courses/3796/files/349054/download?wrap=1)
(https://laroche.instructure.com/courses/3796/files/349054/download?wrap=1) spreadsheet located in
CANVAS. Complete the following tasks:
Rename the first worksheet 2019 and the second one 2018. Delete the remaining worksheets.
Add a new worksheet and rename it 2020; Move it between 2019 and 2018.
Copy the labels from A1:A10 on the 2019 sheet to A1:A10 to the 2017 and 2020 sheets. Group
all the sheets. Enter January label in cell B3 and autofill to C3 and D3; type Total in cell E3.
In B6 calculate the difference between contribution and goals and then copy it to C6:D6. In B10
calculate the difference between budgeted for expenses and the actual expenses of Supplies and
Personnel and copy it to C10:D10. Sum the Total column. NOTE: There will be no values in the
second and third worksheets. What you will see is the formatting once the formatting is done
Center row 3 labels; currency format B10:E10; comma format B4:E9; adjust column widths as
needed. Change orientation to landscape. Place your name in the header, left section.
Save the file as Mod4-2Multiple-Yourname.
If Function (15 points)
Retrieve the file – BonusPay (https://laroche.instructure.com/courses/3796/files/349055/download?
wrap=1) (https://laroche.instructure.com/courses/3796/files/349055/download?wrap=1) from
In C4 create an IF function that determines if the sales representative gets a bonus this month.
Return a Yes for a bonus, No if no bonus. Bonuses are awarded for sales greater than 60,000.
Copy the function to the remaining cells in the column.
In D4 create an IF function that determines the dollar amount for a bonus. If Yes is in C4,
calculate the bonus % shown in G2 (HINT: Use absolute cell reference) of the sales from column
B4. Return a 0 if there is no bonus. Copy the formula to the remaining cells in column D.
Set to Landscape. Place your name in cell A13.
Create a new spreadsheet called formulas. Copy the worksheet to the new formula’s Expose the
formulas – fit it to one page, landscape.
Make a third worksheet and copy the first worksheet to it. Call it changed value. Change the
value in G2 to 5% and save the results as Mod4-2-Bonuspay-Yourname.
Charts (15 points)
Retrieve Park (https://laroche.instructure.com/courses/3796/files/349056/download?wrap=1)
(https://laroche.instructure.com/courses/3796/files/349056/download?wrap=1) from CANVAS and
name it Mod4-2-Park-Lastname (your last name).
Enter your name and today function in the documentation sheet.
On the Park Usage sheet, select the range A3:D15. Create a 3-D column chart that compares
values across categories and across series.
The chart title should be “2019 Usage Statistics”. The X axis title should be “Month”. The Depth
axis title should be “Sites”, and the Value axis (Y) title should be “Visitors”.
Place the chart legend at the bottom of the chart. The chart should be placed on a new chart
sheet named “Usage Chart”.
Change the rotation of the Chart to 24 degrees for Y-axis of the plot area. NOTE: If you didn’t
select the correct chart, the rotation parts may not work correctly.
Change the color of the walls and floors of the 3-D chart to grey (any variation). Display the chart
title in blue 18-point font.
Add a shape of your choice to the chart, with some appropriate text.
Format the shape to look nice.
Save the file. Place all the files in a folder called Assignment4-2-Yourlastname.
Place all the files in a folder and zip (compress) the folder. Submit it with the assignment link in
CANVAS – Mod4-2. (https://laroche.instructure.com/courses/3796/assignments/65594)