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

Business problem solving using excel 2016 simnet

20/10/2021 Client: muhammad11 Deadline: 2 Day

USING MICROSOFT EXCEL 2016 Guided Project 8-3

Guided Project 8-3 Courtyard Medical Plaza has new worksheets for weight loss workshops. You use Solver with sample data and add

scenarios and data tables to complete a sample set. You also create PivotTables to analyze dental insurance data.

Skills Covered in This Project • Create and manage scenarios.

• Use Solver in a worksheet to find a solution.

• Build a one-variable data table.

• Build a two-variable data table.

• Create and customize a PivotTable.

• Insert a slicer in a PivotTable.

• Insert a PivotChart.

• Generate Descriptive Statistics for a set of data.

This image appears when a project instruction has changed to accommodate an update

to Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate

instruction instead.

1. Open the CourtyardMedical-08 workbook and click the Enable Editing button. The file will be

renamed automatically to include your name.

2. Install Solver and the Analysis ToolPak.

a. Select the Options command [File tab].

b. Click Add-Ins in the left pane.

c. Click Go near the bottom of the window.

d. Select the Solver Add-in box.

e. Select the Analysis ToolPak box.

f. Click OK.

3. Click the Workout Plan worksheet tab and select cell E10. Five activities are included in this plan to

burn calories for weight loss. This cell includes a SUM formula.

4. Add scenarios in a worksheet.

a. Click the What-if Analysis button [Data tab, Forecast group] and select Scenario Manager.

b. Click Add.

c. Type Basic Plan as the name.

d. Click the Changing cells box, select cells D5:D9, and click OK.

e. Do not edit the Scenario Values and click OK.

f. Click Add to add another scenario.

g. Type Double as the name, keep the

Changing cells as is, and click OK.

h. Change the values to 2, 2, 4, 2, 2, doubling

each current value, in the Scenario Values

dialog box and click OK (Figure 8-96).

i. Click Close.

5. Use Solver to find a target calorie burn.

a. Click the Solver button [Data tab, Analyze

group].

b. Select cell E10, the cell with a SUM formula,

for the Set Objective box.

c. Click the Value Of radio button and type

3500 in the entry box.

d. Click the By Changing Variable Cells box and select cells D5:D7. Solver finds how many times

each activity should be performed to burn 3,500 calories subject to the constraints.

Step 1: Download start file

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 4/20/18 Page 2

USING MICROSOFT EXCEL 2016 Guided Project 8-3

6. Add constraints to a Solver problem.

a. Click Add to the right of the Subject to the Constraints box.

b. Select cell D5 for the Cell Reference box.

c. Choose >= as the operator.

d. Click the Constraint box and type 2. The constraint requires that the exercise be done at least

twice a week.

e. Click Add to add each of the five remaining constraints shown here:

D5 <=4

D6 <=3

D6 >=1

D7 <=4

D7 >=1

f. When all constraints are identified,

click OK in the Add Constraint

dialog box.

g. Choose GRG Nonlinear for the

Select a Solving Method.

h. Confirm that the Make

Unconstrained Variables Non-

Negative box is selected (Figure 8-

97).

i. Click Solve. A solution displays in

the worksheet, and the Solver

Results dialog box is open.

7. Save Solver results as a scenario.

a. Click Save Scenario in the Solver

Results dialog box.

b. Type 3500 Burn as the scenario

name.

c. Click OK to return to the Solver

Results dialog box.

d. Click the Restore Original Values

button.

e. Select Answer in the Reports list.

f. Click OK. The generated report is inserted, and the original values are restored in the

worksheet.

IMPORTANT: Be sure that you create the Answer report as the grading for the Solver Scenarios is

dependent on the information in the report. If you skip this step you will not receive any points for

instructions 6.g and 6.h.

8. Create a scenario summary report.

a. Click the What-if Analysis button [Data tab, Forecast group] and select Scenario Manager.

b. Click the Summary button.

c. Verify that the Scenario summary button is selected.

d. Click the Result cells box, select cells D5:D9, type a comma, and then select cell E10.

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 4/20/18 Page 3

USING MICROSOFT EXCEL 2016 Guided Project 8-3

e. Click OK in the Scenario Summary

dialog box. The report is generated in

a new worksheet. Since the results cells

are named, the range names appear

in the report (Figure 8-98).

9. Create a one-variable data table to

calculate total calories if dinner calories

are adjusted.

a. Click the Calorie Journal worksheet

tab and select cell I5. The SUM formula

calculates total calories consumed

per day.

b. Select cell E15. The formula for the

data table must be one column to the

right and one row above the first input

value.

c. Type =, click cell I5, and press Enter.

d. Select cells D15:E23 as the data table range.

e. Click the What-If Analysis button [Data tab,

Forecast group] and choose Data Table.

f. Click the Column input cell box and select cell

G5. The input values will be substituted for this

cell in the data table formula.

g. Click OK (Figure 8-99).

10. Create a two-variable data table to calculate total

calories if both lunch and dinner calories are

adjusted.

a. Select cell L15. A two-variable table has one

formula, one row above column inputs and

one column left of row values.

b. Type =, click cell I5, and press Enter.

c. Select cells L15:T23.

d. Click the What-If Analysis button [Data tab,

Forecast group] and choose Data Table.

e. Select cell E5 for the Row input cell box. Lunch calories are in the row of this data table.

f. Click the

Column input

cell box and

select cell G5.

Dinner calories

are in the

column.

g. Click OK to build

the data table

(Figure 8-100).

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 4/20/18 Page 4

USING MICROSOFT EXCEL 2016 Guided Project 8-3

11. Select cell J1 and insert a page break [Page Layout tab, Page Setup group].

12. Create a PivotTable for dental insurance data.

a. Click the Dental Insurance worksheet tab and select cells A4:E35.

b. Click the Recommended PivotTables button [Insert tab, Tables group].

c. Choose Sum of Billed by Service Code and click OK. Label fields are in the Rows area, and

numeric fields are in the Values area.

d. Name the worksheet tab PivotTable 1.

e. Point to Billed in the Choose fields to add to report area and drag the field name to the

Values area to show the field twice in the PivotTable.

13. Edit value field settings.

a. Click Sum of Billed in cell B3 and click the

Field Settings button [PivotTable Tools

Analyze, Active Field group].

b. Type Total Billed as the Custom Name.

c. Click Number Format, choose Currency, set 0

(zero) decimal places, and click OK two

times to close the dialog boxes.

d. Right-click Sum of Billed2 in cell C3 and

select Value Field Settings.

e. Type Average Billed as the Custom Name.

f. On the Summarize Values By tab, choose

Average as the function.

g. Click Number Format, choose Currency, set 0

(zero) decimal places, and close the dialog

boxes.

14. Use PivotTable tools to format the report.

a. Click any cell in the PivotTable and apply Teal, Pivot Style Medium 6.

Click any cell in the PivotTable and Pivot Style Medium 6.

b. Select the Banded Rows and Banded Columns boxes [PivotTable Tools Design tab, PivotTable

Style Options group].

c. Click the Grand Totals button [PivotTable Tools Design tab, Layout group] and choose Off for

Rows and Columns.

15. Select cell A1 and type Courtyard Medical Dental Services.

16. Type Billings by Service Code in cell A2.

17. Format both labels as 14 pt. (Figure 8-101).

18. Create a PivotChart.

a. Select a cell in the PivotTable and click the PivotChart button [PivotTable Tools Analyze, Tools

group].

b. Choose Column and Clustered Column as the subtype and click OK.

c. Drag the chart object so that its top-left corner is in cell E3.

d. Size the chart object to reach cell M24.

e. Click one of the Average Billed columns and click the Change Chart Type button [PivotChart

Tools Design tab, Type group].

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 4/20/18 Page 5

USING MICROSOFT EXCEL 2016 Guided Project 8-3

f. Click the Chart Type arrow for the

Average Billed series and choose

Line with Markers (Figure 8-102).

g. Click OK.

h. Click one of the Total Billed

columns and change its Shape

Fill [PivotChart Tools Format tab,

Shape Styles group] to Teal,

Accent 5, Darker 25%.

19. Insert a slicer.

a. Click any cell in the PivotTable

and click the Insert Slicer button

[PivotTable Tools Analyze tab,

Filter group].

b. Select the Insurance box and

click OK.

c. Position the slicer so that the top-

left corner is in cell O3. Size the

slicer to reach cell Q15.

d. Format the slicer with Aqua, Slicer

Style Dark 5.

Format the slicer with Slicer Style Dark 5.

e. Click CompDent in the slicer to filter the PivotTable and PivotChart (Figure 8-103).

f. Select cell A1.

20. Generate Descriptive Statistics for a rating category.

a. Click the Dental Insurance sheet tab.

b. Click the Data Analysis button [Data tab, Analyze group].

c. Select Descriptive Statistics and click OK.

d. Select cells E4:E35 for the Input Range box.

e. Select the Labels in First Row box.

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 4/20/18 Page 6

USING MICROSOFT EXCEL 2016 Guided Project 8-3

f. Select the Output Range button.

g. Click the Output Range box and click cell G4.

h. Select the Summary statistics box and click OK.

i. AutoFit column G (Figure 8-104).

21. Save and close the workbook.

22. Upload and save your project file.

23. Submit project for grading.

Step 3: Grade my Project

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:

Top Grade Essay
Engineering Exam Guru
Assignment Hut
Ideas & Innovations
Solutions Store
Maths Master
Writer Writer Name Offer Chat
Top Grade Essay

ONLINE

Top Grade Essay

As an experienced writer, I have extensive experience in business writing, report writing, business profile writing, writing business reports and business plans for my clients.

$25 Chat With Writer
Engineering Exam Guru

ONLINE

Engineering Exam Guru

As an experienced writer, I have extensive experience in business writing, report writing, business profile writing, writing business reports and business plans for my clients.

$28 Chat With Writer
Assignment Hut

ONLINE

Assignment Hut

I have read your project details and I can provide you QUALITY WORK within your given timeline and budget.

$37 Chat With Writer
Ideas & Innovations

ONLINE

Ideas & Innovations

I have done dissertations, thesis, reports related to these topics, and I cover all the CHAPTERS accordingly and provide proper updates on the project.

$29 Chat With Writer
Solutions Store

ONLINE

Solutions Store

I have done dissertations, thesis, reports related to these topics, and I cover all the CHAPTERS accordingly and provide proper updates on the project.

$49 Chat With Writer
Maths Master

ONLINE

Maths Master

I can assist you in plagiarism free writing as I have already done several related projects of writing. I have a master qualification with 5 years’ experience in; Essay Writing, Case Study Writing, Report Writing.

$40 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

Coca-cola - Business management 3 4 - Fast ripening rice in china - Reflection and Discussion Forum Week 10 - PROJECT MANAGEMENT (EARNED VALUE ANALYSIS) Assignment 6 - Checking harley oil cold - Whole foods industry analysis - Biblical view of personality development - Difference between adn and bsn scope of practice - What is the life events approach - Smarter Student Performance Hacks - Research Project: Perform Threat Modelling - Sample informative speech about technology - Caterpillar case study solution - 5.3 use counselling theory to understand own relationships - Oracle payables technical reference manual r12 - 1991 lehman brothers cold calling script - Example of affidavit for divorce australia - Joe friday quiz 166 - Financial Report - George ritzer globalization the essentials pdf - Iia experience verification form - Cell homeostasis virtual lab worksheet - How to write an observation - Assignment M2 - Fraud cases that involved agency conflicts - DISCUSSION 2 ACG - How did archimedes find the density of the crown - Against school john taylor gatto thesis - Parafem - As 3500 part 3 - A streetcar named desire scene 11 - A bag of cement of weight 325 n hangs - Kingdom phylum class order family genus species - Nursing theory - Communicating in groups applications and skills 10th edition - Please read the assigned case, answer the questions in the textbook, and create one multiple choice question (no true/false accepted) with four answer choices. Include the correct answer. - Chilling and killing my annabel lee - Phet dc only circuit simulator - 7x 4 x 5 simplify - Same-day 0833173182 JANE FURSE ABORTION CLINIC // PILLS,,,, - 463 fox run road findlay oh - Assumptions of systems theory social work - Butler machine tool v ex cell o corp - History of Public Health worldwide - Webster city daily freeman journal - Ideologies of globalization by manfred steger pdf - Appointment in samarra pdf - License application form rms 1001 - When was the fall of the house of usher written - Robert burns if a body meet a body - Vickie lee roach v electoral commissioner - Breadboard resistors in parallel - Zara's identification of customer needs can be categorized - A kubota tractor acquired on january 8 - ISBN-13: 978-0-13-413042-2 ISBN-10: 0-13-413042-1 9 780134 130422 9 0 0 0 0 OPER ATIONS MANAGEMENT Sustainability and Supply Chain Management TWELFTH EDITION O PER A T IO N S M A N A G E M E N T Su stain ability an d Su p ply C h ain M an agem en t TWELFTH EDITION JAY HEIZER | BARRY RENDER | CHUCK MUNSON HEIZER RENDER MUNSON www.pearsonhighered.com IMPROVING RESULTS A proven way to help individual students achieve the goals that educators set for their course. ENGAGING EXPERIENCES Dynamic, engaging experiences that personalize and activate learning for each student. AN EXPERIENCED PARTNER From Pearson, a long-term partner with a true grasp of the subject, excellent content, and an eye on the future of education. Pearson’s MyLab™ O P E R A T I O N S MANAGEMENT Sustainability and Supply Chain Management A01_HEIZ0422_12_SE_FM.indd iA01_HEIZ0422_12_SE_FM.indd i 29/12/15 4:08 pm29/12/15 4:08 pm 561590_MILL_MICRO_FM_ppi-xxvi.indd 2 24/11/14 5:26 PM This page - Bright star jane campion analysis - Good to great chapter 3 - A formalized report that summarizes your current financial situation - Economic Assignment - Wageweb - DB reply - Yanomamo chagnon 6th edition pdf - University of leicester plagiarism - Exp e capstone 1 european excursions 2.5 - E-6 accu chek performa - How to make a0 poster in word - Hyundai motors is considering three sites - Mirvac house and land packages - Student exploration half life answer key activity b - What is a cc in medicine - Left great toenail injury icd 10 - Pantomime ideas for 3 - Residential property lending strand - Chemistry year 11 syllabus - Active stream meter mk7c - Patricia benner novice to expert theory powerpoint - Project - Word 2016 - Agriculture Insurance Conference - Workplace Environment Assessment - Discussion and Brief Analysis - Assignment 8 - V - The glass castle page count - Self-Image Evaluation - State evidence for brownian motion - Where did rip van winkle live - Assignment3091720 - Leadership Course - Mandalay bay shooting wiki - Jeremy fink and the meaning of life chapter summaries - A character variable can never store more than - According to the attraction selection attrition theory job applicants - Safe current limits for electromedical apparatus - Avarice poem yusef komunyakaa meaning - 3 minute countdown timer for powerpoint - Complex analysis by lars ahlfors - A twenty five foot ladder just reaches - Need 10+ Pages With 10 References Cited In APA Format - PLEASE DO NOT RUN any Plagiarism Report - Khalid mohammed abdul rahim net worth - Discussion about Payback