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...

1 answer below »

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

Answered Same DayMay 15, 2022

Answer To: Scenario: Youare an intern at a Children’s Science Museum that has several locations in Indiana. A...

Prince answered on May 16 2022
83 Votes
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
    PremiumPlus    Yearly    $ 500.00            Southbend
    Grandparent    Yearly    $ 250.00            Bloomington
    Family    Yearly    $ 200.00            FortWayne
    Individual    Yearly    $ 165.00            Evansville
    Adult    1-Day    $ 25.75
    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

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here