New PerspectivesExcel 2016|Module 4: SAM Project 1a

Inception Workspace

ANALYZING AND CHARTING FINANCIAL DATA

GETTING STARTED

• Open the fileNP_EX16_4a_FirstLastName_1.xlsx,available for downloadfrom the SAM website.
• Savethe fileasNP_EX16_4a_FirstLastName_2.xlsxby changing the "1" to a "2".
• If you do not see the.xlsxfile extensionin the SaveAsdialog box, do not type it.The program willadd the file extension for you automatically.
• With the fileNP_EX16_4a_FirstLastName_2.xlsxstill open, ensure that your first and last name is displayed in cell B6 of theDocumentation sheet.
• If cell B6 does not display your name, delete the file and download anew copy from the SAM website.

PROJECTSTEPS

1. Casey Byron is the owner of Inception Workspace, a collaborative office building where individuals, startups, or small businesses can reserve work spaces. Casey needs to secure a loan to renovate the office, so he is preparing some charts that represent Inception Workspace's finances to use in his loan applications.

Casey wants a chart representing the distribution of average hours per week that members utilized Inception Workspace in 2024.Switch to theAverage Usage 2024worksheet. Select the range A4:B243 and make aHistogramchart. (Hint:Use the Name box to select the range.) Modify the chart as described below:

1. Resize and reposition the chart so that the upper-left corner is located within cellD4and the lower-right corner is located within cellK18.
2. EnterAverage Weekly Usage (in Hours) in 2024as the title of the chart.
3. Modify the bins used in the chart by setting theBin Widthaxis option to10.
4. Inception Workspace offers a variety of membership packages to fit the needs and budgets of its customers. Casey wants to graphically represent how those packages impacted Inception Workspace's total annual incomebetween 2019 and 2024.

Switch to theAnnual Incomeworksheet. InsertColumnsparklinesinto the rangeH5:H10based on the data in the rangeB5:G10, and then apply theGreen, Accent 6, Darker 25%sparkline color.

1. Apply aSolidFill,GreenDataBarconditional formatting ruleinto the rangeI5:I10.
2. Casey wants a pie chart representing how each membership package contributed to the Inception Workspace's total annual income in 2019.

Select the rangeA5:B9,andthenmake a2-DPiechart. Modify the chart as described below:

1. Resize and reposition the chart so that theupper-left corner is locatedwithin cellK1and thelower-right corner is locatedwithin cellQ13.
2. Enter2019Total Annual Income by Packageas the chart title.
3. Apply theStyle 6chart style.
4. In the2024 Total Annual Income by Package3-D pie chart(located in the range K14:Q28), position the chart legend using theBottomoption.
5. In the 3-D pie chart, adddata labelsto the chart using the following options:
6. The data labels should display using theOutside Endposition option.
7. The data labels should only display thePercentageassociated witheachslice of the 3-D pie chart. (Hint: You may need to uncheck theValuedata label option.)
8. The data label should use thePercentagenumber format with1decimal place.
9. Update theTotal Annual Income: 2019- 2024line chartin the range A11:J26 by editingtheHorizontal (Category) Axis labelstodisplay using the values in the rangeB4:G4.
10. In the line chart, modify theMinimum boundsof theverticalaxis to be150000.
11. Update the line chart byaddingPrimary Major Horizontal gridlinesandPrimary Major Vertical gridlinestothe chart area.
12. Format the line chart as described below:
13. Apply a solid fill using theBlue, Accent 5, Lighter 80%fill color to the chart area.
14. Apply theArialfont and theBlue, Accent 5font color to the chart title.
15. Casey created a stacked column chart to show how the income generated by each membership package contributed to the total annual income. He now needs to modify the data and formatting used in the chart.

UpdatethePackage Contribution to Annual Income: 2019- 2024stackedcolumn chart(in the range A27:J44)by removing the data series labeled"Total"from the chart. (Hint:Do not filter out or hide the data.)

1. In the stacked column chart, format the chart legend as described below:
2. Apply aShape Fillusing theWhite, Background 1fill color.
3. Apply aSolidLineborderwith aBlue, Accent 1border color.
4. Apply aShadowShapeEffectfrom the Outer sectionusing theOffset Diagonal Bottom Rightoption.(Hint: Depending on your version of Office, this may be displayed as "Offset:Bottom Right".)
5. For his loan application,Casey needsto createa chart that displays both the annual income generated by each membership package and Inception Workspace's total annual income. Because of the large difference between the package income and total income values, Casey determines that a combo chart is most appropriate option.

Selectthe rangeA4:G10andmakeCustom CombinationCombochart as described below:

1. Represent the following dataseriesas aClustered Columnchart:Open Desk - Visitor,Open Desk - Regular,Dedicated Desk,Dedicated Office, andMeeting Room.
2. Represent theTotaldata seriesas aLinechart using theSecondaryAxis,as shown inFigure 1below.

Figure1: Combo Chart Setup

1. Move the combo chart to theIncome Overviewworksheet,andthen resize and reposition the chart so that theupper-left corner is locatedwithin cellA4and thelower-right corner is locatedwithin cellK23.
2. EnterPackage and Total Annual Incomeas the chart title.
3. Add axis titles to the chart, then enterPackage Incomeas the left vertical axis title,andthenenterTotal Incomeas the right vertical axis title. Finally,deletethe horizontal axis title placeholder.
4. Casey wants to calculate the monthly payments for each loanoption that he is considering.

Switch to theLoan Optionsworksheet. In cellB12, createa formula using thePMTfunction to calculate the monthly payments for loan Option A. Use thevalues in cellsB8,B10, andB5for theRate,Nper, andPvarguments,respectively,and do not enter any values for the optional arguments. Copy the formula you createdin cellB12into the rangeC12:D12.