Illustrated Excel 2019 | Modules 1-4: SAM Capstone Project 1a Illustrated Excel 2019 | Modules 1-4: SAM Capstone Project 1a CREATE AND FORMAT A FINANCIAL ANALYSIS
GETTING STARTED
Open the fileIL_EX19_CS1-4a_FirstLastName_1.xlsx, available for download from the SAM website.
Save the file asIL_EX19_CS1-4a_FirstLastName_2.xlsxby changing the “1” to a “2”.
If you do not see the.xlsxfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
With the fileIL_EX19_CS1-4a_FirstLastName_2.xlsxstill open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS
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 theRevenue & Expensesworksheet. In cell K1, insert a formula using theTODAYfunction 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:
Merge and centerthe contents of the range A4:A17.
Rotate the text in the merged cell up to 90 degrees so it reads from bottom to top.
Middle-align the merged cell.
Resize column A to a width of6.00.
UseAutoFitto resize column B to its best fit to display all the revenue and expense types.
Complete the calculations for the Revenue data as follows:
In cell C7, enter45,600as the Business services revenue for September.
In cell C8, use theSUMfunction to total the September Revenue values.
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 usingCommastyle and no decimal places to match the formatting of the Revenue data.
Kamal wants to display the highest and lowest revenue amounts from September to December. Enter and format this information as follows:
In cell C24, enter a formula using theMINfunction to display the lowest revenue in the range C5:F7. In cell C25, enter a formula using theMAXfunction to display the highest revenue in the range C5:F7.
ApplyOutside Bordersto the range B24:C25 usingBlack, 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:
Switch the rows and columns to display expenses by type.
Move the legend to the right side of the chart.
AddMonthly Amountas the primary vertical axis title.
AddExpenses by Typeas the chart title.
Change the fill color of the December data series toDark Red, Accent 1.
Add a chart border using theBlack, 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:
Create a doughnut chart based on the range C21:F22.
Resize and reposition the chart so that its upper-left corner is within cell J19 and its lower-right corner is within cell P31.
EnterSept to Dec Profitas the chart title.
ApplyLayout 6to 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:
Create a Stacked Column chart based on the range B4:F7.
Move the chart to a new sheet namedRevenue Chart.
Change the chart style toStyle 7to match the style of the clustered column chart on theRevenue & Expensesworksheet.
Change the font size of all the chart text to14 pointto make it easier to read.
Remove the chart title since the sheet tab indicates the purpose of the chart.
Clarify the data in the chart as follows:
Format the values in the vertical axis using theAccountingnumber format with no decimal places to clarify the values are dollar amounts.
Add a data table with legend keys to the chart to display the revenue values.
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:
Go to theRevenue & Expensesworksheet. In cell G5, insert aLinesparkline based on the data in the range C5:F5.
Include markers in the sparkline, and then change the marker color toBlack, Text 1.
Copy cell G5, and then paste it in the range G6:G8, the range G12:G17, and cell G22.
Go to theBusiness Customer Analysisworksheet, 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:
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 by365.25, the number of days in a year, accounting for leap year.
Use an absolute reference to cell C2 in the formula.
Display the value in cell E5 with one decimal place.
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:
In cell H5, enter a formula using theIFfunction that tests whether the number of years is greater than or equal to4. If it is, display"Y"in cell H5. If it is not, display"N"in cell H5.
Fill the range H6:H18 with the formula in cell H5.
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:
In cell I5, enter a formula using theANDfunction that tests whether the Wireless value is equal to"Y"and whether the Discount value is equal to"Y".
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:
In cell J5, enter a formula using theORfunction that tests whether the location is equal to"N"or
whether the plan type is equal to"Unlimited".
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 theCOUNTAfunction 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 theAVERAGEfunction to average the number of years.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
The value in cell K1 of the Revenue & Expenses worksheet has been intentionally blurred as it will never be constant. The size of the cell may differ due to the date it contains.
Final Figure 1: Revenue Chart Worksheet
Final Figure 2: Revenue and Expenses Worksheet
Final Figure 3: Business Customer Analysis Worksheet