# Scenario:</o:p> 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...

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.

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

## Answer To : Scenario:</o:p> Youare an intern at a Children’s Science Museum that has several locations in...

Prince answered on May 16 2022
Southbend
Children's Science Museum
Donations
Ticket    TicketType    2020 Quantity    Price    Total
PremiumPlus    Yearly    2401    \$ 500.00    \$ 1,200,500
Grandparent    Yearly    1255    \$ 250.00    \$ 313,750
Family    Yearly    1291    \$ 200.00    \$ 258,200
Individual    Yearly    1525    \$ 165.00    \$ 251,6
25
Adult    1-Day    15954    \$ 25.75    \$ 410,816
Child    1-Day    7200    \$ 20.75    \$ 149,400
Senior    1-Day    3045    \$ 24.50    \$ 74,603
Bloomington
Children's Science Museum
2020 Ticket Sales
Ticket    TicketType    2020 Quantity    Price    Total
PremiumPlus    Yearly    2768    \$ 500.00    \$ 1,384,000
Grandparent    Yearly    1844    \$ 250.00    \$ 461,000
Family    Yearly    2914    \$ 200.00    \$ 582,800
Individual    Yearly    1834    \$ 165.00    \$ 302,610
Adult    1-Day    9312    \$ 25.75    \$ 239,784
Child    1-Day    8586    \$ 20.75    \$ 178,160
Senior    1-Day    8369    \$ 24.50    \$ 205,041
FortWayne
Children's Science Museum
2020 Ticket Sales
Ticket    TicketType    2020 Quantity    Price    Total
PremiumPlus    Yearly    1657    \$ 500.00    \$ 828,500
Grandparent    Yearly    1671    \$ 250.00    \$ 417,750
Family    Yearly    5407    \$ 200.00    \$ 1,081,400
Individual    Yearly    899    \$ 165.00    \$ 148,335
Adult    1-Day    20230    \$ 25.75    \$ 520,923
Child    1-Day    20362    \$ 20.75    \$ 422,512
Senior    1-Day    7463    \$ 24.50    \$ 182,844
Evansville
Children's Science Museum
2020 Ticket Sales
Ticket    TicketType    2020 Quantity    Price    Total
PremiumPlus    Yearly    3095    \$ 500.00    \$ 1,547,500
Grandparent    Yearly    1314    \$ 250.00    \$ 328,500
Family    Yearly    4126    \$ 200.00    \$ 825,200
Individual    Yearly    3033    \$ 165.00    \$ 500,445
Adult    1-Day    13867    \$ 25.75    \$ 357,075
Child    1-Day    16573    \$ 20.75    \$ 343,890
Senior    1-Day    10901    \$ 24.50    \$ 267,075
AllLocations
Children's Science Museum
2020 Ticket Sales
Ticket    TicketType    2020 Quantity    Price    Total
PremiumPlus    Yearly    9921    \$ 500.00    \$ 4,960,500
Grandparent    Yearly    6084    \$ 250.00    \$ 1,521,000
Family    Yearly    13738    \$ 200.00    \$ 2,747,600
Individual    Yearly    7291    \$ 165.00    \$ 1,203,015
Adult    1-Day    59363    \$ 25.75    \$ 1,528,597
Child    1-Day    52721    \$ 20.75    \$ 1,093,961
Senior    1-Day    29778    \$ 24.50    \$ 729,561
Total    \$ 13,784,234
LookUps
Ticket    TicketType    Price            Locations
Grandparent    Yearly    \$ 250.00            Bloomington
Family    Yearly    \$ 200.00            FortWayne
Individual    Yearly    \$ 165.00            Evansville
Child    1-Day    \$ 20.75
Senior    1-Day    \$ 24.50
NewWingLoan
Children's Science Museum
New Wing Loan
Input Variables                Calculated Values
Amount of Loan        \$600,000        Number of Payments        120
Interest Rate        3.25%        Monthly Payment        \$5,863.14
Term of Loan (years)        10        Total of Payments        \$703,577.01
Payments per Year        12        Total Interest Paid        \$103,577.01
Starting Date of Loan        6/1/21
Period    Date    Beginning Balance    Payment    Interest    Principal    Remaining...
SOLUTION.PDF