+1 (208) 254-6996 [email protected]
  

Need help with an Excel assignment

Guest List

Guest List
54Total number of guests attendingcost per invitation$0.97
0Count of invitations sentpostage cost$0.56
54Count of missing responsesTotal cost for purchasing and mailing invitations
79Largest group attending$82.62
1.00Average number of guests in each group
TitleFirst NameLast NameName TagStreetCityStateZipPhoneNumAttending
Mr.CindyJacobsCindyjacobs417 9th St.RocklinCA95602916-598-69522
MrsLarrinaGrandeLarrinagrande87441 Palace SquareNewcastleCA95602916-741-85264
MissKatherineHairlessKatherinehairless2068 Harry HillAuburnCA95602530-888-08051
MrJamesMuchleyJamesmuchley87543 Baldys RoadAuburnCA95602530-885-65231
Mr./MrsWilliamHollandWilliamholland9225 Marchmont DrSaramentoCA95602(916)348-99821
Mr/MrsBethanySanchazBethanysanchaz8852 Jones LaneSanta RosaCA95602(707)521-34783
MrsGeraldWatkinsGeraldwatkins446 Chest AveSan JoseCA95602(415)441-86392
MrEmilyThatcherEmilythatcher2275 Oak Park LaneRocklinCA95602(916)315-86902
Mr/MrsLouisaCaterLouisacater852 Paly PlaceRosevilleCA95602(916)789-44702
Mr.HaroldRogerHaroldroger14 Header DrRocklinCA95602555-1324
Mr.MichaelSlocumMichaelslocum543 Tulip AveRocklinCA95602555-24530
Mr.AdamMelonAdammelon387 Daly StRocklinCA95602555-26060
Mr.ClaireAceClaireace9083 Rocky RdRocklinCA95602555-2690
Mr.JacksonPerryJacksonperry883 Forest DriveRocklinCA95602555-99021
Mr.JamesSmithJamessmith5366 Onyx DriveRocklinCA95602(916)521-66542
Mrs.JimSmithJimsmith5366 Onyx DriveRocklinCA95602(916)632-51520
Ms.TomJacksonTomjackson2323 Windyl DriveRocklinCA95602(916)632-01270
Mr.ChuckNewmanChucknewman4477 Jade RoadRosevilleCA95602(916)624-56562
Miss.BarbraWilliamsBarbrawilliams1453 Frosty St.San DiegoCA95602(619)279-94860
Mr.WalterPellWalterpell890 1st Ave.San DiegoCA95602(619)560-23561
Mrs.JerryJonesJerryjones5678 Oak Ave.AuburnCA95602(530)823-49832
Mr.ChristopherJonesChristopherjones5678 Oak Ave.AuburnCA95602(530)823-49830
Miss.KeithLeeKeithlee7823 SeacrestWeimarCA95602(530)637-99011
Mr.ByungNagatoByungnagato23 Shadow LnDark CityCA95602458-62881
Mr.SharonNagasakiSharonnagasaki410 Lemoen DrWatershedCA95602626-83241
Ms.DavidLonsdaleDavidlonsdale725 Wave st.La BeachCA95602364-62745
Mr.KarenNegreedyKarennegreedy858 Steal WayDark CityCA95602458-58262
Ms.LarryChamaLarrychama443 Harty Ave.WatershedCA95602626-54611
MrBrendaSmithBrendasmith564 Newby StreetRackCA95602906-452-55812
MsMattCastleMattcastle48265 Long StDewyCA95602554-967-45181
MrsTechiWillowsTechiwillows852 Spree StBarkCA95602613-555-43214
MrMarieBondMariebond1007 Pagent StSteamsCA95602541-948-48491
MrJuanScottJuanscott8584 Sure LnNowhereCA95602987-654-32101
MissKimJohnsonKimjohnson5687 Art WayRocklinCA95602916-789-52852
MrCharlesNosmilesCharlesnosmiles4412 Meanie StreetLoomisCA95602916-883-25461
MrsLarrinaGrandeLarrinagrande87441 Palada CircleNewcastleCA95602916-741-85264
MissKatherineHairlessKatherinehairless2068 Bald HillAuburnCA95602530-888-08051
MrJamesMuchleyJamesmuchley87543 Harry’s RoadAuburnCA95602530-885-65231
Mr./MrsWilliamHollandWilliamholland9225 Larchmont DrSaramentoCA95602(916)348-99822
Mr/MrsBethanySanchazBethanysanchaz8852 Smith LaneSanta RosaCA95602(707)521-34783
MrsGeraldWatkinsGeraldwatkins446 Chester AveSan JoseCA95602(415)441-86391
MrEmilyThatcherEmilythatcher2275 Oak Tree LaneRocklinCA95602(916)315-86901
Mr/MrsLouisaCaterLouisacater852 Park PlaceRosevilleCA95602(916)789-44701
Mr.HaroldRogerHaroldroger14 Body DrRocklinCA95602555-13240
Mr.MichaelSlocumMichaelslocum543 Lily AvRocklinCA95602555-2453
Mr.AdamMelonAdammelon387 Day StRocklinCA95602555-26060
Mr.ClaireAceClaireace9083 Rock RdRocklinCA95602555-26900
Mr.JacksonPerryJacksonperry883 Forest LnRocklinCA95602555-99021
Mr.JamesSmithJamessmith5366 Onyx DriveRocklinCA95602(916)521-66541
Mrs.JimSmithJimsmith5366 Onyx DriveRocklinCA95602(916)632-51520
Ms.TomJacksonTomjackson2323 Windfall DriveRocklinCA95602(916)632-01270
Mr.ChuckNewmanChucknewman4477 Emerald RoadRosevilleCA95602(916)624-56561
Mrs.SteveNicksStevenicks2897 Tyler StreetLincolnCA95602(916)521-46464
Mr.BillWilsonBillwilson1925 Park Ln.ColfaxCA95602530-346-89532
Mrs.RobertNettleRobertnettle45 High St.AuburnCA95602530-889-76322
Mrs.DebbieFrostDebbiefrost25572 Sierra WayWeimarCA95602530-637-85214
Mr.LinzyO’NeilLinzyo’Neil6312 Westridge Ave.AltaCA95602530-346-9975
Mr.JoshHawkinsJoshhawkins7438 Atlantic Dr.AuburnCA95602530-878-73691

Shopping List

Shopping List
number of different items0ERROR:#NAME?Average item cost
ERROR:#NAME?Highest Item Cost
ERROR:#NAME?Lowest Item Cost
$48,065,099,448,155,970,000,000,000,000,000.00Total Cost with Tax
CitySourceItem DescriptionQuantityUnitsCostperUnitCostTaxTotal Cost
5%
ERROR:#NAME?Super SupermarketAppetizers10pounds$10.00$100$5.00$105.00
ERROR:#NAME?Sharons BakeryCake1Each$350.00$350$1,750.00$2,100.00
ERROR:#NAME?The Party StoreDessert Forks, Cofee spoons125each$0.50$63$109,375.00$109,437.50
ERROR:#NAME?The Party StoreDessert Plates125each$1.00$125$13,671,875.00$13,672,000.00
ERROR:#NAME?The Party StoreDinner Plates125each$1.25$156$2,136,230,468.75$2,136,230,625.00
ERROR:#NAME?The Party StoreDinner Silverware125sets$1.00$125$267,028,808,593.75$267,028,808,718.75
ERROR:#NAME?Flower Power FloristFlower Arangements25each$40.00$1,000$267,028,808,593,750.00$267,028,808,594,750.00
ERROR:#NAME?Party RentalsGlass Coffee Cups and Saucers75each$2.00$150$40,054,321,289,062,500.00$40,054,321,289,062,600.00
ERROR:#NAME?The Party StoreGlasses125each$1.50$188$7,510,185,241,699,220,000.00$7,510,185,241,699,220,000.00
ERROR:#NAME?EngraversInvitations75each$0.89$67$501,304,864,883,422,860,000.00$501,304,864,883,422,860,000.00
ERROR:#NAME?The EntertainersMusic1each$1,500.00$1,500$751,957,297,325,134,200,000,000.00$751,957,297,325,134,200,000,000.00
ERROR:#NAME?The Party StoreNapkins125each$0.50$63$46,997,331,082,820,890,000,000,000.00$46,997,331,082,820,890,000,000,000.00
ERROR:#NAME?The Party StoreSalad Plates125each$0.75$94$4,405,999,789,014,459,000,000,000,000.00$4,405,999,789,014,459,000,000,000,000.00
ERROR:#NAME?Costco – FolsomSoft Drinks and Water12case$9.00$108$475,847,977,213,561,600,000,000,000,000.00$475,847,977,213,561,600,000,000,000,000.00
ERROR:#NAME?The Party StoreTable Cloths25each$4.00$100$47,584,797,721,356,160,000,000,000,000,000.00$47,584,797,721,356,160,000,000,000,000,000.00

Places to Shop

look up table
1st column2nd column3rd column
CompanyNameStreetCityStateZipContactFNContactLNCell Phone
BevMO2000 Price Dr.SacramentoCA95643BrendaGreen888-8888
Costco – Folsom345 Stanford Ranch RoadFolsomCA95612JuanWhitenhouse666-7878
Cravens456 Bishop StreetFolsomCA95613ManuelEnriquez667-7879
CreativeBallons454 Vernon StreetCitrus HeightsCA99509KarenBonnet677-7654
Engravers600 Arden WayCarmichaelCA95605DavidNewsom878-9089
Flower Power Florist3456 Tulip DriveFolsomCA95613ShaneDavid666-7911
John’s Dishes9857 First StreetFolsomCA95614BobAdzich666-7122
Party Rentals455 Douglas Bld.RosevilleCA95643TechiSmith765-0987
Sharons Bakery700 LeidersdorfFolsomCA95612SharonCoon555-6666
Super Supermarket1345 Sierra Blvd.RosevilleCA95643MarieJorgenson676-8765
The Entertainers1600 BroadwaySacramentoCA95643LarryShane786-8998
The Flower Shop12Marh LaneSacramentoCA95644SueMarshal786-8449
The Party Store333 Galilee DriveCitrus HeightsCA99509MattCanoli555-8989
Total Wines652 Sutter StreetFolsomCA95612ByungJacobs777-7777

Summary

Party Financing
As of1/6/20
# of InvitationsERROR:#NAME?
# of GuestsERROR:#NAME?
Cash Available for Event$2,500.00
Do We Need to Borrow?
Amount to Borrow
APR7.0%
# Months to Pay12
Monthly Payment Amount

Instructions

Don't use plagiarized sources. Get Your Custom Essay on
Excel Homework
Just from $13/Page
Order Essay

In this project, you will correct function mistakes and other formula errors in a workbook designed for planning a large party or event. Be sure to save your work often!

Skills needed to complete this project:

· Checking Formulas for Errors (Skill 3.18)

· Finding Errors Using Trace Precedents and Trace Dependents (Skill 3.19)

· Displaying and Printing Formulas (Skill 3.20)

· Creating Formulas Using Counting Functions (Skill 3.6)

· Finding Minimum and Maximum Values (Skill 3.4)

· Formatting Text Using Functions (Skill 3.7)

· Using CONCAT to Combine Text (Skill 3.8)

· Finding Data Using the VLOOKUP Function (Skill 3.17)

· Using the Function Arguments Dialog to Enter Functions (Skill 3.1)

· Using Formula AutoComplete to Enter Functions (Skill 3.2)

· Calculating Averages (Skill 3.3)

· Naming Ranges of Cells (Skill 3.11)

· Working with Named Ranges (Skill 3.12)

· Updating Named Ranges with the Name Manager (Skill 3.13)

· Editing and Deleting Names with the Name Manager (Skill 3.14)

· Using Date and Time Functions (Skill 3.5)

· Using the Logical Function IF (Skill 3.15)

· Creating Formulas Referencing Data from Other Worksheets (Skill 3.10)

· Calculating Loan Payments Using the PMT Function (Skill 3.16)

·  Open the start file EX2019-FixIt-3-6. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.

·  If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.

· On the GuestList sheet, check all the formulas. Cells to check are filled with the light purple color. Most of them need to be corrected. Use error checking as needed and/or display the formulas on-screen for easy viewing.

·  In the Name Tag column, modify the formula to display the guest name in this format: BILL SMITH  Hint: There are multiple errors in this formula. Fix the formula in cell D10 and then copy it to the other cells in the column.

·  Correct the function used in cell A3 to calculate the sum of the values in the NumAttending column.

·  Correct the function used in cell A4 to count the number of values in the Street column.

·  Correct the function used in cell A5 to count the number of blank cells in the NumAttending column.

·  Correct the function used in cell A6 to display the largest value in the NumAttending column.

·  Correct the function used in cell A7 to display the average value in the NumAttending column.

· On the Shopping List sheet, check all the formulas. Cells to check are filled with the light purple color. Most of them need to be corrected. Many of the problems on this worksheet can be solved by creating named ranges or using a name that already exists.

·  The formula in cell B2 uses the wrong function.

·  The formulas in cells A9:A23 reference a named range that doesn’t exist. There is more than one correct way to fix this problem using the cell range A5:H18 on the Places to Shop worksheet. You can create the named range referenced in the formulas, or you can change the function arguments to reference the cell range instead.

·  The formula in cell H9 results in the correct value. However, the workbook author copied this formula to the remaining cells in the column and those values are definitely not correct! Fix the formula in cell H9 and copy it to cells H10:H23. Hint: Notice that cell H8 is named Tax.

· If you’ve fixed the formulas in cells H9:H23 correctly, the formulas in cells I9:I23 and G5 should calculate properly now. However, the formulas in cells G2:G4 still have errors that need to be fixed. Hint: Use error checking as needed and/or display the formulas on-screen for easy viewing.

·  Correct the function used in cell G2 to average value of the Cost column.

·  Correct the function used in cell G3 to display the largest value in the Cost column.

·  Correct the function used in cell G4 to display the smallest value in the Cost column.

· On the Summary sheet, you will be entering all the formulas. Cells to complete are filled with the light purple color. Hint: Use error checking as needed and/or display the formulas on-screen for easy viewing.

·  Cell B2 should use a function that will update the date to the current date every time the workbook is opened.

·  Cell B4 references a named range that doesn’t exist. It should reference cell A4 on the Guest List sheet. You can create the named range or edit the formula to reference the cell instead.

·  Cell B5 references a named range that doesn’t exist. It should reference cell A3 on the Guest List sheet. You can create the named range or edit the formula to reference the cell instead.

·  Cell B8 is missing the formula to calculate whether or not the total Cost with tax on the Shopping List sheet + the total Cost for purchasing and mailing invitations on the Guest List sheet is greater than the available cash. The cell should display yes or no.

·  Add a formula to cell B9 to calculate the amount to borrow (total Cost with tax on the Shopping List sheet + the total Cost for purchasing and mailing invitations on the Guest List sheet – the cash available) if the value in cell B8 is yes. If the value in cell B8 is not yes, the cell should display 0.

·  Add a formula to cell B12 to calculate the monthly loan payment based on the information in cells B9:B11. Use a negative number for the Pv argument.

·  Save and close the workbook.

·  Upload and save your project file.

Order your essay today and save 10% with the discount code ESSAYHELP