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

4 ratings icon set excel

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

A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

1 | Page Challenge Yourself 8.4 Last Updated 4/9/18

Challenge Yourself 8.4 In this project, you will use scenarios and a scenario summary to analyze car purchasing options. The data for

this project were downloaded from: https://www.fueleconomy.gov/feg/download.shtml You will use custom

filtering to find the data you are interested in and copy it from this very large data set to another worksheet.

You will then use Advanced Filter, conditional formatting, sorting, and subtotals to further analyze this subset

of data. You will manipulate a PivotTable created from the main vehicle data set. Finally, you will use Solver

to solve a business problem to help you pay for your new car.

Skills needed to complete this project: • Creating What-If Analysis Scenarios

• Creating Scenario Summary Reports

• Creating a Custom Filter

• Using Advanced Filter

• Creating New Conditional Formatting Rules

• Sorting Data on Multiple Criteria

• Adding Subtotals

• Changing the Look of a PivotTable

• Filtering PivotTable Data

• Activating the Solver Add-Inn

• Using Solver

1. Open the start file EX2016-ChallengeYourself-8-4. The file will be renamed automatically to include

your name. Change the project file name if directed to do so by your instructor, and save it.

2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the

top of the workbook so you can modify the workbook.

3. You want to purchase a new car. Your current car is worth $3,500 as a trade-in. The base sticker price on

the car you want is $24,595. The first financing offer from the dealer is 1.9% APR for 24 months, with

no cash back. The original financing offer terms are listed in cells A3:B9 on the Car Loan worksheet. Cell

B10 contains a formula with a PMT function to calculate the monthly payment. Use what-if analysis tools

to compare financing options. Create scenarios to compare financing options for the car loan.

a. Create names for the following cells on the Car Loan worksheet to make the scenarios easier to

follow:

Name cell B3: Price

Name cell B4: TradeIn

Name cell B5: CashBack

Name cell B6: Loan

Name cell B8: Months

Name cell B9: APR

Name cell B10: Payment

Name cell B12: TotalCost

Step 1

Download start file

https://www.fueleconomy.gov/feg/download.shtml
A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

2 | Page Challenge Yourself 8.4 Last Updated 4/9/18

b. Create a scenario named Original Financing to save the original values in cells B3, B5, B8,

and B9.

c. Create a second scenario named Intermediate Car. Change the values as follows:

Cell B3: 33999

Cell B5: 1500

Cell B8: 36

Cell B9: 2.4%

d. Show the Intermediate Car scenario to verify your data entry.

e. Create a third scenario named Luxury Car. Change the values as follows:

Cell B3: 62700

Cell B5: 2500

Cell B8: 60

Cell B9: 3.9%

f. Show the Luxury Car scenario to verify your data entry.

4. Create a scenario summary report to compare the results for cells B10 (the monthly payment) and B12

(the total cost of the car including interest).

5. The data in the MPG Data worksheet were downloaded from the Web site

https://www.fueleconomy.gov/feg/download.shtml. The greenhouse gas scores range from 0 to 10,

where 10 is best. The vehicles with the best scores on both air pollution and greenhouse gas receive the

SmartWay designation. Use custom filtering to find cars in this data set that meet your criteria and then

copy the smaller data set to another worksheet where you can work with it further.

a. Go to the MPG Data worksheet, and display the AutoFilter arrows for the data set.

b. Create a custom number filter to show only rows where the greenhouse gas score is greater than 8.

c. Create another custom number filter to show only rows where the highway MPG is greater than 35.

d. Create a custom text filter to show only rows where the Trans column contains the word auto.

e. Create a custom text filter to show only rows where the value in the Fuel column equals Electricity

or Gasoline.

f. At this point, the list of more than 2,000 cars should be filtered down to just 48 records.

g. Copy the filtered data to cell A1 in the My Car Data worksheet. Be sure to include the heading row,

but do not include the All Models title. Paste keeping the source column widths.

6. On the My Car Data worksheet, use Advanced Filter to copy a subset of data to another location on this

worksheet.

a. Copy cells A1:P1 and paste them below the data set in row 52.

b. In cell A51, type: Criteria

c. Apply the Accent 4 cell style to cell A51.

d. Set up the criteria in the row(s) below row 52. Find cars where the value in the Fuel column is

Gasoline and the value in the Hwy MPG column is greater than 35.

https://www.fueleconomy.gov/feg/download.shtml
A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

3 | Page Challenge Yourself 8.4 Last Updated 4/9/18

e. In cell A55, type: Filtered Data

f. Apply the Accent 4 cell style to cell A55.

g. Autofit column A.

h. Use Advanced Filter to find the rows that meet the criteria and copy the filtered data to another

location beginning in cell A56.

i. Sixteen rows should have been copied from the main data set to cells A57:P72.

7. Format the filtered data set (cells A56:P72) as a table. Use White, Table Style Light 1, or Table Style

Light 1. Be careful not to include row 55 in the table data range.

8. Insert a new column to the left of the Greenhouse Gas Score column. Hint: Be sure to add a column to

the worksheet, not just the table.

a. Copy the values from cells M57:M72 to N57:N72.

b. Type this column label in cell N56: Cmb MPG Icon

9. Apply a new custom conditional formatting rule to cells N57:N72 to show the 4 Ratings icon set. Show

the icon only.

10. Continue working with the filtered data that has the conditional formatting applied. Sort the table first

alphabetically by the values in the Model column and then by icon in the Cmb MPG Icon column so

the Signal Meter With Four Filled Bars icon appears at the top.

11. Go back to the main data set at the top of the My Car Data worksheet and add subtotals for each change

in Model to calculate the average for the following: Air Pollution Score, City MPG, Hwy MPG, and

Cmb MPG.

12. Collapse the data to show just the total rows.

13. Go to the MPG PivotTable worksheet. This sheet includes a PivotTable created from the data on the

MPG Data worksheet.

a. Refresh the PivotTable data.

b. Apply the White, Pivot Style Medium 1, or Pivot Style Medium 1 Quick Style to the PivotTable.

c. Display a slicer for the SmartWay field and show only data where the SmartWay value is Elite.

14. To pay for the new car, you’ve decided to start a bakery business out of your home kitchen. You have limited

capital and capacity, so you need to make wise decisions about which products to bake each day. Go to the

Bakery Business worksheet. Cell B1 uses a SUMPRODUCT formula to calculate profit. Carefully review the

assumptions and formulas in this worksheet before entering the following Solver parameters:

a. Find the maximum possible profit (cell B1) by changing the values in cells C7:C10.

b. The working hours required (B3) must be less than or equal to the number of working hours in

the day (D3).

c. The total cost of ingredients (B4) must be less than or equal to the available capital (D4).

A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

4 | Page Challenge Yourself 8.4 Last Updated 4/9/18

d. For each item in the range C7:C10, the number of items baked cannot exceed the maximum capacity

available per day as defined in cells F7:F10. This restriction requires four separate constraints in the

Solver Parameters dialog.

e. The items in the variable cell range C7:C10 must be whole numbers.

f. There should be a total of seven constraints.

g. Run Solver and accept the Solver solution.

15. Save and close the workbook.

16. Upload and save your project file

17. Submit project for grading.

Step 2

Upload & Save

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:

Phd Writer
Top Academic Guru
Financial Assignments
Assignment Guru
Buy Coursework Help
Financial Solutions Provider
Writer Writer Name Offer Chat
Phd Writer

ONLINE

Phd Writer

I have written research reports, assignments, thesis, research proposals, and dissertations for different level students and on different subjects.

$15 Chat With Writer
Top Academic Guru

ONLINE

Top Academic Guru

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

$36 Chat With Writer
Financial Assignments

ONLINE

Financial Assignments

As per my knowledge I can assist you in writing a perfect Planning, Marketing Research, Business Pitches, Business Proposals, Business Feasibility Reports and Content within your given deadline and budget.

$27 Chat With Writer
Assignment Guru

ONLINE

Assignment Guru

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

$38 Chat With Writer
Buy Coursework Help

ONLINE

Buy Coursework Help

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.

$42 Chat With Writer
Financial Solutions Provider

ONLINE

Financial Solutions Provider

This project is my strength and I can fulfill your requirements properly within your given deadline. I always give plagiarism-free work to my clients at very competitive prices.

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

Scoring model in project selection - Robert gray harbour dusk analysis - Ethics in business - I need 3 pages on early childhood practice - The reluctant fundamentalist changez quotes - Gleim cia test bank - Bearings for sale near me - Business research report - Part II APA style 6 pages - Ck+ dataset free download - Accounting - Managerial Epidemiology Week 3 Discussion - Zalewska v county of sullivan - Teaching project - L98a2 cadet gp rifle - C squared equals a squared b squared - Spelling connections 7 answers - Year 8 chemistry revision - Introduction to criminal justice 9th edition bohm pdf - Nun gimel hei shin song - Alienated labour karl marx summary - The intensity of an earthquake wave passing - Lvr home loan meaning - May might could exercises - The great gatsby research paper thesis - Week6 - Stegbar aluminium window colour chart - Harvard referencing la trobe - If capital markets are efficient then - Action research/traditional research - Excel module 2 sam project 1 - Late nite labs ph indicator answers - Subway surfers player not authenticated - POWER POINT!!! SCIENCE SCHOOL FAIR PROJECT FOR A 4TH GRADER GIRL - Cultural Sensitivity in the Workplace and Community - Uws handbook construction management - 4mat review - Aisi 1050 hr steel properties - Insomnia - Pakistan economic survey 2018 19 - Custom molds inc case study answers - Structured analysis of competing hypotheses - What is the main goal of gohsep - Sociology observation assignment - Finance manager personal statement - Wk 1 - Signature Assignment: Ethical Decisions Worksheet - Di - Oedipus rex hubris quotes - Https booknow eid appointment plus com - How many core electrons are in a chlorine atom - State theatre melbourne seating review - Canal and river trust gold licence - Discussion(TM) - Anglo german naval agreement - Respond post to other classmate only need 2 paragraph 2 classmates - Lorenzo ghiberti north doors - Escucha la conversación y completa las oraciones - Case study assignment - What are toms shoes called - Discussion - Mining - Vlt fc 300 manual - Cake creations lisbellaw enniskillen - Unlike peru's coast ecuador's coastland consists of - Business Policy and Strategy III Questions & Answers - Describe the use of ethical code in nursing - Whats your theme - Ul awm style 1015 - Autozone case study - Does technology make us alone essay - What percentage of apple's authorized common stock was issued at september 27, 2014? - Financial statement analysis lessens the need for expert judgment - Ronna kress casting email - Draw and label the diagram of solar system - Cover letter goldman sachs 300 words - Fickle monster cases discount code - Janson medical clinic case study - Purdue owl expository essay - Using the cpt manual code the following alkaloids quantitative urine - Mtv illest road trip of all time answers - Transportation problem questions and answers doc - Dulce et decorum est meaning - ME - Online - Dis 2 - Platelet count calculation formula - Longhorn corporation provides low cost food delivery - Nema ve 2 2013 - Spirax sarco liquid drain trap - Threats attacks and vulnerability assessment - Cereus queen of the night - Turnigy 9x aux channels - 4. what would happen if the tubing with the yellow band was placed in a beaker of distilled water? - Agile project management practitioner - Layers of fear lock code cat dog rat - International computer driving license syllabusinternational computer driving license syllabus - Person vs person conflict - Marriott international swot analysis 2019 - Project topic on food hygiene - Descriptive statistics worksheet answers - Brown bin collection breckland - Namma kalvi 12th study material - Find the length of the loop of the given curve. x = 12t − 4t3, y = 12t2