Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Excel capstone project 1

26/12/2020 Client: saad24vbs Deadline: 7 Days

Illustrated Excel 2016 | Modules 9-16: SAM Capstone Project 1a


C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png Illustrated Excel 2016 | Modules 9-16: SAM Project Capstone 1a


Breeze Smart Home Devices


AUTOMATING AND ANALYZING DATA


GETTING STARTED

Open the file IL_EX16_CS9-16a_FirstLastName_1.xlsm, available for download from the SAM website.


Save the file as IL_EX16_CS9-16a_FirstLastName_2.xlsm by changing the “1” to a “2”.


0. If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.


1. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:


1. Support_IL_EX16_CS9-16a_Orders.accdb


With the file IL_EX16_CS9-16a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.


· If cell B6 does not display your name, delete the file and download a new copy from the SAM website.


If you see a Message Bar with a security warning at the top of the Excel window, click the Enable Content button in the Message Bar to enable the macros contained in the file.


This project requires you to use the Solver and the Analysis ToolPak add-ins. If these add-ins are not available on the Data tab in the Analysis group (or if the Analysis group is not available), click the File tab, and then click the Options button. In the left pane of the Excel Options dialog box, click the Add-Ins option. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. In the Add-Ins dialog box, click the Analysis ToolPak and the Solver Add-In check boxes and then click the OK button. Follow any remaining prompts to install the Analysis ToolPak and Solver.


To complete this project you will need to display the Developer tab. To add this tab to the Excel Ribbon, click the File tab to open Backstage View and then click the Options button. In the Excel Options dialog box, click the Customize Ribbon option and click the Developer check box. Click the OK button to close the Excel Options dialog box and confirm that the Developer tab appears in the Excel Ribbon.


PROJECT STEPS

Will Deyes is the sales manager for Breeze Smart Home Devices, a growing company that sells smart devices such as lights, locks, plugs, and thermostats that can be controlled with a smartphone. Will has asked you to update a workbook to show recent sales data and identify trends. He has reviewed the workbook and inserted comments about its contents.


Switch to the Sales Overview worksheet and respond to a comment as follows:


a. Read Will’s comment in cell C5, and then change the value in cell C16 to $416,000 to respond to Will’s request.


b. At the end of Will’s comment in cell C5, press ENTER, and then add OK, it’s done. (including the period).


In response to a comment, modify the Total Sales by Region chart in the range D1:G12 as follows:


c. Read Will’s comment in cell C10, and then change the chart type to a Clustered Bar chart using the first chart option.


d. Add the data in the range B9:C9 to the chart.


e. Apply the Style 5 chart style to coordinate with other charts in the worksheet.


f. Change the colors of the chart to Monochromatic Palette 5 in the Monochromatic section of the Change Colors gallery. (Hint: Depending on your version of Office, the name of the color may appear as Color 9.)


g. Delete Will’s comment in cell C10.


Modify the axes to identify the data in the bar chart as follows:


h. Add 2019 Sales as the primary horizontal axis title.


i. Display the units on the primary horizontal axis in Thousands.


j. Add Inside End data labels to the bars.


k. Remove the legend from the chart.


To show trends in the quarterly sales data for each region, add sparklines to the worksheet as follows:


l. Add Column Sparklines to the range G15:G21 based on data in the range C15:F21.


m. Apply the Blue, Accent 5 sparkline color (9th column, 1st row of the Theme Colors palette) to coordinate with the colors in the other charts.


n. Display a High Point marker in the sparklines to quickly identify the highest sales quarter for each region.


o. Change the High Point marker color to Orange, Accent 2 (6th column, 1st row of the Theme Colors palette) so that the high points use the orange accent color in the worksheet.


Make the Total Quarterly Sales - 2019 chart in the range I1:O12 easier to interpret as follows:


p. Add Sales as the primary vertical axis title.


q. Increase the font size of the primary vertical axis title to 12 pt.


r. Add Quarters as the primary horizontal axis title.


s. Increase the font size of the primary horizontal axis title to 12 pt.


Will wants to make the Quarterly Sales by Region chart easier to interpret. Format the clustered column chart in the range H14:O27 as follows:


t. Change the chart type to a Stacked Column chart grouped by Quarter.


u. Change the fill color of the Mid-Atlantic data series to Blue in the Standard Colors palette (8th column) to make the data series less prominent.


v. Remove the legend.


w. Add a Data Table With Legend Keys to the chart.


x. Resize the stacked column chart so it covers the gray shading in the range H14:O34.


Switch to the Sales by Product worksheet. Modify the Quarterly Sales Trend clustered column chart in the range C1:F11 so it compares products across all quarters by switching the row and column data.


Modify the column sparklines in the range F15:F19 to make it easier to compare the product sales in each quarter as follows:


y. Change the sparkline type to Line.


z. Apply the Blue, Sparkline Style Dark #5 sparkline style.


aa. Display the High Point and the Low Point in the sparklines.


Modify the 2019 Sales by Product Type chart in the range G1:L19 to make the chart easier to analyze as follows:


ab. Change the chart type to a Combo chart.


ac. Use Clustered Column as the chart type for the Lights, Locks, Plugs, and Thermostats data series.


ad. Use Line as the chart type for the Average data series.


ae. Change the color of the line in the Average data series to Orange, Accent 2 (6th column, 1st row of the Themes Colors palette) to make the line more noticeable.


af. Remove all the gridlines from the chart.


ag. Change the Maximum Bounds of the vertical axis to 900,000.


ah. Add a Linear Trendline based on the Thermostats data series.


ai. Change the weight of the new trendline to 2¼ pt.


aj. Apply the White, Background 1, Darker 15% shape fill color (1st column, 3rd row of the Theme Colors palette) to the Plot area of the chart to separate the plot area from the rest of the chart.


ak. Remove the primary horizontal axis title from the chart.


Switch to the Sales by Rep worksheet. Will created a macro to insert a chart of sales by each sales representative. From the last time he ran the macro, the worksheet displays a chart in the incorrect format (a bar chart). The chart also includes sales data for Harsia and Lewison, but not Sattinger. Will has already updated the macro to produce a column chart instead of a bar chart. Troubleshoot and edit the data range in the macro as follows:


al. Assign the SalesChart macro to the button labeled Chart Sales by Rep.


am. Edit the SalesChart macro by changing the selected range in the following lines of code to include the Sattinger label in cell A8 and sales value in cell C8:


Range("A5:A7,C5:C7").Select


and


ActiveChart.SetSourceData Source:=Range( _ "'Sales by Rep'!$A$5:$A$7,'Sales by Rep'!$C$5:$C$7")


an. Save the changes, then delete the bar chart.


ao. Click the Chart Sales by Rep button to run the updated SalesChart macro to verify the chart includes the information for all three sales reps.


ap. Resize and reposition the column chart so the upper-left corner is in cell E4 and the lower-right corner is in cell K18.


Will also created a macro named RepBonus, which determines which sales reps receive a bonus each quarter. Cell D8 incorrectly indicates Sattinger should not receive a bonus though her sales exceeded the quota. Troubleshoot the RepBonus macro as follows:


aq. Edit the RepBonus macro in the Visual Basic Editor. Correct the following code at the end of the module so that Sattinger will receive a bonus when her sales exceed $450,000:

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

University Coursework Help
Homework Guru
Helping Hand
Top Essay Tutor
Writer Writer Name Offer Chat
University Coursework Help

ONLINE

University Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$112 Chat With Writer
Homework Guru

ONLINE

Homework Guru

Hi dear, I am ready to do your homework in a reasonable price and in a timely manner.

$112 Chat With Writer
Helping Hand

ONLINE

Helping Hand

I am an Academic writer with 10 years of experience. As an Academic writer, my aim is to generate unique content without Plagiarism as per the client’s requirements.

$110 Chat With Writer
Top Essay Tutor

ONLINE

Top Essay Tutor

I have more than 12 years of experience in managing online classes, exams, and quizzes on different websites like; Connect, McGraw-Hill, and Blackboard. I always provide a guarantee to my clients for their grades.

$115 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Field ion microscopy ppt - Http secure adppayroll com au - Www solentlep org uk - Canberra sand and gravel green waste - Spanish alphabet pronunciation quiz - Code halal and haram food list - Population and sample quiz - My si net email - Module 1 Discussion Post 2 BHS380 - Sultan qaboos university physics - Grand chase rufus 4th job - Ol 211 final project milestone two - Quick answer - Moreton bay waste collection - Scripting Dialogues - Snagit is an example of ____ software - Short Essay - Nunthorpe academy student email - Essay - Spider crab and algae mutualism - Punctuated equilibrium model of team development - Essay - Fundamentals of financial management brigham houston pdf - Intermediate accounting ch 23 solutions - What is the social media networks impacts on internal workplace communication? Give your opinion in management perspective. (business communication) - Social Media, Data & Society - Qs 6 8 days sales uncollected lo a1 - Discussion Question - Single or double quotation marks australia - How to create football field valuation chart in excel - Hypothesis and Variables - The invisible goldmine strategy - 6 methods of separating mixtures - Week 1 case study - Hayden walker weather reviews - Gre chemistry practice test - Army surplus george st sydney - Week 1 - The court cases in bruff and walden demonstrated that - Staefa control system parts - Discussion w6 635 - Happy belly food truck menu atlanta - The last dance encountering death and dying quiz - Mod 2 PASU - Sherlock holmes the speckled band pdf - Derive planar density expressions for bcc - Basic english grammar test - Pisces monkey suzanne white - What is a survivorship curve - 81 prime or composite - Explain town matrix and portfolio matrix - A comparative balance sheet for shabbona corporation - Multi spindle drilling machine definition - Worldview analysis - Breakdown voltage of 1n4001 diode - Sweatt model vs swot analysis - Describe and evaluate the humanistic approach in psychology - Compare and contrast servant leadership and followership - Balance ag2o ag o2 - Bank of america merrill lynch case study - Jayco warranty service near me - Why is understanding the bacteria communication important for humans - Mohs hardness scale worksheet - Paragraph 353b of the immigration rules - Thinking critically about ethical issues - The nine billion names of god sparknotes - Evolution of organisation theory - Discussion needed by 3pm Sat - Perfect society essay - English-Discussion: Golden Lines from Robert Ray's "Thematic Paradigm" - Conflict resolution cover letter - How is john proctor a hypocrite - How to do wes evaluation - Chapter 13 financial statement analysis - Trevor noah tickets tulsa river spirit casino resort march 13 - Mission statement for mcdonald's - Md120 document in oracle apps - Poetry reading - St george asset finance - How does the setting of the lottery affect the story - Criminal justice/affidavit/complaint offense#2 - 28 rate gain worksheet - Is movicol low fodmap - 3 page essay - Internal control procedure used to safeguard a company's assets - Adele's esl corner past continuous - Articulating your philosophy of nursing - Translational research graphic organizer example - Upper intermediate reading texts - Michael galanes say yes to the dress - Air hose festoon system - Paper - Osha ppe toolbox talk - HRM Case Study 5 Pages or Less, APA USE PROVIDED SOURCES - How to cite mbti results in apa format - A pig's tale book - Interview homework - Deconstructing an advertisement assignment - Operation winback ps2 cheats - Strategic Marketing 3