Monroe College Excel Charts and Reports Worksheet

User Generated

npr44

Computer Science

Monroe College

Description

Save work in excel file.

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello Buddy,All the changes has been implemented

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
AP9...


Anonymous
Great! Studypool always delivers quality work.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags