Microsoft Excel 2016: Data Analysis and Business Modeling ptg XXXXXXXXXX ptg XXXXXXXXXX Microsoft Excel 2016 Data Analysis and Business Modeling Wayne L. Winston ptg XXXXXXXXXX PUBLISHED BY Microsoft...


For this assignment, you will work through the following problems from Chapter 42 in Microsoft Excel 2016 Data Analysis and Business Modeling: Problems 6, 7, 15 and 16. Please see the book for the questions, and then start working inthis file, which includes the data for each problem in a separate tab



For this assignment, you should submit your completed Excel file, including short written answers to questions at the top of each tab, if needed.




Microsoft Excel 2016: Data Analysis and Business Modeling ptg18466955 ptg18466955 Microsoft Excel 2016 Data Analysis and Business Modeling Wayne L. Winston ptg18466955 PUBLISHED BY Microsoft Press A Division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright © 2016 by Wayne L. Winston. All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. Library of Congress Control Number: 2016943109 ISBN: 978-1-5093-0421-9 Printed and bound in the United States of America. First Printing Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Support at [email protected]. Please tell us what you think of this book at http://aka.ms/tellpress. This book is provided “as-is” and expresses the author’s views and opinions. The views, opinions and information expressed in this book, including URL and other Internet website references, may change without notice. Some examples depicted herein are provided for illustration only and are fctitious. No real association or connection is intended or should be inferred. Microsoft and the trademarks listed at http://www.microsoft.com on the “Trademarks” webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. Acquisitions and Developmental Editor: Rosemary Caperton Project Editor: John Pierce Editorial Production: Kathleen Atkins, Rob Nance, and John Pierce Technical Reviewer: Ed Price; Technical Review services provided by Content Master, a member of CM Group, Ltd. Copyeditor: Kathleen Atkins Indexer: Maureen Johnson, MoJo’s Indexing and Editorial Services Cover: Twist Creative • Seattle http://www.aka.ms/tellpress http://www.microsoft.com ptg18466955 Contents at a glance Introduction xxi CHAPTER 1 Basic spreadsheet modeling 1 CHAPTER 2 Range names 9 CHAPTER 3 Lookup functions 21 CHAPTER 4 The INDEX function 29 CHAPTER 5 The MATCH function 33 CHAPTER 6 Text functions 39 CHAPTER 7 Dates and date functions 55 CHAPTER 8 Evaluating investments by using net present value criteria 63 CHAPTER 9 Internal rate of return 69 CHAPTER 10 More Excel financial functions 75 CHAPTER 11 Circular references 89 CHAPTER 12 IF statements 95 CHAPTER 13 Time and time functions 113 CHAPTER 14 The Paste Special command 119 CHAPTER 15 Three-dimensional formulas and hyperlinks 125 CHAPTER 16 The auditing tool 131 CHAPTER 17 Sensitivity analysis with data tables 139 CHAPTER 18 The Goal Seek command 151 CHAPTER 19 Using the Scenario Manager for sensitivity analysis 157 CHAPTER 20 The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions 163 CHAPTER 21 The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS functions 171 CHAPTER 22 The OFFSET function 177 CHAPTER 23 The INDIRECT function 191 CHAPTER 24 Conditional formatting 199 CHAPTER 25 Sorting in Excel 227 CHAPTER 26 Tables 235 CHAPTER 27 Spin buttons, scroll bars, option buttons, check boxes, combo boxes, and group list boxes 251 CHAPTER 28 The analytics revolution 263 ptg18466955 iv Contents at a Glance CHAPTER 29 An introduction to optimization with Excel Solver 269 CHAPTER 30 Using Solver to determine the optimal product mix 273 CHAPTER 31 Using Solver to schedule your workforce 285 CHAPTER 32 Using Solver to solve transportation or distribution problems 291 CHAPTER 33 Using Solver for capital budgeting 297 CHAPTER 34 Using Solver for financial planning 305 CHAPTER 35 Using Solver to rate sports teams 311 CHAPTER 36 Warehouse location and the GRG Multistart and Evolutionary Solver engines 317 CHAPTER 37 Penalties and the Evolutionary Solver 327 CHAPTER 38 The traveling salesperson problem 333 CHAPTER 39 Importing data from a text file or document 337 CHAPTER 40 Validating data 343 CHAPTER 41 Summarizing data by using histograms and Pareto charts 351 CHAPTER 42 Summarizing data by using descriptive statistics 365 CHAPTER 43 Using PivotTables and slicers to describe data 383 CHAPTER 44 The Data Model 429 CHAPTER 45 Power Pivot 439 CHAPTER 46 Power View and 3D Maps 451 CHAPTER 47 Sparklines 465 CHAPTER 48 Summarizing data with database statistical functions 471 CHAPTER 49 Filtering data and removing duplicates 481 CHAPTER 50 Consolidating data 495 CHAPTER 51 Creating subtotals 501 CHAPTER 52 Charting tricks 507 CHAPTER 53 Estimating straight-line relationships 543 CHAPTER 54 Modeling exponential growth 551 CHAPTER 55 The power curve 555 CHAPTER 56 Using correlations to summarize relationships 561 CHAPTER 57 Introduction to multiple regression 567 CHAPTER 58 Incorporating qualitative factors into multiple regression 575 CHAPTER 59 Modeling nonlinearities and interactions 585 CHAPTER 60 Analysis of variance: One-way ANOVA 593 CHAPTER 61 Randomized blocks and two-way ANOVA 599 CHAPTER 62 Using moving averages to understand time series 609 ptg18466955 Contents at a Glance v CHAPTER 63 Winters method 613 CHAPTER 64 Ratio-to-moving-average forecast method 619 CHAPTER 65 Forecasting in the presence of special events 623 CHAPTER 66 An introduction to probability 631 CHAPTER 67 An introduction to random variables 641 CHAPTER 68 The binomial, hypergeometric, and negative binomial random variables 647 CHAPTER 69 The Poisson and exponential random variable 655 CHAPTER 70 The normal random variable and Z-scores 659 CHAPTER 71 Weibull and beta distributions: Modeling machine life and duration of a project 669 CHAPTER 72 Making probability statements from forecasts 675 CHAPTER 73 Using the lognormal random variable to model stock prices 679 CHAPTER 74 Introduction to Monte Carlo simulation 683 CHAPTER 75 Calculating an optimal bid 693 CHAPTER 76 Simulating stock prices and asset-allocation modeling 699 CHAPTER 77 Fun and games: Simulating gambling and sporting-event probabilities 709 CHAPTER 78 Using resampling to analyze data 717 CHAPTER 79 Pricing stock options 721 CHAPTER 80 Determining customer value 735 CHAPTER 81 The economic order quantity inventory model 741 CHAPTER 82 Inventory modeling with uncertain demand 747 CHAPTER 83 Queuing theory: The mathematics of waiting in line 753 CHAPTER 84 Estimating a demand curve 759 CHAPTER 85 Pricing products by using tie-ins 765 CHAPTER 86 Pricing products by using subjectively determined demand 771 CHAPTER 87 Nonlinear pricing 777 CHAPTER 88 Array formulas and functions 785 CHAPTER 89 Recording macros 803; Index 815 ptg18466955 This page intentionally left blank ptg18466955 vii Contents Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Chapter 1 Basic spreadsheet modeling 1 Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Chapter 2 Range names 9 How can I create named ranges? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Using the Name box to create a range name . . . . . . . . . . . . . . . . . . .10 Creating named ranges by using the Create From Selection option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11 Creating range names by using the Define Name option . . . . . . . .12 The Name Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13 Answers to this chapter’s questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14 Remarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18 Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Apr 05, 2021
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here