Ex2016 challenge yourself 8.4 | Accounting homework help

 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 fileA 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.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 Step 3  Grade my  Projec 

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Custom Writings Help is a Quality-Oriented Company in Online Writing as a result of hiring exceptional professionals to execute clients' tasks.


Research Papers

At Custom Writings Help,We understand the struggle of research paper writing, and that is why at Custom WritingS Help, we are all out to help you. We pride ourselves on having a team of clinical writers. The stringent and rigorous vetting process ensures that only the 'BEST' Writers are chosen for the job. We have highly qualified Ph.D. and MA writers working with us; we equally offer these experienced writers specific bonuses and incentives to make them deliver highly original, unique, and informative content at reasonably low prices.


Thesis Writing Service

Worlwide, Many Masters Students are struggling with Thesis Completion. A thesis is likely to be the longest and most challenging piece of work a student has ever completed. However, unlike essays and other assignments, a student can pick a particular interest topic and work on their initiative. Fortunately, we are there for you. At Custom Writings Help, you are assured of an authentic, imaginative, informative, linguistically great, and advantageous thesis that adheres to all your needs. So, why continue considering different writers when you have discovered the best in the field?


Custom Papers

Not a single student can avoid writing custom papers. However, a total lack of experience, skills, and time makes it very hard to produce a superb writing piece. Therefore, if you are seeking professional help, turn to us. Our specialized and experienced writers compose a variety of model papers, including custom essays, college term papers, research papers, book reports, MBA essays, executive summaries, dissertations, Ph.D. theses, admission essays, and research proposals for college and university students at any level.


Essay Writing

Most of the students disregard the critical principles of essay writing and compose papers below sensible guidelines. Therefore, with Custom Writings Help, one should not worry about his/her essay. Our Writers compose informative and engaging content on all complexities and topics. We write meaningful and smart essays while prioritizing all aspects that bring about a good grade, such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines.


Coourse Work Writing

Don't let the seemingly never-ending onslaught of writing assignments get you down. If you are looking where to get course work assistance online, the writers at Custom Writings Help are here to assist you with all of your writing needs. We undertake to unique delivery of papers that meet the professor's requirements. The content is proofread, edited, and checked plagiarism before submission to customers. No matter how big or small your work is, we will deliver on time. Try US Now! !


Dissertation Writing Service

High-Quality Dissertation Writing Services are rare. They require Ph.D. academicians – not easily found. However, are an exception. The years, time, and resources we have invested in the dissertation world has given us a competitive advantage over others. Choose to come to Custom Writings Help; You will find perfect Ph.D. consultants who have written hundreds of dissertations theses ready to help you. Let our dissertation-writing services help you craft your dissertation, for you are assured we will give you the results.