excel 2019 modules 1 4 sam capstone project 1a

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.

• For educators
• English (US)
• English (India)
• English (UK)
• Greek Alphabet

Question: Illustrated Excel 2019 | Modules 1-4: SAM Capstone Project 1a Cello Worldwide CREATE AND FORMAT A FINANCIAL ANALYSIS

Cello Worldwide

CREATE AND FORMAT A FINANCIAL ANALYSIS

· Open the file IL_EX19_CS1-4a_ FirstLastName _1.xlsx , available for download from the SAM website.

· Save the file as IL_EX19_CS1-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 IL_EX19_CS1-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. Kamal Haman directs the San Antonio office of Cello Worldwide, a global communications company. He has been tracking revenues and expenses along with customer data in an Excel workbook, including charts to help him visualize the data. He has asked you to help him complete the workbook and insert additional charts. Go to the Revenue & Expenses worksheet. In cell K1, insert a formula using the TODAY function to display today's date.

2. Fill the range D4:F4 with a series based on the value in cell C4 to provide the missing month names.

3. Format the text in cell A4 as follows to make it readable and more meaningful:

a. Merge and center the contents of the range A4:A17.

b. Rotate the text in the merged cell up to 90 degrees so it reads from bottom to top.

c. Middle-align the merged cell.

d. Resize column A to a width of 6.00 .

Not the question you’re looking for?

Post any question and get expert help quickly.

View SAM Project Answer Keys

View the correct answers for SAM projects.

• Click SAM assignments .

• Click the file under Solution File to download it to your computer.
• Open the solution file.

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

We store cookies data for a seamless user experience. To know more check the Privacy Policy

• computer-science
• engineering
• science-math
• Online Tutoring
• AI Essay Writer

• Financial Accounting
• Shelly Cashman Excel 2019 | Modules 4-7: SAM...

Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Victoria Streaming Service · Bao is 1 answer below »

315+ Users Viewed

127+ Downloaded Solutions

Texas, US Mostly Asked From

Victoria Streaming Service

·        Bao is confident that revenue will increase by at least 2 percent per quarter next year. He wants to calculate these revenue goals in a second projection. Project next year's revenue in the second projection (range H9:K9) based on a growth series using 1.02 as the step value.

1 Approved Answer

Do you need an answer to a question different from the above? Ask your question!

(Hide this section if you want to rate later)

Was the final answer of the question wrong?

Were the solution steps not detailed enough?

Was the language and grammar an issue?

Does the question reference wrong data/report or numbers?

Stay Solved :)

Didn't find yours?

Get plagiarism-free solution within 48 hours

Review Please

Help us make our solutions better

Rate this solution on a scale of 1-5 star

Thank you for your feedback.

Related questions, shelly cashman excel 2019 | modules 4-7: sam capstone project 1a victoria....

Understanding the Prompt and Planning the Solution The prompt outlines a comprehensive project involving financial analysis, data consolidation, and visualization for a streaming service company. The key tasks include: Data Consolidation: Combining data from multiple worksheets into a single location. Financial Analysis: Calculating revenue projections, total sales, and other financial metrics. Visualization: Creating charts to...

Victoria Streaming Service CONSOLIDATE DATA AND USE THE FINANCIAL FUNCTIONS, TABLES, AND CHARTS Bao wants to consolidate the sales data in the U.S., Canada , and U.K. worksheets on the All Locations worksheet. Go to the All Locations worksheet, and...

Sonia also wants to display the revenue by branch data as a chart. insert a combo pivotchart based on the new pivottable. display the 2020 revenue and cengage shelly cashman excel 2019 | module 8: end of module project 1 the 2021 revenue data as a..., modules 5-8: sam capstone project 1a project steps benicio cortez is a financial consultant with newsight consulting in denver, colorado. he is working with dig technology, a company that sells five models of portable speakers to consumers in north..., shelly cashman excel 365/2021 | modules 4-7: sam capstone project 1a victoria streaming service consolidate data and use financial functions, tables, and charts getting started • save the file sc_ex365_2021_cs4-7a_firstlastname_1.xlsx as..., recent questions in financial accounting, pincus associates uses the allowance method to account for bad debts. 2024 was the first year of operations for pincus, so it had a $0 opening balance in its allowance for uncollectible accounts. during 2024, pincus provided a total of $297,000 of..., solve and explain the following image transcription text problem 17-17 (laa) at the beginning of current year, marie company purchased 40% of the outstanding ordinary shares of lester company paying p2,560,000 when the carrying amount of the net..., knowledge check 01 on december 31, fantastic tea receives $3,000 cash from don smith, in exchange for common stock. complete the necessary journal entry by selecting the account names from the pull-down menus and entering dollar amounts in the debit..., image transcription text stark company has the following adjusted accounts and normal balances at its december 31 year-end. notes payable $ 30,090 accumulated depreciation-buildings $ 34,060 prepaid insurance 4,400 accounts receivable 7,809 interest..., 1 of 3 question answered step-by-step asked by megamoonsquirrel17 image transcription text identify the fraud triangle risk factor in each situation. 1. salespeople are expected to make unrealistic sales numbers. 2. an employee feels underpaid and..., image transcription text which of the following statements regarding credit entries is true multiple choice 0 credits decrease liability accounts. credits increase asset accounts. credits increase asset and common stock accounts and decrease..., image transcription text imari brown arrived at the following tax information: tax information gross salary $ 36,145 additional small business income (at 26% tax rate) 16,666 interest earnings 265 dividend income 65 standard deduction 12,666 itemized..., image transcription text use the following data to calculate the current ratio. cash accounts receivable inventory short-term investments land (held for future use) land buildings less: accumulated depreciation franchise total assets $338500 (60000)..., image transcription text required: 1. prepare the bank reconciliation for this company as of july 31. bank statement balance add: book balance add adjusted bank balance adjusted book balance the following information is available to reconcile branch..., a local chevrolet dealership carries the following types of vehicles: inventory items quantity unit cost unit nrv vans 2 $18,500 $16,500 trucks 5 16,300 15,300 2-door sedans 1 11,300 13,300 4-door sedans 6 15,300 18,300 sports cars 2 28,500 31,500..., plagiarism checker.

Submit your documents and get free Plagiarism report

Stuck with a Question?

Your solution is just a click away! Get it Now

Couldn't Find What You Were Looking For ?

Get it solved from our top experts within 48hrs!

Good News! We have found the answer to this question!