sam capstone project 1a excel modules 1 4

Modules 1-4 - SAM Capstone Project 1a

Description

Topics covered in this video:

• Carmelo Miraglia is an executive consultant at Tallridge Regional Medical Center. Carmelo is building a workbook that summarizes income and expenses for the organization. Change the theme of the workbook to Office.
• On the Departments worksheet, change the width of columns C through H to 12.00 characters.
• Change the height of row 1 to 21 points.
• Merge and center the contents of the range A2:J2.
• Format the merged range A2:J2 as described below: a. Apply the 40% - Accent 5 cell style. b. Apply bold formatting. c. Change the font size to 14.
• Enter the values shown in Table 1 into the corresponding cells in the range A5:E5. Table 1: Data for the Range A5:E5

A B C D E 5 Abbreviation Department MD RN Support

• Format the range A5:J5 as described below: a. Center cell contents. b. Change the font size to 11 pt. c. Change the background color to Blue, Accent 5, Lighter 40% (9th column, 4th row of the Theme Colors palette). d. Apply Wrap Text to the text in each cell.
• Select the range A6:J14 and then add a White, Background 1 border to all sides of each cell.
• Select the range A14:J14 and then add a thin top border to each cell using the Automatic color.
• Select the range H6:H13 and then format the range as described below: a. Format the range with the Percentage number format with zero decimal places. b. Add a Highlight Cells conditional formatting rule that formats cells that are greater than 0.24 as light red fill with dark red text.
• Select the range I6:I13 and then use conditional formatting to add solid orange data bars.
• Select the range J6:J13 and then add top/bottom conditional formatting rules to format the top 10% of values as green fill with dark green text and the bottom 10% of values as light red fill with dark red text.
• Enter a formula in cell M7 using the VLOOKUP function to find an exact match for the department abbreviation. Look up the department abbreviation (cell M6) using an absolute reference, search the staff table data (the range A6:J13) using an absolute reference, and return the department name (the 2nd column).
• Copy the formula in cell M7 to the range M8:M15, and edit the copied formulas to return the value from the column indicated by the label in column L.
• In cell C17, enter a formula using the TODAY function that displays the current date.
• Delete column O.
• Hide row 18.
• On the Budget worksheet, create a 2-D pie chart based on the non-adjacent range A5:A12 and F5:F12. Modify the chart as described below: a. Resize and reposition the chart so that the upper-left corner is located within cell H4 and the lower-right corner is located within cell O22. b. Apply Chart Style 3 to the chart. c. Enter 2022 Budget by Department as the chart title.
• In the 2018 Budget by Department 2-D pie chart (located in the range H23:O43), make the following changes: a. Change the data labels to display only the percentage and a label position of Center. (Hint: Select Reset Label Text to remove category names) b. Reposition the legend on the right side of the chart.
• Update the Departmental Budget Comparison 2018-2022 combo chart in the range A15:G36 as follows: a. Change the minimum bound of the right vertical axis to 40,000,000. b. Add axis titles to the chart. Use Department Budgets as the left vertical title, use Total Budget as the right vertical title, and remove the horizontal axis title. c. Apply a shape fill to the chart using the Blue, Accent 5, Lighter 80% fill color.
• Delete the Hiring worksheet.

Support 💖 Buy me a coffee: https://www.buymeacoffee.com/taandar Join Patreon: https://www.patreon.com/Taandar

#cengage #solutions #sam #excel

Disclaimer: Our videos are intended solely for educational purposes, with the goal of assisting students in understanding how to solve problems in various courses. We want to emphasize that any attempt to copy or replicate the solutions shown in these videos may be regarded as a breach of academic integrity policies at your college or university. We strongly recommend that you use these videos as a guide and as a tool to support your learning, but we urge you to complete your assignments independently and refrain from cheating or plagiarism. Please be aware that we are not liable for any consequences resulting from the misuse of these videos in violation of academic integrity policies.

1 00:00:00,000 --> 00:00:04,980 so this is Capstone project 1A and step

2 00:00:03,179 --> 00:00:06,720 1 it is saying

This is member-only video. Contact [email protected] if you are confused.

Recommended Books

Reading books is a great way to learn. Here are some of the books we recommend.

• VIEW ALL TOPICS
• Art & Design
• Articles & News Stories
• Arts & Architecture
• Arts & Ideas
• Automobiles
• Biography & Memoir
• Book Excerpts
• Books - Fiction
• Books - Non-fiction
• Business & Economics
• Business & Leadership
• Business/Law
• Children's Literature
• Comic Fiction & Satire
• Computers & Technology
• Contemporary Fiction
• Contemporary Women
• Cooking & Food
• Corporate Finance
• Court Filings
• Court Records
• Creative Writing
• Criminal Procedure
• Current Economy
• Diet & Nutrition
• Economic Conditions
• Economic History & Theory
• Emigration & Immigration Studies
• Environmental Economics
• Essays & Theses
• Ethnic & Minority Studies
• Ethnicity, Race & Gender
• Faith & Spirituality
• Family Sagas
• Fan Fiction
• Fiction & Literature
• Food & Wine
• Games & Puzzles
• Genre Fiction
• Government & Politics
• Government Documents
• Graphic Art
• Health & Lifestyle
• Health & Medicine
• Health & Wellness
• History, Criticism & Theory
• Information Technology & Theory
• Instruction manuals
• Internet & Technology
• Legal forms
• Magazines/Newspapers
• Login / Register
• Science Fiction & Fantasy

Illustrated Excel 2016 | Modules 1–4: SAM Capstone Project 1a (solved solution)

Share & Embed

Sponsor Documents

Recommended.

Illustrated Excel 2016 | Modules 5–8: SAM Capstone Project 1a (completed solution)

Illustrated Excel 2016 | Module 5: SAM Project 1a (completed solution)

Illustrated Excel 2016 | Module 7: SAM Project 1a (completed solution)

Illustrated Excel 2016 | Module 6: SAM Project 1a (completed solution)

Illustrated Excel 2016 | Module 8: SAM Project 1a (completed solution)

Illustrated PowerPoint 2016 | Modules 1–4: SAM Capstone Project 1a (completed solution)

Illustrated PowerPoint 2016 | Module 1: SAM Project 1a (completed solution)

CMPTR Excel 2016 | Chapter 15: SAM Project 1b (completed solution)

New Perspectives Word 2016 | Module 4: SAM Project 1a (solved solution)

New Perspectives Excel 2016 | Module 9: SAM Project 1a (completed solution)

Excel 2013 Capstone Project EX-3 (completed solution)

New Perspectives Word 2016 | Module 3: SAM Project 1a (completed solution)

New Perspectives Word 2016 | Module 2: SAM Project 1a (completed solution)

New Perspectives Excel 2016 | Module 10: SAM Project 1b (completed solution)

Capstone Project

capstone project

Facebook Google Twitter

Password Hide

Remember me

Forgot your password?

I agree to the Terms

Lost your password? Please enter your email address. You will receive a link to create a new password.

Back to log-in

New User? Register

Fill This Form To Receive Instant Help

Homework answers / question archive / Illustrated Excel 2019 | Modules 1-4: SAM Capstone Project 1b Youngwood Insurance CREATE AND FORMAT A FINANCIAL ANALYSIS GETTING STARTED Open the file IL_EX19_CS1-4b_FirstLastName_1

Illustrated Excel 2019 | Modules 1-4: SAM Capstone Project 1b Youngwood Insurance CREATE AND FORMAT A FINANCIAL ANALYSIS GETTING STARTED Open the file IL_EX19_CS1-4b_FirstLastName_1

Share With -->

Illustrated Excel 2019 | Modules 1-4: SAM Capstone Project 1b

Youngwood Insurance

CREATE AND FORMAT A FINANCIAL ANALYSIS

• Open the file IL_EX19_CS1-4b_ FirstLastName _1.xlsx , available for download from the SAM website.
• 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.
• If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• PROJECT STEPS
• Yuki Lo is a financial manager for Youngwood Insurance, a national insurance company. In an Excel workbook, he has been tracking income and expenses along with client data for the office in Portland, Oregon. He has asked you to help him complete the workbook and insert new charts. Go to the Portland Office worksheet. In cell K2, insert a formula using the TODAY function to display today's date.
• Fill the range D5:F5 with a series based on the value in cell C5 to provide the missing month names.
• Merge and center the contents of the range A5:A18.
• Rotate the text in the merged cell up so it reads from bottom to top.
• Middle align the merged cell.
• Resize column A to a width of 5.00 .
• Use AutoFit to resize column B to its best fit to display all the text in the column.
• In cell C8, enter 64,500 as the Life insurance income for January.
• In cell C9, use the SUM function to total the January Income values (the range C6:C8 ).
• Copy the formula in cell C9 to the range D9:F9 and to cell H9 to complete the totals.
• Format the nonadjacent ranges C14:F17 and H14:H17 using the Comma Style format and no decimal places to match the formatting of the Income data.
• In cell C25, enter a formula using the MIN function to display the lowest revenue (the range C6:F8 ).
• In cell C26, enter a formula using the MAX function to display the highest revenue (the range C6:F8 ).
• Apply Outside Borders to the range B25:C26 using Blue, Accent 2 (1st row, 6th column of the Theme Colors palette) as the border color to show the information belongs together.
• Switch the rows and columns to display expenses by type.
• Display the legend at the top of the chart.
• Add Expense Amounts as the primary vertical axis title.
• Add Portland Monthly Expenses as the chart title.
• Change the fill color of the April data series to Green, Accent 4 .
• Add a chart border using the Blue, Accent 2 shape outline color.
• Create a doughnut chart based on the range C22:F23.
• Resize and reposition the chart so that its upper-left corner is within cell J21 and its lower-right corner is within cell P34.
• Enter Quarter 1 Profit as the chart title.
• Apply Layout 1 to the chart to display percentages on each part of the doughnut and no legend.
• Create a Stacked Column chart based on the range B5:F8.
• Move the chart to a new sheet, using Quarter 1 Income as the name of the new sheet.
• Change the chart style to Style 7 to match the column chart on the Portland Office worksheet.
• Change the font size of all the chart text to 12 point to make it easier to read.
• Remove the chart title since the sheet tab indicates the purpose of the chart.
• Format the values in the vertical axis using the Accounting number format with no decimal places to clarify the values are dollar amounts.
• Add the default data labels to the chart to display the revenue values.
• Remove the legend to allow more room for the chart data.
• Go to the Portland Office worksheet. In cell G6, insert a Line sparkline based on the data in the range C6:F6.
• Include markers in the sparkline, and then change the marker color to Turquoise, Accent 3 .
• Copy cell G6, and then paste it in the range G7:G9, the range G13:G18, and cell G23.
• In cell E6, enter a formula without using a function that subtracts the start date for the client (cell D6 ) from the current date (cell C3 ) and divides the result by 365.25 , the number of days in a year, accounting for leap year. Use an absolute reference to cell C3 in the formula.
• Display the value in cell E6 with one decimal place.
• Fill the range E7:E19 with the formula in cell E6.
• In cell H6, enter a formula using the IF function that tests whether the number of years (cell E6 ) is greater than or equal to 5 . If it is, display "Y" in cell H6. If it is not, display "N" in cell H6.
• Fill the range H7:H19 with the formula in cell H6.
• In cell I6, enter a formula using the AND function that tests whether the Business value (cell F6 ) is equal to "Y" and whether the Discount value (cell H6 ) is equal to "Y" .
• Fill the range I7:I19 with the formula in cell I6.
• In cell J6, enter a formula using the OR function that tests whether the region (cell C6 ) is equal to "S" or whether the policy type (cell G6 ) is equal to "AHL" .
• Fill the range J7:J19 with the formula in cell J6.
• Yuki wants to display the total number of clients who signed policies in April. In cell M5, enter a formula using the COUNTA function to count the client IDs (the range B6:B19 ).
• He also wants to determine the average number of years clients have been with Youngwood Insurance. In cell M6, enter a formula using the AVERAGE function to average the number of years (the range E6:E19 ).

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.

Fill The Details Below To Receive Instant

Homework help, purchase a new answer, custom new solution created by our subject matter experts, get a quote, low cost option, download this past answer in few clicks, purchase solution.

Already member? Sign In

GET ANSWER TO THIS QUESTION

100% UNIQUE SOLUTION WITHIN FEW HOURS

Related Questions

• New Perspectives Excel 2019 Chapter 1Textbook Project Chapter 2Textbook Project Chapter 3 Textbook Project Chapter 4 Textbook Project Chapter 5Textbook Project Chapter 6Textbook Project Chapter 7Textbook Project Chapter 8Textbook Project Chapter 9Textbook Project Chapter 10Textbook Project Chapter 11 Textbook Project Chapter 12Textbook Project QC Inn To summarize two years of financial data on 24 QC Inn motels located in the NebraskaSouth Dakota region To analyze revenue and royalties from 24 QC Inn franchises in NebraskaSouth Dakota during 2021 Primrose Community Clinic To monitor the progress of the Primrose Community Clinic fundraising campaign Note This question only contains solution to chapter 11 and chapter 12 projects and cost total 39
• Shelly Cashman PowerPoint 2019 Module 5 SAM Project 1b Publishing with Fir Tree Books Working with advanced animation formatting and collaboration GETTING STARTED Open the file SCPPT195bFirstLastName1
• Illustrated Excel 2016 Modules 1ndash4 SAM Capstone Project 1a Merchandise Projections Formatting formulas and charts GETTING STARTED Open the file ILEX16CS14aFirstLastName1
• New Perspectives Excel 2019 Module 1 End of Module Project 2 Deepak Singh 1
• Shelly Cashman Excel 2019 Module 10 SAM Critical Thinking Project 1c Franklin National ANALYZE DATA WITH POWER TOOLS AND CREATE MACROS GETTING STARTED Open the file SCEX19CT10cFirstLastName1
• Shelly Cashman Excel 2016 Module 7 SAM Project 1b J Smith Events Importing Data and working with SmartArt Images and Screenshots GETTING STARTED Open the fileSCEX167bFirstLastName1
• New Perspectives Excel 2016 Module 1 SAM Project 1a Pepitorsquos Supermarkets getting started with excel GETTING STARTED Open the fileNPEX161aFirstLastName1
• New Perspectives Excel 2019 Module 4 SAM Project 1a Alanis Parks Department ANALYZE AND CHART FINANCIAL DATA 1
• New PerspectivesExcel 2016 Module 2 SAM Project 1b Kellyrsquos Telemarketing formatting workbook text and data GETTING STARTED Open the fileNPEX162bFirstLastName1

Help in Homework

• Don't have an account. Register Now
• Forgot Password?. Reset Password

Please enter your email address and we'll send you instructions on how to reset your password

• Already have an account? Log In

Excel 2019 Capstone 1-3 Project A

• Solved Assignments
• Request Solution
• Perdisco Request
• Make Payment

Tallridge Regional Medical Center

Formatting, Formulas, and Charts

     GETTING STARTED

• Open the file NP_EX19_CS1-4a_ FirstLastName xlsx , available for download from the SAM website.
• 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.
• If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

• Carmelo Miraglia is an executive consultant at Tallridge Regional Medical Center. Carmelo is building a workbook that summarizes income and expenses for the organization. Change the theme of the workbook to Office .
• On the Departments worksheet, change the width of columns C through H to 00 characters.
• Change the height of row 1 to 21
• Merge and center the contents of the range A2:J2.
• Apply the 40% – Accent 5 cell style.
• Change the font size to 14 .
• Enter the values shown in Table 1 into the corresponding cells in the range A5:E5.

Table 1: Data for the Range A5:E5

• Center cell contents.
• Change the font size to 11
• Change the background color to Blue, Accent 5, Lighter 40% (9th column, 4th row of the Theme Colors palette).
• Apply Wrap Text to the text in each cell.
• Select the range A6:J14 and then add a White, Background 1 border to all sides of each cell.
• Select the range A14:J14 and then add a thin top border to each cell using the Automatic color.
• Format the range with the Percentage number format with zero decimal places.
• Add a Highlight Cells conditional formatting rule that formats cells that are greater than 24 as light red fill with dark red text.
• Select the range I6:I13 and then use conditional formatting to add solid orange data bars.
• Select the range J6:J13 and then add top/bottom conditional formatting rules to format the top 10% of values as green fill with dark green text and the bottom 10% of values as light red fill with dark red text.
• Enter a formula in cell M7 using the VLOOKUP function to find an exact match for the department abbreviation. Look up the department abbreviation (cell M6 ) using an absolute reference, search the staff table data (the range A6:J13 ) using an absolute reference, and return the department name (the 2 nd column).
• Copy the formula in cell M7 to the range M8:M15, and edit the copied formulas to return the value from the column indicated by the label in column L.
• In cell C17, enter a formula using the TODAY function that displays the current date.
• Delete column O.
• Hide row 18.
• Resize and reposition the chart so that the upper-left corner is located within cell H4 and the lower-right corner is located within cell O22.
• Apply Chart Style 3 to the chart.
• Enter 2022 Budget by Department as the chart title.
• Change the data labels to display only the percentage and a label position of Center. ( Hint : Select Reset Label Text to remove category names)
• Reposition the legend on the right side of the chart.
• Change the minimum bound of the right vertical axis to 40,000,000 .
• Add axis titles to the chart. Use Department Budgets as the left vertical title, use Total Budget as the right vertical title, and remove the horizontal axis title.
• Apply a shape fill to the chart using the Blue, Accent 5, Lighter 80% fill color.
• Delete the Hiring

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: Departments Worksheet

Final Figure 2: Budget Worksheet

Click on Buy Solution and make payment. All prices shown above are in USD . Payment supported in all currencies . Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).

After making payment, solution is available instantly .Solution is available either in Word or Excel format unless otherwise specified.

If your question is slightly different from the above question, please contact us at [email protected] with your version of question.

Choose your currency:

• USD US Dollars ($)

Pardon Our Interruption

As you were browsing something about your browser made us think you were a bot. There are a few reasons this might happen:

• You've disabled JavaScript in your web browser.
• You're a power user moving through this website with super-human speed.
• You've disabled cookies in your web browser.
• A third-party browser plugin, such as Ghostery or NoScript, is preventing JavaScript from running. Additional information is available in this support article .

To regain access, please make sure that cookies and JavaScript are enabled before reloading the page.