Scenario:
Youare an intern at a Children’s Science Museum that has several locations in Indiana. A prior intern started tocreate and update Excel spreadsheets to monitor and analyze the business. You will be working with these spreadsheets to complete the tasks below.
Project Preparations
Download the ChildrensMuseum_Data.xlsx(opens in a new tab)file to use for the project.
Save the file as FinalProject_ChildrenMuseum_MEID.xlsx. Be sure to replace MEID with your actual MEID.
Donors Worksheet
On the Donors worksheet, format theheadings in cells A4:E4 as Bold, 12 point, and Centered. Autofit column width if necessary.
In E5, calculate the totaldonation amountfor each donor using Amount and Frequency.
Copy the formula from E5 down to E52.
FormatC5:C52andE5:E52asCurrency.
In cell E53,calculate the Total donations andformatthe total as Currency.
In cell D53, add Total as a label.
Apply borders of your choice toA5:E52.
LookUps Worksheet
On theLookUpsworksheet, name the cell range A4:C11Tickets.
Workbook Maintenance
Group the worksheets fromSouthbend to AllLocations.
With the sheets grouped, on the Southbend worksheet,enter a formula in cell D5 that uses a VLOOKUP to returnthe ticket price from the Tickets named range.
Copy the formula down to D11.
In cell E5,enter a formula to calculate the Total PremiumPlus tickets using the Price and 2020 Quantity.
Copy the formula down toE11.
Ungrouptheworksheets.
Group the Evansville and AllLocations worksheets together.
Fill both the contents and formats from Evansville A1:E4to AllLocations.
Ungroupthe worksheets.
On theAllLocations worksheetin cell C5,usea 3-D cellreferenceto calculate the total 2020 Quantity from allfour locations.
Copy the formula down to C11.
In cell D12, typeTotaland set the cell style toTurquoise Accent3.
Right-align cell D12.
In E12,calculate thetotalfor all kinds of tickets.
NewWingLoan Worksheet
Calculate the number of loan payments, the monthly payment, the total amount to be paid, and the total interest to be paidfor a loan for a new wing to the museum.
Construct a loan amortization schedule incell rangeC12:G131
The museum must also track various exhibits and manage the construction of them.
Exhibits Worksheet
On the Exhibits worksheet in cellrangeH4:H12calculate the projected length of each projectin months.
Format cellrangeI4:I12withGreenfont colorandboldif the progress is at50%or more.
Rotating exhibits are not supposed to take more thantwomonths toconstruct. In cell J4,write an IF statement that will return "Contact Manager"if it is a rotatingexhibit that is projected to take longer thantwomonths.
Donations Worksheet
After the Donorsworksheet, add a new worksheetto analyze Donations. Name the new worksheet "Donations" and change the tab colorto light bluefor this worksheet.
Create aPivotChart andPivotTable ontheDonations worksheet.
Create a PivotChart and place it under the PivotTable.
For the PivotChart, add "Typeof Donors" to the horizontal axis.
Remove thefilter button and legend.
Change the Chart Title toTotal Donations.
Add a slicer forTypeinDonations.
Arrange the slicer under the chart.
Save the Workbook file, FinalProject_ChildrenMuseum_MEID.xlsx.
Macros
Save the file again, this time using the Excel Macro-Enabled Workbook format: FinalProject_ChildrenMuseum_MEID.xlsm.
Create a macro to clear the slicer's filter.
Insert a button on the Donations worksheet that will run the macro to clear the slicer's filter.
Place the button under the slicer.
Edit the text of the button to read "Clear Slicer".
Save the workbook and close Excel.
Assignment Submission
Submit the following two (2) files following the instructions in the lesson:
FinalProject_ChildrenMuseum_MEID.xlsx
FinalProject_ChildrenMuseum_MEID.xlsm