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”.
o
•
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.
o
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:
2.
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.
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:H10 based on the data in the range B5:G10, and then apply the
Green, Accent 6, Darker 25% sparkline color.
New Perspectives Excel 2016 | Module 4: SAM Project 1a
3.
Apply a Solid Fill, Green Data Bar conditional formatting rule into the range
I5:I10.
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.
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.
6.
In the 3-D pie chart, add data labels to 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 Value data
label option.)
c.
The data label should use the Percentage number format with 1 decimal
place.
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.
8.
In the line chart, modify the Minimum bounds of the vertical axis to be
150000.
9.
Update the line chart by adding Primary Major Horizontal gridlines and Primary
Major Vertical gridlines to the chart area.
10.
Format the line chart as described below:
11.
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.
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.)
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.
New Perspectives Excel 2016 | Module 4: SAM Project 1a
c.
13.
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”.)
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.
Figure 1: Combo Chart Setup
14.
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.
Casey wants to calculate the monthly payments for each loan option that he is
considering.
Switch to the Loan Options worksheet. In cell B12, create a formula using the
PMT function to calculate the monthly payments for loan Option A. Use the
New Perspectives Excel 2016 | Module 4: SAM Project 1a
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 B12 into 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.
Final Figure 1: Average Usage 2024 Worksheet (Range A1:K24)
New Perspectives Excel 2016 | Module 4: SAM Project 1a
Final Figure 2: Annual Income Worksheet
Final Figure 3: Income Overview Worksheet
New Perspectives Excel 2016 | Module 4: SAM Project 1a
Final Figure 4: Loan Options Worksheet
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t
SAM website.
New Perspectives Excel 2016 | Module 4: SAM Project 1a
Inception Workspace
ANALYZING AND CHARTING FINANCIAL DATA
Keshawn Harris
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.
Inception Workspace
Average Usage by Member in 2024
Member ID
AP6052
AP9617
AP5409
AP9034
AP9673
AP3708
AP7235
AP9499
AP5853
AP8225
AP8617
AP7315
AP7187
AP7346
AP5408
AP8150
AP6068
AP3706
AP7867
AP1744
AP9764
AP8020
AP7310
AP8013
AP1073
AP7539
AP9822
AP8141
AP5479
AP2139
AP4738
AP3375
AP7159
AP5946
AP9018
AP8745
AP8387
AP9710
AP3004
AP7921
Average Weekly Use (in Hours)
32
30
29
60
63
30
22
29
28
25
21
33
35
28
22
33
22
29
20
58
26
66
43
67
26
26
31
20
59
27
25
53
26
35
45
26
31
30
54
21
AP2991
AP4249
AP6245
AP6366
AP3412
AP9045
AP4941
AP6955
AP9774
AP5881
AP2901
AP4991
AP6385
AP9983
AP3729
AP1638
AP1370
AP9587
AP6078
AP2277
AP5423
AP5081
AP5639
AP5798
AP5687
AP8182
AP8928
AP1683
AP4718
AP6499
AP4399
AP7664
AP2039
AP9236
AP5166
AP4551
AP6687
AP7563
AP6280
AP6962
AP4455
AP5044
AP5563
AP7611
AP6187
AP4610
AP7490
28
34
65
27
61
23
24
66
20
18
23
35
17
54
74
25
39
39
29
42
35
22
46
60
23
35
56
67
20
35
55
29
39
66
35
26
25
32
31
20
75
32
27
19
35
18
25
AP3932
AP1649
AP5731
AP9211
AP9265
AP7827
AP6294
AP5888
AP2582
AP1694
AP7994
AP9314
AP6933
AP3911
AP4158
AP5437
AP3544
AP7606
AP3605
AP2310
AP9186
AP8389
AP7334
AP3722
AP6218
AP8893
AP9588
AP9792
AP9082
AP8252
AP7135
AP5943
AP5617
AP7919
AP1533
AP9070
AP2576
AP9382
AP4175
AP5800
AP3423
AP4025
AP4826
AP6780
AP3896
AP6432
AP1361
44
60
30
66
25
53
21
23
23
28
46
60
54
70
25
55
33
49
33
30
24
23
24
34
30
66
37
34
22
33
62
19
75
65
70
28
42
59
73
48
39
30
27
73
30
69
28
AP6489
AP7445
AP2715
AP4030
AP3808
AP1255
AP5549
AP9424
AP1730
AP2546
AP5015
AP4720
AP6930
AP7975
AP7575
AP8840
AP1675
AP6694
AP9811
AP8990
AP6345
AP3987
AP1884
AP7749
AP5679
AP6722
AP8408
AP1815
AP8696
AP5274
AP3290
AP1621
AP6782
AP4375
AP3144
AP4047
AP2354
AP6824
AP2492
AP8730
AP3677
AP9409
AP3283
AP5847
AP9115
AP8541
AP5000
25
49
59
27
26
21
20
22
31
17
26
61
30
64
18
42
33
21
54
26
53
64
55
34
28
48
41
71
25
23
17
26
27
48
27
69
25
51
48
16
23
50
58
75
26
56
37
AP5072
AP6975
AP5446
AP8451
AP4579
AP9850
AP8645
AP7938
AP9657
AP3100
AP7834
AP3077
AP7757
AP8230
AP2430
AP2464
AP5642
AP4063
AP2785
AP3723
AP7150
AP8640
AP1500
AP3417
AP2844
AP1586
AP6204
AP4191
AP8624
AP3607
AP9905
AP1879
AP3749
AP3208
AP4084
AP8429
AP7839
AP8082
AP4015
AP3800
AP1131
AP5663
AP8079
AP7405
AP1724
AP8109
AP9173
22
65
32
45
28
34
32
28
19
70
15
32
33
23
44
25
29
47
30
35
32
23
34
22
27
64
67
30
30
24
75
74
23
38
26
23
24
26
58
70
56
27
35
24
34
59
46
AP2743
AP1303
AP4093
AP8026
AP9037
AP1738
AP5907
AP4497
AP4550
AP2228
AP9291
41
58
53
66
55
34
69
35
49
23
57
Inception Workspace
Total Annual Income: 2019 - 2024
Membership Package
2019
Open Desk - Visitor
Open Desk - Regular
Dedicated Desk
Dedicated Office
Meeting Room
Total
$
$
$
$
$
$
54,000
86,400
21,600
28,800
190,800
2020
$
$
$
$
$
$
2021
62,640
120,960
27,000
43,200
253,800
$
$
$
$
$
$
2022
67,500
130,500
28,125
60,000
41,700
327,825
$
$
$
$
$
$
65,520
117,000
40,950
46,800
58,650
328,920
2023
$
$
$
$
$
$
69,660
145,800
42,525
48,600
73,200
379,785
Total Annual Income: 2019 – 2024
$500,000
$450,000
$400,000
$350,000
$300,000
$250,000
$200,000
$150,000
$100,000
$50,000
$1
2
3
4
Package Contribution to Annual Income: 2019
Open Desk - Visitor
Open Desk - Regular
Dedicated Desk
Dedicated Office
$1,000,000
$900,000
$800,000
$700,000
$600,000
$500,000
$400,000
$300,000
$200,000
$100,000
$2019
2020
2021
2022
2019
2020
2021
2022
kspace
2024
2024
$
$
$
$
$
$
Trends
68,475
158,400
43,313
82,500
78,877
431,565
Total
$
$
$
$
$
$
387,795
759,060
203,513
309,900
252,427
1,912,695
2024
2024 Total Annual Income by Package
Open Desk - Visit
Open Desk - Regu
Dedicated Desk
Dedicated Office
Meeting Room
5
6
ncome: 2019 – 2024
dicated Office
Meeting Room
2023
Total
2024
2023
2024
me by Package
Open Desk - Visitor
Open Desk - Regular
Dedicated Desk
Dedicated Office
Meeting Room
Inception Workspace
Annual Income Overview: 2019 - 2024
ce
Inception Workspace
Business Loan Request
Loan Amount
Annual Interest Rate
Payments per Year
Interest Rate per Period
Number of Years
Total Payments
Monthly Payments
Annual Payments
Option A
$
$
300,000
8.00%
12
0.67%
10
120
-
Option B
Option C
$
300,000 $
350,000
6.57%
5.99%
12
12
0.55%
0.50%
15
20
180
240
$
-
$
-
Purchase answer to see full
attachment