New Perspectives Excel 2016 | Module 4: SAM Project 1a Inception Workspace AnalyZing and Charting Financial Data GETTING STARTED Open the file NP_EX16_4a_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2016 | Module 4: SAM Project 1a

Inception Workspace AnalyZing and Charting Financial Data

GETTING STARTED

Open the file NP_EX16_4a_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as NP_EX16_4a_FirstLastName_2.xlsx by changing the “1” to a “2”.

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 NP_EX16_4a_FirstLastName_2.xlsx still 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

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 the Average Usage 2024 worksheet. Select the range A4:B243 and create a Histogram chart. (Hint: Use the Name box to select the range.) Modify the chart as described below:

a. Resize and reposition the chart so that the upper-left corner is located within cell D4 and the lower-right corner is located within cell K18.

b. Enter Average Weekly Usage (in Hours) in 2024 as the title of the chart.

c. Modify the bins used in the chart by setting the Bin Width axis option to 10.

Insert a chart.

Resize a chart.

Insert a chart title.

Change the bin width of the chart data.

2. 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 income between 2019 and 2024. Switch to the Annual Income worksheet. Insert Column sparklines into the range H5:H10based on the data in the range B5:G10, and then apply the Green, Accent 6, Darker 25% sparkline color.

Add sparklines to cells.

Set the color of a sparkline.

3. Apply a Solid Fill, Green Data Bar conditional formatting ruleinto the range I5:I10.

Apply a conditional formatting rule that uses data bars.

4. Casey wants a pie chart representing how each membership package contributed to the Inception Workspace’s total annual income in 2019. Select the range A5:B9, and then create a 2-D Pie chart. Modify the chart as described below:

a. Resize and reposition the chart so that the upper-left corner is located within cell K1 and the lower-right corner is located within cell Q13.
b. Enter 2019 Total Annual Income by Package as the chart title.

c. Apply the Style 6 chart style.

Insert a chart.

Resize and reposition a chart.

Insert a chart title.

Change the style of a chart.

5. In the 2024 Total Annual Income by Package 3-D pie chart (located in the range K14:Q28), position the chart legend using the Bottom option.

Change the position of the legend.

6. In the 3-D pie chart, add data labelsto the chart using the following options:

a. The data labels should display using the Outside End position option.

b. The data labels should only display the Percentage associated with each slice of the 3-D pie chart. (Hint: You may need to uncheck the Valuedata label option.)

c. The data label should use the Percentage number format with 1 decimal place.

Change the data label position.

Change the data label options.

Change the number format of a data label.

 

7. Update the Total Annual Income: 2019 – 2024 line chart in the range A11:J26 by editing the Horizontal (Category) Axis labels to display using the values in the range B4:G4.

Update the data labels in a chart.

8. In the line chart, modify the Minimum boundsof the vertical axis to be 150000.

Change the minimum bounds of the vertical axis.

9. Update the line chart by adding Primary Major Horizontal gridlines and Primary Major Vertical gridlines to the chart area.

Display gridlines for a chart's axis.

Display gridlines for a chart's axis.

10. Format the line chart as described below:

a. Apply a solid fill using the Blue, Accent 5, Lighter 80% fill color to the chart area.

b. Apply the Arial font and the Blue, Accent 5 font color to the chart title.

Change the fill color of a chart area.

Apply a font style to the chart title.

Change the font color of the chart title.

11. 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. Update the Package Contribution to Annual Income: 2019 – 2024 stacked column 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.)

Remove a data series from a chart.

12. In the stacked column chart, format the chart legend as described below:

a. Apply a Shape Fill using the White, Background 1 fill color.

b. Apply a Solid Line border with a Blue, Accent 1 border color.
c. Apply a Shadow Shape Effect from the Outer section using the Offset Diagonal Bottom Right option. (Hint: Depending on your version of Office, this may be displayed as “Offset: Bottom Right”.)

Change the fill color of a chart object.

Add a border to a chart element.

Apply a shadow effect to a chart object.

13. For his loan application, Casey needs to create a 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. Select the range A4:G10 and create a Custom Combination Combo chart as described below:

a. Represent the following data series as a Clustered Column chart: Open Desk - Visitor, Open Desk - Regular, Dedicated Desk, Dedicated Office, and Meeting Room.

b. Represent the Total data series as a Line chart using the Secondary Axis,as shown in Figure 1 below.

c. Move the combo chart to the Income Overview worksheet, and then resize and reposition the chart so that the upper-left corner is located within cell A4 and the lower-right corner is located within cell K23.
d. Enter Package and Total Annual Income as the chart title.

e. Add axis titles to the chart, then enter Package Income as the left vertical axis title, and then enter Total Income as the right vertical axis title. Finally, delete the horizontal axis title placeholder.

Insert, resize, and reposition a chart.

Insert a chart title.

Add an axis title to a chart.

14. Casey wants to calculate the monthly payments for each loan option that he is considering. Switch to the Loan Optionsworksheet. In cell B12, create a formula using the PMT function to calculate the monthly payments for loan Option A. Use the values in cells B8, B10, and B5 for the Rate, Nper, and Pv arguments, respectively, and do not enter any values for the optional arguments. Copy the formula you created in cell B12into the range C12:D12. 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.

Create a formula using a function.

Copy a formula into a range.