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 2013 Chapter 8: Exploring Advanced Data Analysis

1 | Page challenge yourself 8.4 Last Updated 4/3/15

challenge yourself 8.4 In this project, you will use scenarios and a scenario summary to analyze car purchasing options. A large amount of data about car gas consumption was downloaded from https://explore.data.gov/d/9un4-5bz7 and are explained at this Web site: http://www.epa.gov/greenvehicles/Aboutratings.do. 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 EX2013-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 $2,500 as a trade-in. The base sticker price on the car you want is $21,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.

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

a. Create names for the following cells 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

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

2 | Page challenge yourself 8.4 Last Updated 4/3/15

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: 56500

Cell B5: 2500

Cell B8: 60

Cell B9: 3.9%

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

5. 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).

6. The data in the MPG Data worksheet were downloaded from the Web site https://explore.data.gov/d/9un4-5bz7 and are explained at this Web site: http://www.epa.gov/greenvehicles/Aboutratings.do. 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 or equal to 8.

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

d. Create a custom text filter to show only rows where the Trans column contains the word Auto. e. At this point, the list of more than 2,000 cars should be filtered down to just 27 records.

f. Copy the filtered data to cell A1 in the My Car Data worksheet. Be sure to include the heading row and paste keeping the source columns widths.

7. 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:O1 and paste them below the data set in row 31.

b. In cell A30, type: Criteria

c. Set up the criteria in the row(s) below row 31. Find cars where the value in the Fuel column is gasoline and the value in the Hwy MPG column is greater than 35.

d. In cell A34, type: Filtered Data

e. Use Advanced Filter to find the rows that meet the criteria and copy the filtered data to another location beginning in cell A35.

f. Thirteen rows should have been copied from the main data set to cells A36:O48.

http://www.epa.gov/greenvehicles/Aboutratings.do
A Skills Approach: Excel 2013 Chapter 8: Exploring Advanced Data Analysis

3 | Page challenge yourself 8.4 Last Updated 4/3/15

8. Format the filtered data set (cells A35:O48) as a table. Use Table Style Light 9. Be careful not to include row 34 in the table data range.

9. Insert a new table column to the left of the Greenhouse Gas Score column.

a. Copy the values from cells M36:M48 to N36:N48.

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

10. Apply a new custom conditional formatting rule to cells N36:N48 to show the 4 Ratings icon set. Show the icon only.

11. 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 and the Signal Meter With One Filled Bar icon appears at the bottom.

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

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

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

a. Apply the Pivot Style Light 14 Quick Style to the PivotTable.

b. Display a slicer for the SmartWay field and show only data where the SmartWay value is Yes.

15. 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).

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.

16. Save and close the workbook.

17. Upload and save your project file.

18. Submit project for grading. Step 2 Upload & Save

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 Rated Expert
Top Essay Tutor
Top Writing Guru
Writing Factory
Calculation Master
Financial Assignments
Writer Writer Name Offer Chat
Top Rated Expert

ONLINE

Top Rated Expert

I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You

$19 Chat With Writer
Top Essay Tutor

ONLINE

Top Essay Tutor

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.

$17 Chat With Writer
Top Writing Guru

ONLINE

Top Writing Guru

I have worked on wide variety of research papers including; Analytical research paper, Argumentative research paper, Interpretative research, experimental research etc.

$40 Chat With Writer
Writing Factory

ONLINE

Writing Factory

I have assisted scholars, business persons, startups, entrepreneurs, marketers, managers etc in their, pitches, presentations, market research, business plans etc.

$23 Chat With Writer
Calculation Master

ONLINE

Calculation Master

I reckon that I can perfectly carry this project for you! I am a research writer and have been writing academic papers, business reports, plans, literature review, reports and others for the past 1 decade.

$15 Chat With Writer
Financial Assignments

ONLINE

Financial Assignments

I have worked on wide variety of research papers including; Analytical research paper, Argumentative research paper, Interpretative research, experimental research etc.

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

An airplane propeller is m in length - Journal Assignment 8 - PHILOSOPHY Here and Now - Thorn airfield lighting uk - Busschaert racing pigeons for sale - Cover letter after career break - Procurve manager plus 4 - Rocky bay medication training - Weighted average unit contribution margin formula - 0.75 inches on a ruler - How is strategic management illustrated by this case story - Rikki tikki tavi questions - Paulo Case Scenario: Independent Living Mini Unit - Flvs english 4 honors answers - Base ten expanded form - What is the chemical formula for alka seltzer - Discussion Question - Www cbserv com au - Alone maya angelou analysis - Discussion Unit 7 Assignment 2 Autism Spectrum Disorder - Christophe rault sciences po - Read Leaked Movie Trailer and a Confidentiality Agreement and complete the questions at the end of the case study. - Silicone keypad design guide - Enterprise risk management - Chay - Main - Dis 4 - ESL - Ato frcgw clearance certificate - What are two examples of hypothetical syllogism - What are the 3 major monotheistic religions - What is the difference between dss and knowledge management - Theory of culture care diversity and universality - Borland's Brave Beginning - All in green went my love riding - Difference between high tech and high touch positioning strategies - On may 1 pierce company purchased - How to do a scenario summary in excel - NURSING - Dr james armstrong neurologist - End - Password Polices and Authentication Methods Responses - Geographic pay differentials calculator - Left sided heart failure concept map - Until the streetcars come back sparknotes - Have fun teaching comprehension - Strategic plan part 2 swot analysis paper - Cloud computing - One page project update template - Project charter example for a wedding - Blake and mouton grid questionnaire - Who coined the term knowledge worker - Subcontracting process in sap sd - Hospital management system desktop application - Chemcollective virtual lab answers - Benefits minus costs is known as the _____. - Issa specialist in fitness nutrition final exam answers - Anabolic ice cream recipe - Philosophy W5-Rough Draft of Signature Assignment - Certificate iv in frontline management - Circuit breaker symbol single line diagram - Economic module-2 - Rough Draft - Undergraduate programs admission policy griffith - Subject verb agreement for collective nouns - Nau mai ki te ao - Willie hutch fully exposed zip - Helium gas is compressed from kpa - Pegging currency to gold and guaranteeing convertibility - Percy jackson and the lightning thief part 1 - Defining Innovation as Creative Destruction - High cognitive demand math lessons - Aqa a level physics scheme of work - Case study 3 - Window dimensions on floor plans - Bbc in ucas points - CoreChair - Kit kat brand positioning - Pwc guide to accounting for stock based compensation - HRM 652 EVALUATING RESULTS AND BENEFITS - Smitty's men's & boy's wear - Woolworths car insurance cancellation fee - What channel is mtv on frontier - Register of approved occupational clothing with ausindustry - As level grade boundaries 2016 - The blind side interpersonal communication - What could explain the distribution of mesosaurus fossils - Red degeneration of fibroid ppt - How tall is a smurf - Str 581 week 5 quiz - Halton and frodsham harriers - Dr jekyll and mr hyde dialectical journal - Threaded 1 - Box hill postcode sydney - ?? same-day +27833173182 ROMA - MASERU ABORTION CLINIC // PILLS,,,, - Three gorges dam stakeholders - Port lincoln radio station - Research Methods used rhetoric 400 WORDS POST need it in 12 hours - Unit 6 creating digital graphics assignment 2 - What was doubting thomas occupation - Suzanne lacy symbolizes the beauty of aging women in whisper, the waves, the wind by - Bluish black element crossword - Brahmin arden satchel topaz alma