New User? Register
Fill This Form To Receive Instant Help
Homework answers / question archive / Shelly Cashman Excel 2019 | Modules 8-11: SAM Capstone Project 1a Porter 360 ANALYZE DATA AND SOLVE PROBLEMS GETTING STARTED Open the file SC_EX19_CS8-11a_FirstLastName_1
Shelly Cashman Excel 2019 | Modules 8-11: SAM Capstone Project 1a Porter 360 ANALYZE DATA AND SOLVE PROBLEMS GETTING STARTED Open the file SC_EX19_CS8-11a_FirstLastName_1
Share With -->
Shelly Cashman Excel 2019 | Modules 8-11: SAM Capstone Project 1a
ANALYZE DATA AND SOLVE PROBLEMS
- Open the file SC_EX19_CS8-11a_ FirstLastName _1.xlsm , available for download from the SAM website.
- 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.
- If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
- Click enable content at the top, if required.
- To complete this project, you need to add the Developer tab. If this tab does not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the Developer check box, and click OK.
- To complete this project, you need to add the Power Pivot tab to the ribbon. From the File tab, click the Options button. In the Data options section of the Data tab, click the checkbox next to Enable Data Analysis add- ins: Power Pivot, Power View, and 3D Map, and click OK.
- To complete this project, you need to add the Analyze group to the Data tab on the ribbon. From the File tab, click the Options button. In the Add-ins section, use the dropdown menu to select the the Excel Add-ins and click Go... In the dialog box, click the checkboxes next to Analysis ToolPak and Solver Add-in, and click OK.
- PROJECT STEPS
- Insert WordArt using the Fill: Red, Accent color 1; Shadow WordArt style.
- Type Porter 360 Client Survey as the WordArt text.
- Change the Text Fill color to Aqua, Accent 5 .
- Change the font size to 40 point .
- Move the WordArt so that it is centered in the top of the form in the range C2:I3.
- In cell J6, add a link to the www.porter.example.com website.
- In cell J7, add a link to the [email protected] email address.
- Use Contact Porter 360 as the ScreenTip for the email link.
- Eva asks you to finish automating the form, which contains sample data for testing. In cell D23, enter a formula without using a function that displays the client's city from the named range City .
- In cell H12, insert an Option Button (Form Control) .
- Edit the option button text to use the Website to replace the placeholder text.
- Format the new option button to link it to cell $I$24 (if necessary).
- Format the option button to use 3-D shading.
- Position the new option button so that its top and bottom align with the Social media option button to its left, and that its left aligns with the TV ad option button above it.
- In cell G15, insert a Check Box (Form Control) .
- Edit the check box text to use Video doorbell to replace the placeholder text.
- Format the new check box to link it to cell $L$23 .
- Format the check box to use 3-D shading.
- Position the Video doorbell check box so that its box aligns with the top of the Security lights check box to its left.
- In cell G17, insert a Button (Form Control) to the right of the Save Data button.
- Assign the ClearData macro to the new button.
- Edit the button to use Clear to replace the placeholder text.
- Format the new button to change the height to 0.3" and the width to 1" .
- Position the Clear button to align with the top and bottom of the Save Data button on its left.
- Edit the button to display Save as the button text.
- Change the font to 11 point Lucida Grande (Body) .
- Use the Save button to run the SaveData macro.
- Unhide the Client Information worksheet to verify that it contains the data from the Survey worksheet.
- Hide the Client Information worksheet.
- Use any error-checking method to determine the source of the error in cell E12, which should calculate the average sales in Year 1.
- Correct the error by editing the formula in cell E12.
- Use the Trace Precedents arrows to find the source of the #VALUE! error in cell G13.
- Correct the formula in cell G13, which should subtract the total Year 1 Sales from the total Year 2 sales.
- Remove the trace arrows, if necessary.
- The line chart in the range C15:G29 shows gross monthly sales in Year 2. Eva wants to forecast the trend for the next two periods. Add a Linear Forecast trendline to the line chart.
- Insert a Scatter chart that shows the relationship between the R&D expenses (range J3:J15 ) and the sales (range K3:K15 ).
- Resize and position the Scatter chart so that it covers the range I16:M29.
- Use R&D and Sales as the chart title.
- Eva finds that the data points are clustered too close together. She asks you to make the Scatter chart easier to interpret by moving them farther apart. Change the Minimum bounds of the horizontal axis to 3000 to allow more space for the data points on the chart.
- Set a data validation rule for the range C4:C30 that allows only data from the list in the range H4:H11 . ( Hint: Select the range using the dropdown.)
- Add an Input Message using Product ID as the Input Message title and the following text as the Input message: Enter the ID for this product.
- Add an Error Alert using the Stop style, Product ID Error as the Error Alert title, and the following text as the Error message: The Product ID is incorrect.
- Circle the invalid data in the worksheet.
- Use the DB100 as the correct Product ID for the invalid data.
- Clear any remaining errors in the worksheet.
- Insert the Sum of Payment by Region recommended PivotTable.
- Use May Sales by Region as the name of the new worksheet.
- Apply the Sky Blue, Pivot Style Medium 13 style to the PivotTable.
- Add a second copy of the Payment field to the Values area of the Field List, and then change its summary function to Average so that Eva can compare the average payments to the totals.
- Change the number format of the two value fields to Currency with 2 decimal places and the $ symbol.
- Use Total Sales as the column heading in cell B3, and use Average Sales as the column heading in cell C3.
- Insert a Combo PivotChart based on the PivotTable in the May Sales by Region worksheet.
- Display the Total Sales as a Clustered Column chart and the Average Sales as a Line chart.
- Include a secondary axis for the Average Sales data.
- Change the PivotChart colors to Monochromatic Palette 5 .
- Resize and position the chart so it covers the range A12:F24.
- Hide all the field buttons to allow more room for the data.
- Display the legend at the top of the PivotChart.
- In cell I14, use the GETPIVOTDATA function to display the total order amount for the Midwest from the PivotTable on the May Sales by Region worksheet (cell B4 ). ( Hint : Type an equal sign in cell I14 and then click the cell containing the value you want the GETPIVOTDATA function to get from the PivotTable.)
- In cell I15, use the GETPIVOTDATA function to display the total order amount for the Northeast from the PivotTable on the May Sales by Region worksheet (cell B5 ).
- In cell I16, use the GETPIVOTDATA function to display the total order amount for the Northwest from the PivotTable on the May Sales by Region worksheet (cell B6 ).
- In cell I17, use the GETPIVOTDATA function to display the total order amount for the Southeast from the PivotTable on the May Sales by Region worksheet (cell B7 ).
- In cell I18, use the GETPIVOTDATA function to display the total order amount for the Southwest from the PivotTable on the May Sales by Region worksheet (cell B8 ).
- Create a PivotTable based on the Orders table.
- Place the PivotTable on a new worksheet, and then use May Sales by Product Type as the name of the worksheet.
- Use MaySales as the name of the PivotTable.
- Display the Product Type and then the Product ID fields as row headings.
- Display the Payment field as the values.
- Change the Number format of the Sum of Payment values to Currency with 0 decimal points and the $ sign.
- Apply the Sky Blue, Pivot Style Medium 13 style to match the other PivotTable in the workbook.
- Change the layout to show the PivotTable in Tabular form.
- Hide the field headers.
- Create a calculated field using Price as its name.
- The formula should multiply the Payment field value by 0.1 , and then add the result to the Payment field value to calculate the increased price.
- Use Increased Price in cell D3 as the column heading for the calculated field.
- Use Current Price in cell C3 as the column heading for Payment field values.
- Resize columns C and D to their best fit.
- Add a slicer based on the Order Source field.
- Resize and position the slicer so it covers the range E4:F10.
- Change the slicer style to Sky Blue, Slicer Style Light 5 .
- Use the slicer to filter the PivotTable to show only Hardware store data.
- Use Power Pivot to create a PivotTable on a new worksheet, using Sales Reps as the name of the worksheet.
- Display the LastName field values from the Query – SalesReps data source as row headings.
- Display the Region field values from the Query – SalesReps data source as column headings.
- Add the TotalSales field from the Query - Sales data source to the Values area to sum the field values.
- To properly combine data from the Sales and SalesReps tables, use the Power Pivot for Excel window to create a relationship between the Sales and SalesReps tables using the SalesRepID column to relate the tables.
- Use the Data Analysis tool to create a histogram.
- Use the days between a request and installation (range D4:D30 ) as the input range.
- Use the Bin list (range F4:F7 ) as the bin range.
- Use cell A32 as the output range.
- Show a cumulative percentage and chart output in the histogram.
- Resize and position the Histogram chart so it covers the range D32:L48.
- Change the chart layout to Layout 5 to include a data table with the chart.
- Insert a Sunburst chart based on the accessories data in the range H3:K13.
- Resize and position the chart so it covers the range E14:L30.
- Use Sales of Accessories as the chart title.
- Apply Style 5 to the chart to include a legend and dark text.
- Use Raise Price as the scenario name.
- Use the revenue data and fixed expenses (range B5:B6 and B18:B21 ) as the changing cells.
- Enter cell values for the Raise Price scenario as shown in bold in Table 1.
- Create a Scenario Summary report using the net income (cell B27 ) as the result cell to show how the net income changes depending on the revenue and expense changes.
- Use Income Scenario Report as the name of the worksheet containing the report.
- Use the total cost of the promotions (cell E11 , named Total_promotion_cost) as the objective cell in the Solver model, with the goal of matching the value of 98000 in that cell.
- Use the number of promotions run in the three locations (range B9:D9 ) as the changing variable cells.
- Determine and enter the constraints based on the information provided in Table 2.
- Use Simplex LP as the solving method to find a global optimal solution.
- Save the Solver model in cell A13 .
- Solve the model, keeping the Solver solution.
- Solve the model again, this time choosing to produce an Answer report.
- Use Trade Show Answer Report as the name of the worksheet containing the Answer report.
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, low cost option, download this past answer in few clicks, purchase solution.
Already member? Sign In
Custom new solution created by our subject matter experts
Get a quote.
Instructions_SC_EX19_CS8-11a_(1).docx --> Instructions_SC_EX19_CS8-11a_(1).docx
GET ANSWER TO THIS QUESTION
100% UNIQUE SOLUTION WITHIN FEW HOURS
rated 5 stars
Purchased 16 times
View Answer
Sitejabber (5.0)
Trustpilot (4.9)
Google (5.0)
Related questions.
- New Perspectives Excel 2019 Module 10 SAM Critical Thinking Project 1c Lighting Designs ANALYZE DATA WITH POWER TOOLS GETTING STARTED Open the file NPEX19CT10cFirstLastName1
- Shelly Cashman PowerPoint 2019 Module 6 End of Module Project 1 Trenton Bank Funding for Small Businesses CREATING INFORMATION GRAPHICS AND INTEGRATING WITH OTHER PROGRAMS GETTING STARTED Open the file SCPPT19EOM61FirstLastName1
- New Perspectives Excel 2019 Module 11 Textbook Project
- New Perspectives Excel Module 11 and Module 12
- INFO 2130 ndash Spring 2021 Optional Final Exam Total points 100 Instructions Download and rename the macroenabled Excel start file using your NinerNet ID
- I need help with an Excel Spreadsheet
- Shelly Cashman Excel 2019 Modules 13 SAM Capstone Project 1a Ensight Healthcare Consultants CREATE FORMULAS WITH FUNCTIONS GETTING STARTED Open the file SCEX19CS13a FirstLastName 1
- MOS Excel 2019 Module 4 SAM Project 1a Prism Real Estate IMPORT DATA FORMAT WORKSHEETS AND INSERT FORMULAS GETTING STARTED Open the file SAMEX19MOS4aFirstLastName1
- New Perspectives Excel 2019 Module 12 End of Module Project 1 Abernathy Academy DEVELOP AN EXCEL APPLICATION GETTING STARTED Open the file NPEX19EOM121FirstLastName1
- Shelly Cashman Excel 2016 Module 6 SAM Project 1b Go Adventure Tours Creating Sorting and Querying a Table GETTING STARTED Open the file SCEX166bFirstLastName1
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
IMAGES
VIDEO
COMMENTS
Shelly Cashman Excel 2019 | Modules 8-11: SAM Capstone Project 1a Porter 360 ANALYZE DATA AND.
Week5-2 PPT Module 2 Project 1a · EXCEL 2019 SAM MODULES 1 3 CAPSTONE PROJECT A- ENSIGHT HEALTHCARE CONSULTANTS · Microsoft Excel Module 8 End of
Excel Module 11 Project Steps 8-11. Part 2 of the tutorial video for this project.
Word Module 10 End of Module Project 1: Santa Fe Film Offic. Linda Sessions. Linda Sessions. •. •. 2.1K views 1 year ago
Shelly Cashman Excel 2016 | Modules 8-11: SAM Capstone Project 1a Parma ... Save the file asSC_EX16_CS8-11a_FirstLastName_2.xlsmby changing the“1” to a
Illustrated Word 2019 | Modules 8-11: SAM Capstone Project 1a4.In cell B4 of the form table, position the insertion point before the space at the
6, S_2001641_1, MS Excel 2016, Advanced, Core, Shelly Cashman Excel 2016 Modules 8-11: SAM Capstone Project 1a, Lightning Storm, Vasin Lee/Shutterstock.com.
xlsx by changing the “1” to a “2”. If you do not see the. Shelly Cashman Excel 2019 | Module 8: SAM Project 1a. Eastern Mobile. ANALYZE DATA WITH CHARTS AND
With Slide 8 still displayed, use the content placeholder to insert the video. New Perspectives PowerPoint 2019 | Modules 1-2: SAM Capstone Project la 26.
8, a. Note: A third project could be Illustrated Word 2013 Unit C: SAM Project 1a ... Illustrated Fundamentals Word 2013 Units D-F: SAM Capstone Project 1a