10 MARKS You have been hired as a consultant for Touf cily's Community Pool. The manager of Taif city's Communiry Pool needs you to prepare a worksheet to help analyze weekly receipts of child pool passes and adult pool passes. Perform the following steps on the spreadsheet: 1. Create the spreadsheet using the following data: Begin in cell A4. Re-format dates to mmVdd if needed. 2. Save workbook as: StudentId_First.Vame_Last.Vame to your competer. You will upload a Zip file later in Blackboard that contains two files. 3. Format the A column to show the date as 6/1,6/2, etc. Autofill the dates beginning with 6/1 and 6/2. Your final year should be 6/6. Center dates. 4. Add the following data to row 11 : 5. In cell Al type in " Taif city's Community Pool ". Merge and cemer vertically and horizontally over cells Al:F1. Use "Cambria" font. Change font size to 16. Bold and ltalicize text. 6. In cell A2, type in "Pool Pass Receipts for the Week of June 1 - June 7". Merge and center over eells A2:F2. Use "Cambria" font. Change fort size to 14. Bold Text. 7. Add a fill color to cell A1 8. In cell A12, insert the word, 'Totals'. Bold and right-align. 9. Total each column in row 12 10. In cell A15, type in the words "Child Pass Price". 11. In cell A16, type in the words "Adult Pass Price". 12. In cell D15, type in $1.50. In cell D16, type in $3,00. Make both currency format. 13. Create a formula uxing an absolute cell reference to calculate Child Pass Receipts for each day. Put that formula (and 5 total) in cell D5:D11. The total should calculate in D12. 14. Create a formula using an absolute cell reference to calculate Adult Pass Receipts for each day. Put that formula (and $ total) in cell E5:E11. The total should calculate in E12. 15. Cakculate total passes receipts ( $ earned each day) in F5:F11, with a grand total in F12. 16. Use Currency Fonnat for the D5:F12, center align 17. Select the range B12:F12 and add a 'Top and Doable Botiom Border.' 18. In cell A20, type in "Average." Calculate Average number of Child passes, Adulh passes, Child Pass $. Adalt Pass $ and Total Pass Receipts in that row. For money, use currency format, for number of passes use only whole numbers (no decimals). 19. In cell A21, type in 'Minimum." Calculate minimum number of Child passes, Adult passes, Child Pass 5. Adult Pass 5 and Total Pass Receipts in that row. For money, use currency format, for number of passes use only whole numbers (no decimals). 20. In cell A22, type in 'Maximum. Calculate Maximum number of Child passes, Adult passes, Child Pass \$. Adult Pass $ and Total Pass Receipts in that row. For money, use currency format, for number of passes use only whole numbers (no decimals). 21. Select the range A4:F4. Format the cells to Bold Italic, add a fill color 22. Select the range A4:F4. Center align, middle align the text then wrap text. 23. Change oricntation of spreadsheet to "Landseape". Center spreadsheet vertically \& horizontally. 24. In the header, add filename in center, add date in left. 25. Rename tab as "Pool Receipes" and color the tab 26. Select the range that coetains Dates, Number of Child Passes and Number of Adult Passes (A4:C11). Create a column or bar chart. 27. Change title in chart to "Weekly Receips"". 28. Save file to your memory stick 29. Delete any blank worksheets. (Don't delete the worksheet you just made!) 30. Spell check spreadsheet. 31. AutoSize columns A:F. 32. Create a pdf of your worksheet named as StudentId_First_Vame_Lastivame 33. Close file. 34. Zip both files, the pdf and the excel file. Name the file as studentId, upload to BlackBoard. 10 MARKS You have been hired as a consultant for Touf cily's Community Pool. The manager of Taif city's Communiry Pool needs you to prepare a worksheet to help analyze weekly receipts of child pool passes and adult pool passes. Perform the following steps on the spreadsheet: 1. Create the spreadsheet using the following data: Begin in cell A4. Re-format dates to mmVdd if needed. 2. Save workbook as: StudentId_First.Vame_Last.Vame to your competer. You will upload a Zip file later in Blackboard that contains two files. 3. Format the A column to show the date as 6/1,6/2, etc. Autofill the dates beginning with 6/1 and 6/2. Your final year should be 6/6. Center dates. 4. Add the following data to row 11 : 5. In cell Al type in " Taif city's Community Pool ". Merge and cemer vertically and horizontally over cells Al:F1. Use "Cambria" font. Change font size to 16. Bold and ltalicize text. 6. In cell A2, type in "Pool Pass Receipts for the Week of June 1 - June 7". Merge and center over eells A2:F2. Use "Cambria" font. Change fort size to 14. Bold Text. 7. Add a fill color to cell A1 8. In cell A12, insert the word, 'Totals'. Bold and right-align. 9. Total each column in row 12 10. In cell A15, type in the words "Child Pass Price". 11. In cell A16, type in the words "Adult Pass Price". 12. In cell D15, type in $1.50. In cell D16, type in $3,00. Make both currency format. 13. Create a formula uxing an absolute cell reference to calculate Child Pass Receipts for each day. Put that formula (and 5 total) in cell D5:D11. The total should calculate in D12. 14. Create a formula using an absolute cell reference to calculate Adult Pass Receipts for each day. Put that formula (and $ total) in cell E5:E11. The total should calculate in E12. 15. Cakculate total passes receipts ( $ earned each day) in F5:F11, with a grand total in F12. 16. Use Currency Fonnat for the D5:F12, center align 17. Select the range B12:F12 and add a 'Top and Doable Botiom Border.' 18. In cell A20, type in "Average." Calculate Average number of Child passes, Adulh passes, Child Pass $. Adalt Pass $ and Total Pass Receipts in that row. For money, use currency format, for number of passes use only whole numbers (no decimals). 19. In cell A21, type in 'Minimum." Calculate minimum number of Child passes, Adult passes, Child Pass 5. Adult Pass 5 and Total Pass Receipts in that row. For money, use currency format, for number of passes use only whole numbers (no decimals). 20. In cell A22, type in 'Maximum. Calculate Maximum number of Child passes, Adult passes, Child Pass \$. Adult Pass $ and Total Pass Receipts in that row. For money, use currency format, for number of passes use only whole numbers (no decimals). 21. Select the range A4:F4. Format the cells to Bold Italic, add a fill color 22. Select the range A4:F4. Center align, middle align the text then wrap text. 23. Change oricntation of spreadsheet to "Landseape". Center spreadsheet vertically \& horizontally. 24. In the header, add filename in center, add date in left. 25. Rename tab as "Pool Receipes" and color the tab 26. Select the range that coetains Dates, Number of Child Passes and Number of Adult Passes (A4:C11). Create a column or bar chart. 27. Change title in chart to "Weekly Receips"". 28. Save file to your memory stick 29. Delete any blank worksheets. (Don't delete the worksheet you just made!) 30. Spell check spreadsheet. 31. AutoSize columns A:F. 32. Create a pdf of your worksheet named as StudentId_First_Vame_Lastivame 33. Close file. 34. Zip both files, the pdf and the excel file. Name the file as studentId, upload to BlackBoard