Solution for QID #49632: Illustrated Excel 2016 Module 5: SAM Project 1b Old Town | StudyHelpMe

Illustrated Excel 2016  Module 5: SAM Project 1b Old Town Art Gallery ANALYZING DATA USING FORMULAS GETTING STARTED Open the file IL_EX16_5b_ FirstLastName _1.xlsx , available for download from the SAM website.   Save the file as IL_EX16_5b_ FirstLastName _2.xlsx by changing the “1” to a “2”. o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.   With the file IL_EX16_5b_ FirstLastName _2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Richard is the membership director at the Old Town Art Gallery. He asked you to help him analyze online and gallery ticket office sales of memberships. Richard also needs you to look at loan options for a renovation of the gallery’s education wing. Switch to the Gallery worksheet. Assign the name begin_date to the range C5:C13. 2. In cell E5, enter a formula without using a function to determine the membership renewal date by adding 365 to the beginning date in cell C5, using the range name begin_date in the formula. Copy the formula in cell E5 into the range E6:E13. 3. Delete the range name “membership_number” from the workbook. ( Hint: Do not delete the cell contents in this range, only the custom range name.) 4. Richard asks you to calculate the membership prices for the first quarter memberships sold in the gallery ticket office. The individual membership price is $50. Students pay a discounted price of $25. In cell F5, create a formula using the IF function to determine the membership price based on the following criteria: a. If cell D5 (the Category) is Student the price is 25 . ( Hint: Student must appear in quotes. Do not enter $ for the price.) b. All other individual members must pay 50 . Copy the formula in cell F5 into the range F6:F13. 5. In cell F14, create a formula using the SUM function to total the values in the range F5:F13 . Format cell F14 using bold formatting. 6. Richard asks you to calculate the number of student memberships sold in the gallery ticket office. In cell D17, create a formula using the COUNTIF function to count the number student memberships sold. Use D5:D13 as the range and Student as the criteria in your formula. 7. Richard is curious about the average membership price paid on a monthly basis. You will begin by looking at the average membership paid in January. In cell D18, create a formula using the AVERAGEIF function to find the average contribution prior to 2/1/2020. Use C5:C13 as the range, <2/1/2020 as the criteria, and F5:F13 as the average_range in your formula. 8. Richard asks you to find the total amount paid for student memberships. In cell D19, use the SUMIF function to total the student membership payments. Use D5:D13 as the range, Student as the criteria and F5:F13 as the sum_range in your formula. 9. Format cells D18 and D19 using the Currency number format with two decimal places and the $ symbol. 10. Richard would like you to calculate the membership sales for both the gallery and online. Switch to the Summary worksheet. In cell A5, create a formula using the SUM function and 3-D references to total cell F14 on the Gallery worksheet and cell F14 on the Online worksheet. 11. Format cell A5 using the Accounting number format with two decimal places and the $ symbol. 12. Richard would like you to separate the member names from their categories. Enter the text Jean Hogg in cell E5, then enter the text Bert Marson in cell E6, and then select the range E5:E22 and use Flash Fill to fill in the range E7:E22. Enter the text Individual in cell F5, then enter the text Individual in cell F6, and then select the range F5:F22 and use Flash Fill to fill in the range F7:E22. [Mac Hint: Flash Fill is not available in Excel 2016 for Mac, so refer to Final Figure 2 to enter the text.] 13. The gallery is considering a renovation of the Education Wing. Richard asks you to help with the analysis of the loan options to fund the renovation. Switch to the Education Wing worksheet, then create a formula using the PMT function in cell B8 to determine the monthly payment using the loan information shown in the range B5:B7. In your formula, divide the rate (cell B6 ) by 12 , use the term in months (cell B7 ) as the nper, and use a negative value for the pv (cell B5 ). Copy the formula from cell B8 into the range C8:D8. 14. In cell B9, enter a formula without using a function to calculate the total payments by multiplying the monthly payments (cell B8 ) by the term in months (cell B7 ) for Loan Option 1. Copy the formula from cell B9 into the range C9:D9. 15. In cell B10, enter a formula without using a function that calculates the total interest by subtracting the loan amount (cell B5 ) from the total payments (cell B9 ) for Loan Option 1. Copy the formula from cell B10 into the range C10:D10. Your workbook should look like the Final Figures below. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Gallery Worksheet Final Figure 2: Summary Worksheet Final Figure 3: Education Wing Worksheet