IL_EX19_CS1-4a_RodrigoTroconis Al Choufi_Report_2

Illustrated Excel 2019 | Modules 1-4: SAM Capstone Project 1a Rodrigo Troconis Al Choufi GE ver. 15.0.0-rc0000 1. 5/5 Create a formula using a function. 2. 5/5 AutoFill values in a range. 3. 5/5 Merge and center a range. Change the orientation of cell content. Align cell content vertically. Change the column width. 4. 5/5 Change the column width. 5. 5/5 Enter a number in a cell. Create a formula using a function. Copy a formula into a range. 6. 0/5 Change the number format. Change the number format. 7. 5/5 Create a formula using a function. Create a formula using a function. Add a border to a range. Add a border color to a range. 8. 5/5 Switch rows and columns of a chart. Change the position of the legend. Add an axis title to a chart. Insert a chart title. Change the fill color of a data series. Change the color of the chart border. 9. 5/6 Insert a chart. Resize and reposition a chart. Insert a chart title. Change the layout of a chart. In the Revenue & Expenses worksheet, the doughnut chart should be formatted using the Layout 6 chart layout. 10. 6/6 Insert a chart. Move a chart to a chart sheet. Change the chart style. Change the size of text in vertical and horizontal axes. Remove a chart title. 11. 6/6 Change the number format of the vertical axis text. Change the type of data table displayed in a chart. Remove a legend from a chart. 12. 6/6 Add sparklines to a cell. Format sparklines. Copy sparklines to a range. 13. 6/6 Create a formula without using a function. Use an absolute reference in a formula. Change the number of decimal places. Copy a formula into a range. 14. 0/6 Create a formula using a function. Copy a formula into a range. In the Business Customer Analysis worksheet, cell H5 contains an incorrect formula. 15. 6/6 Create a formula using a function. Copy a formula into a range. 16. 6/6 Create a formula using a function. Copy a formula into a range. 17. 6/6 Create a formula using a function. 18. 6/6 Create a formula using a function. SUBMISSION #2 | SCORE IS: 88 OUT OF 100 Kamal Haman directs the San Antonio office of Cello Worldwide, a global communications company. He has been tracking revenues and expenses along with customer data in an Excel workbook, including charts to help him visualize the data. He has asked you to help him complete the workbook and insert additional charts. Go to the Revenue & Expenses worksheet. In cell K1, insert a formula using the TODAY function to display today's date. Fill the range D4:F4 with a series based on the value in cell C4 to provide the missing month names. Format the text in cell A4 as follows to make it readable and more meaningful: a. Merge and center the contents of the range A4:A17. b. Rotate the text in the merged cell up to 90 degrees so it reads from bottom to top. c. Middle-align the merged cell. d. Resize column A to a width of 6.00 . Use AutoFit to resize column B to its best fit to display all the revenue and expense types. Complete the calculations for the Revenue data as follows: a. In cell C7, enter 45,600 as the Business services revenue for September. b. In cell C8, use the SUM function to total the September Revenue values. c. Copy the formula in cell C8 to the range D8:F8 and to cell H8 to complete the totals. Format the nonadjacent ranges C13:F16 and H13:H16 using Comma style and no decimal places to match the formatting of the Revenue data. In the Revenue & Expenses worksheet, the contents of the range C13:F16 should be formatted using the Comma number format with zero decimal places. In the Revenue & Expenses worksheet, the contents of the range H13:H16 should be formatted using the Comma number format with zero decimal places. Kamal wants to display the highest and lowest revenue amounts from September to December. Enter and format this information as follows: a. In cell C24, enter a formula using the MIN function to display the lowest revenue in the range C5:F7. b. In cell C25, enter a formula using the MAX function to display the highest revenue in the range C5:F7. c. Apply Outside Borders to the range B24:C25 using Black, Text 1, Lighter 35% as the border color to show the information belongs together. In the clustered column chart in the range J3:P17, Kamal wants to show the expenses by type, not by month. He also wants to make the contents of the chart clearer. Provide this information for him as follows: a. Switch the rows and columns to display expenses by type. b. Move the legend to the right side of the chart. c. Add Monthly Amount as the primary vertical axis title. d. Add Expenses by Type as the chart title. e. Change the fill color of the December data series to Dark Red, Accent 1 . f. Add a chart border using the Black, Text 1, Lighter 50% shape outline color. Kamal wants to include a chart showing the monthly profits for the San Antonio office to determine which months have been more favorable. Create a new chart as follows: a. Create a doughnut chart based on the range C21:F22. b. Resize and reposition the chart so that its upper-left corner is within cell J19 and its lower-right corner is within cell P31. c. Enter Sept to Dec Profit as the chart title. d. Apply Layout 6 to the chart to display percentages on each part of the doughnut. Kamal also wants to include a chart showing the revenue earned from mobile phones, wireless services, and business services. Create and format a chart for him as follows: a. Create a Stacked Column chart based on the range B4:F7. b. Move the chart to a new sheet named Revenue Chart . c. Change the chart style to Style 7 to match the style of the clustered column chart on the Revenue & Expenses worksheet. d. Change the font size of all the chart text to 14 point to make it easier to read. e. Remove the chart title since the sheet tab indicates the purpose of the chart. Clarify the data in the chart as follows: a. Format the values in the vertical axis using the Accounting number format with no decimal places to clarify the values are dollar amounts. b. Add a data table with legend keys to the chart to display the revenue values. c. Remove the legend, which is now redundant. Kamal wants to track the trends for each type of revenue and expense and for the profit analysis. Provide this information for him as follows: a. Go to the Revenue & Expenses worksheet. In cell G5, insert a Line sparkline based on the data in the range C5:F5. b. Include markers in the sparkline, and then change the marker color to Black, Text 1 . c. Copy cell G5, and then paste it in the range G6:G8, the range G12:G17, and cell G22. Go to the Business Customer Analysis worksheet, which compiles data about the Texas business accounts that Kamal handles. Calculate the number of years a customer has been with Cello Worldwide as follows: a. In cell E5, enter a formula without using a function that subtracts the start date for the customer from the current date and divides the result by 365.25 , the number of days in a year, accounting for leap year. b. Use an absolute reference to cell C2 in the formula. c. Display the value in cell E5 with one decimal place. d. Fill the range E6:E18 with the formula in cell E5. Cello Worldwide offers a discount to customers who have been with the company for at least four years. Determine whether each customer qualifies for a discount as follows: a. In cell H5, enter a formula using the IF function that tests whether the number of years is greater than or equal to 4 . If it is, display "Y" in cell H5. If it is not, display "N" in cell H5. b. Fill the range H6:H18 with the formula in cell H5. In the Business Customer Analysis worksheet, the formula in cell H5 should check if the value in cell E5 is greater than or equal to 4 in the logical_test argument. Kamal plans to offer new, more favorable contracts to business customers who are now receiving a discount and use wireless services. Determine whether each customer should receive a new contract as follows: a. In cell I5, enter a formula using the AND function that tests whether the Wireless value is equal to "Y" and whether the Discount value is equal to "Y" . b. Fill the range I6:I18 with the formula in cell I5. Kamal also plans to offer a free mobile phone to customers with businesses in northern Texas using an Unlimited plan. Determine whether each customer should receive a free phone as follows: a. In cell J5, enter a formula using the OR function that tests whether the location is equal to "N" or whether the plan type is equal to "Unlimited" . b. Fill the range J6:J18 with the formula in cell J5. Kamal wants to display the total number of business customers. In cell M4, enter a formula using the COUNTA function to count the customer IDs. Kamal wants to determine the average number of years customers have been with Cello Worldwide. In cell M5, enter a formula using the AVERAGE function to average the number of years.

Author: Note: Do

M

September October November December $- $50,000 $100,000 $150,000 $200,000 $250,000

Date: 1/20/2024 Total 255,912 313,273 168,880 $ 738,065 Total $ 52,000 33,400 167,450 24,850 31,900 $ 309,600 Total $ 428,465 Rent Insurance Salaries Utilities Taxes $- $5,000 $10,000 $15,000 $20,000 $25,000 $30,000 $35,000 $40,000 $45,000 Expenses by Type Monthly Amount Sept to Dec Profit Se Oc No De

September October November December eptember ctober ovember ecember

Analysis Discount? New Contract? Free Phone? Number of customers 14 Y 0 1 Average years as customer 3.2 Y 1 1 N 0 0 N 0 1 N 0 1 N 0 1 N 0 0 N 0 1 N 0 1 N 0 1 Y 1 1 N 0 0 Y 1 1 N 0 1