a. PROJECT STEP 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 D6:E11 and insert a Clustered Column chart. Modify the chart as described below: Resize and reposition the chart so that the upper-left corner is located within cell D13 and the lower-right corner is located within cell J28. b. Enter Average Weekly Usage (in Hours) in 2024 as the chart title. Format the series by changing the Series Overlap to 0% and the Gap Width to 10%. d. Change the bin width used in the chart by setting cell E4 to 10. Change the lower bounds of the chart by setting cell F6 to 15. 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 c. e. 3. 4. a. Switch to the Annual Income worksheet. Insert Column sparklines into the range H5:H10 based on the data in the range B5:10, and then apply the Green, Accent 6, Darker 25% sparkline color. Insert Solid fill, Green Data Bars into the range 15:110. 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: 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. Apply the Style 6 chart style. 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. In the 3-D pie chart, add data labels to the chart using the following options: 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 and Show leader lines data label options.) The data label should use the Percentage number format with 1 decimal place. C. 5. 6. a. C. a. 7. Update the Total Annual Income: 2019-2024 line chart in the range A11:26 by editing the Horizontal (Category) Axis labels to display using the values in the range B4:44 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: 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. 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:144) 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: Apply a Shape Fill using the White, Background 1 fill color. a. a. C. b. Apply a Solid Line border with a Blue, Accent 1 border color. c. Apply a Shadow Shape Effect using the Offset Diagonal Bottom right option. (Hint: Depending on your version of Office, this may be displayed as Offset: Bottom Right.) 13. For his loan application, Casey needs to create a chart that displays the annual income generated by each membership. Select the range A4:49 and create a Clustered Column chart b. Move the 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. Enter Package Annual Income as the chart tile. d. Add a vertical axis title to the chart, and then enter Package Income as the vertical axis title. 14. 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 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:012. 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. D G H Inception Workspace Average Usage by Member in 2024 Average Weekly Use fin Hours Bin Width 32 30 29 60 63 30 22 29 28 25 21 10,20 120,40) (40,60) 160, 80) 180, 1001 1100, 1201 20 Avg. Weekly Use (in Hours) 11 142 47 39 0 0 D 20 40 60 80 100 Hours 20 40 60 BO 100 120 Chart Title 160 140 2 3 4 Memberi 5 AP6052 6 AP9617 7 AP5409 8 AP9034 9 AP9673 10 AP3708 11 AP7235 12 AP9499 13 AP5853 14 AP8225 15 APR617 15 AP7315 17 AP7187 18 AP7346 19 AP5408 20 AP8150 21 AP606B 22 AP3706 23 AP7867 24 AP 1744 25 AP9764 26 AP8020 27 AP7310 28 AP8013 29 AP1073 30 AP7539 31 AP9822 -32 AP8141 33. AP5479 120 100 30 .. 60 35 28 22 33 22 29 20 58 26 66 43 67 26 26 31 20 59 392 40 20 0 10. 201 120.40 140,60 160.19 1. 1001 1100 1201 Documentation Average Usage 2024 Annual Income Income Overview Loan Options + ha C M N N 0 0 Inception Workspace 1 2 Total Anncom 2019 2024 2011 3921 PEO trene Teal SI $ $ S. SOS SO 11800 73.000 2031 2021 14.000 87,100 50 1.400 120.000 110,100 317,000 21.00 27.000 2.1353 21.00 3.2005 60,000 16.00 3 1.000 100's 53,2005 3 Membership Open to Opera Dedik #DOC Menom 10 TM 11 12 13 SEE 5 5 3 10,100 42. 11.600 00 7. TIS . 82.300 T. ESE 5 5. ' 5 DEZE WEE 5 1912.05 Total Annual Income: 2019-2024 2024 Total Annual income by Package 001991 S. 15 16 11 18 19 20 21 22 23 533000 S2000 11 3 ARA 20 27 Package Contribution to Annual Income: 2019-2024 30 31 Income Overview Loan Option Annual income Documentation Average Usage 20124 B C D F G H 1 K M N O Inception Workspace 2 Annual Income Overview: 2019-2024 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Documentation Average Usage 2024 Annual Income Income Overview Loan Options + Ready B D G H Inception Workspace Business Loan Request Option A S Option Optionc 300,000 $ 300,000 $ 350,000 8.00% 6.57% 5.99% 12 12 12 0.67% 0.55% 0.50% 10 15 20 120 180 240 $ $ 2 3 4 5 Loan Amount 6 Annual Interest Rate 7 Payments per Year 8 Interest Rate per Period 9 Number of Years 10 Total Payments 11 12 Monthly Payments 13 Annual Payments 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Documentation Average Usage 2024 Annual Income Income Overview Loan Options Ready a. PROJECT STEP 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 D6:E11 and insert a Clustered Column chart. Modify the chart as described below: Resize and reposition the chart so that the upper-left corner is located within cell D13 and the lower-right corner is located within cell J28. b. Enter Average Weekly Usage (in Hours) in 2024 as the chart title. Format the series by changing the Series Overlap to 0% and the Gap Width to 10%. d. Change the bin width used in the chart by setting cell E4 to 10. Change the lower bounds of the chart by setting cell F6 to 15. 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 c. e. 3. 4. a. Switch to the Annual Income worksheet. Insert Column sparklines into the range H5:H10 based on the data in the range B5:10, and then apply the Green, Accent 6, Darker 25% sparkline color. Insert Solid fill, Green Data Bars into the range 15:110. 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: 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. Apply the Style 6 chart style. 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. In the 3-D pie chart, add data labels to the chart using the following options: 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 and Show leader lines data label options.) The data label should use the Percentage number format with 1 decimal place. C. 5. 6. a. C. a. 7. Update the Total Annual Income: 2019-2024 line chart in the range A11:26 by editing the Horizontal (Category) Axis labels to display using the values in the range B4:44 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: 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. 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:144) 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: Apply a Shape Fill using the White, Background 1 fill color. a. a. C. b. Apply a Solid Line border with a Blue, Accent 1 border color. c. Apply a Shadow Shape Effect using the Offset Diagonal Bottom right option. (Hint: Depending on your version of Office, this may be displayed as Offset: Bottom Right.) 13. For his loan application, Casey needs to create a chart that displays the annual income generated by each membership. Select the range A4:49 and create a Clustered Column chart b. Move the 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. Enter Package Annual Income as the chart tile. d. Add a vertical axis title to the chart, and then enter Package Income as the vertical axis title. 14. 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 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:012. 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. D G H Inception Workspace Average Usage by Member in 2024 Average Weekly Use fin Hours Bin Width 32 30 29 60 63 30 22 29 28 25 21 10,20 120,40) (40,60) 160, 80) 180, 1001 1100, 1201 20 Avg. Weekly Use (in Hours) 11 142 47 39 0 0 D 20 40 60 80 100 Hours 20 40 60 BO 100 120 Chart Title 160 140 2 3 4 Memberi 5 AP6052 6 AP9617 7 AP5409 8 AP9034 9 AP9673 10 AP3708 11 AP7235 12 AP9499 13 AP5853 14 AP8225 15 APR617 15 AP7315 17 AP7187 18 AP7346 19 AP5408 20 AP8150 21 AP606B 22 AP3706 23 AP7867 24 AP 1744 25 AP9764 26 AP8020 27 AP7310 28 AP8013 29 AP1073 30 AP7539 31 AP9822 -32 AP8141 33. AP5479 120 100 30 .. 60 35 28 22 33 22 29 20 58 26 66 43 67 26 26 31 20 59 392 40 20 0 10. 201 120.40 140,60 160.19 1. 1001 1100 1201 Documentation Average Usage 2024 Annual Income Income Overview Loan Options + ha C M N N 0 0 Inception Workspace 1 2 Total Anncom 2019 2024 2011 3921 PEO trene Teal SI $ $ S. SOS SO 11800 73.000 2031 2021 14.000 87,100 50 1.400 120.000 110,100 317,000 21.00 27.000 2.1353 21.00 3.2005 60,000 16.00 3 1.000 100's 53,2005 3 Membership Open to Opera Dedik #DOC Menom 10 TM 11 12 13 SEE 5 5 3 10,100 42. 11.600 00 7. TIS . 82.300 T. ESE 5 5. ' 5 DEZE WEE 5 1912.05 Total Annual Income: 2019-2024 2024 Total Annual income by Package 001991 S. 15 16 11 18 19 20 21 22 23 533000 S2000 11 3 ARA 20 27 Package Contribution to Annual Income: 2019-2024 30 31 Income Overview Loan Option Annual income Documentation Average Usage 20124 B C D F G H 1 K M N O Inception Workspace 2 Annual Income Overview: 2019-2024 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Documentation Average Usage 2024 Annual Income Income Overview Loan Options + Ready B D G H Inception Workspace Business Loan Request Option A S Option Optionc 300,000 $ 300,000 $ 350,000 8.00% 6.57% 5.99% 12 12 12 0.67% 0.55% 0.50% 10 15 20 120 180 240 $ $ 2 3 4 5 Loan Amount 6 Annual Interest Rate 7 Payments per Year 8 Interest Rate per Period 9 Number of Years 10 Total Payments 11 12 Monthly Payments 13 Annual Payments 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Documentation Average Usage 2024 Annual Income Income Overview Loan Options Ready