Increased by more than 50%, which leads to the conclusion of model over-fitting. A score underneath – 0.04 for instance will be resolved as a contrary test, which interprets as a monetarily beneficial individual, who could be endorsed for a Mastercard. For questions in this section, assume your model and the data it uses are not available – the bank’s choice is between Eggertopia scores and the base rate. At the above maximum (break-even) price per score, what would be the value per bit of incremental information gained from the Eggertopia scores? Milestone. Review confusion matrix definitions and letter designations on the Information Gain Spreadsheet, [PPV is defined at Cell G41], obtain True Positive and False Positive Rates from the AUC Calculator Spreadsheet, and use algebra to solve. Excel Data Analysis ToolPak 1 TUTORIAL & 1 TEST. Assume a Gaussian Probability Distribution with mean = 3 and standard deviation = 4. Industrial IoT on Google Cloud Platform By Coursera. Proudly powered by WordPress If you want to have a deep dive in those popular data analysis, data mining and statistical techniques, this course will be helpful. introduction to data analysis using excel coursera quiz answers. Two stocks have the following expected annual returns: Oil stock – expected return = 9% with standard deviation = 13%, IT stock – expected return = 14% with standard deviation = 25%. What do you think?Your answer cannot be more than 10000 characters. a) The main thing we should do is examine the connection of the factors, and recognize which are the most important in the model. Hint: Use the Correlation and P.I.G. Round to the nearest dollar. eval(ez_write_tag([[970,90],'jamdbokhtier_com-mobile-leaderboard-2','ezslot_25',642,'0','0'])); 9. What is its Positive Predictive Value (PPV) of the forecasting model using Eggertopia scores? Use Excel’s chart function to generate a scatter plot of SPY index monthly returns (y axis) against Amazon monthly returns (x axis) 4. 5. 10. The linear correlation is R = .33. Hint: Refer to the Excel NormSFunctions Spreadsheet. Hint: Use the Typical Problem with NormSDist Spreadsheet. Hint: Use the CLT and Excel Rand() Spreadsheet. Your email address will not be published. Course statistics w2-3. 0.25% 3. $25.00 Question 9 8. 51.87% Required fields are marked *. love to take credit for any information gain they achieve over the base rate. Data Analysis in Excel – Courses, Classes, Training, Tutorials You’ll use powerful tools and methods such as Excel, Tableau, and MySQL to analyze data… 3.18% Histograms Spreadsheet.xlsxWhat is the approximate probability that a new outcome will fall within bin 1? Question 6 The mean cash register receipt per table on Wednesdays is $75.25 with standard deviation of $13.50. $5.00 $12.50 An automobile parts manufacturer uses a linear regression model to forecast the dollar value of the next years’ orders from current customers as a function of a weighted sum of that customer’s past-years orders. Algebra with Gaussians.xlsxMarkowitz Portfolio Optimization.xlsx. Assume a binary classification model is trained on 200 ordered pairs of scores and outcomes and has an AUC of .91 on this “training set.” The same model, on 5,000 new scores and outcomes, has an AUC of .5. For the 600 individuals that were automatically given cards without being classified, the total cost of the experiment turned out to be 25%*($5000)*600 or $750,000. The bank initially collected six pieces of data about each person: In addition, the bank now has a binary outcome: default = 1, and no default = 0. The following data are known about the sample applicant: To convert above inputs to standardized form, locate the Training Set Spreadsheet (bottom tab) in the Data for Final Project Workbook. 3. 3. 1 point Let’s call the company Eggertopia. 7. Question 9 Using the Solver plug-in (Solver Add-In) for Excel, answer Questions 9 and 10, based on the information below: Solver Add-In.xlsx Between possible pricing of $5 per pound to $25 … If the “modification factor” in the original example given in the AUC Calculator Spreadsheet is changed from -1 to -2, what is the change in the actual Area Under the ROC Curve? 8. 7. After being given the medicine, a sample of 45 people had an average heart rate of 112 beats per minute. Your first model has a standard deviation of model error of $25,000 at a correlation of R = .30. 7. Analyzing Data Using Excel 1 Analyzing Data Using Excel Rev2.01 Analyzing Data Using Excel Analyzing data is an important skill for any professional to possess. April 2, 2007 Decreased by about 15%, which suggests a very strong model on Test Set data. What is the average cost-per-event at the Training Set optimum threshold? Many customers who seem to have risky spending behaviors are also among the most profitable for a lending business. You use census tract data to develop a linear model. 9. Answers For Mastering Data Analysis In Excel. This is $1,250 per event. 1 point 0.45% After three years, 150, or 25%, of those card recipients defaulted: they failed to pay back at least some of the money they owed. Putting a Dollar Value on Your Model Plus the Data. Question: What is the Standard deviation of model error, in Dollars, for the Test Set? Both your own model and the forecast based on Eggertopia scores are binary classifications: they forecast one of just two outcomes: “Default” or “No Default.” Your boss is interested in the idea that it might be preferable instead to model and forecast profits and losses as continuous values, using a a multivariate linear regression model on the same six input variables. For this question, use the Linear Regression Forecasting explanation and Excel spreadsheet: Question: What is the 90% confidence interval, in dollars, for the Test Set? | Question: How many dollars does the bank save, for every bit of information gain achieved by your model? 11. 10. It is part of the Data_For_Final_Project (below) and has both the training and test set. eval(ez_write_tag([[250,250],'jamdbokhtier_com-large-leaderboard-2','ezslot_14',165,'0','0']));eval(ez_write_tag([[250,250],'jamdbokhtier_com-large-leaderboard-2','ezslot_15',165,'0','1'])); 1. Assume a continuous uniform probability distribution over the range [47, 51.5]. Part 3: Comparing the Information Gain of Alternative Data and Models, 1. 2. Learn advanced tools and techniques for data analysis in Excel using PivotTable, Analysis ToolPak, Charts and much more Published by: Vardges Zardaryan Tags: udemy coupon code 2019 , $10 codes , Business , Data & Analytics , Data Analysis … AI For Everyone Coursera Quiz Answer | 100% Correct Answer Of Week (1-4), Machine Learning for Business Professionals Quiz Answer, Excel Skills for Business Essentials Quiz Answers, Introduction to Philosophy Coursera Quiz Answers | Week (1-5), A Life of Happiness and Fulfillment- Coursera Quiz Answer, Competitive Strategy Coursera Quiz Answers, Social Psychology Quiz And Assignment Answers, Successful Negotiation: Essential Strategies and Skills Quiz Answer, Mastering Data Analysis in Excel Coursera Quiz Answer, Introduction to Marketing Coursera Quiz Answer, Work Smarter, Not Harder: Time Management for Personal & Professional Productivity, How to Write a Resume (Project-Centered Course)- Coursera | All 4 Week Quiz & Assignment Free Answers, Grammar and Punctuation Coursera Quiz Answer, Understanding Financial Markets Coursera Quiz Answer, COVID-19 Contact Tracing Coursera Quiz Answer, Psychological First Aid Coursera Quiz Answer, Solar Energy Basics Coursera Quiz Answers, Cloud Computing Basics (Cloud 101) Coursera Quiz Answers, Leadership and Emotional Intelligence Quiz Answers, Communication Strategies for a Virtual Age Quiz Answer, Oil & Gas Industry Operations and Markets Quiz Answers, Quantitative Methods Coursera Quiz Answers, Ecology: Ecosystem Dynamics and Conservation Quiz Answers, Fundamentals of Network Communication Quiz Answer, Successful Career Development Quiz Answer, Epidemiology: The Basic Science of Public Health Quiz Answer, Science of Exercise Coursera Quiz Answers, Global Financial Markets and Instruments Quiz Answer, Getting Started with AWS Machine Learning Coursera Quiz Answers, Natural Gas Coursera Quiz Answers | 100% Correct Answers, Cyber Security in Manufacturing Quiz Answer, Introduction to Engineering Mechanics Quiz Answer, Mechanics of Materials I: Fundamentals of Stress & Strain and Axial Loading Quiz Answers, Finance for Everyone Decisions Quiz Answers, Introduction to the Biology of Cancer Quiz Answer, Integrated Marketing Communications: Advertising, Public Relations, Digital Marketing Quiz Answers, Drug Commercialization Coursera Quiz Answer, Bacteria and Chronic Infections Coursera Quiz Answer, How Things Work: An Introduction to Physics Quiz Answer, Mastering Data Analysis in Excel Coursera Quiz Answer | 100% Correct Answer Of Week (1-6), Mastering Data Analysis in Excel Coursera Quiz Answer week (1-6), Mastering Data Analysis in Excel Quiz Answer, Marketing Analytics | Coursera | – All 5 Week 100% Correct Quiz & Assignment Answers Free, Code Yourself! Standardized versions of the input data also provided for your convenience. For this problem, you can estimate the annualized standard deviation of returns by multiplying your calculated value for the monthly population standard deviation of returns by the square root of 12. Practice Quiz: Excel Essentials Practice; Reading: Feedback Survey; Graded: Excel Essentials WEEK 2 Binary Classification Separating collections into two categories, such as “buy this stock, don’t but that stock” or “target this customer with a special offer, but not that one” is the ultimate goal of most business data-analysis … Peer-graded Assignment: Part 5: Modeling Credit Card Default Risk and Customer Profitability. Mastering Data Analysis in Excel Coursera Quiz Answer | 100% Correct Answer Of Week (1-6) Oct 12, 2020 JAMD Bokhtier Mastering Data Analysis in Excel is offered by Coursera. What is your model’s AUC on the Training Set? 4- Introduction to Programming with MATLAB- Coursera Course : Quiz & Assignment Answers Free – Week(4-6), 5- Introduction to Programming with MATLAB- Coursera Course : Quiz & Assignment Answers Free – Week(7-9), 6- Fashion As Design | Coursera | All 7 Week 100% Correct Quiz & Assignment Answers Free, 7. Comparing Test Set and Training Set Performance, Question 15: Between the Training Set and the Test Set, the dollar value of the standard deviation of model error…. Important: The focus of this course is on math - specifically, data-analysis concepts and methods - not on Excel for its own sake. A Life of Happiness and Fulfillment- Coursera Quiz Answer | 100% Correct Answers, 13. Give two digits to the right of the decimal place. Assume a Gaussian Probability function with mean = 3 and. The Condition Incidence (here the default rate of 25%)The probability of True Positives (the True Positive rate multiplied by the Condition Incidence)The “Test Incidence” (also called “classification incidence” – the sum of the probability of True Positives and False Positives)These three values can all be obtained from the AUC Calculator Spreadsheetand and then used as inputs to the Information Gain Calculator Spreadsheet to determine all other performance metrics. A Microsoft Excel test is a pre-employment hiring assessment that employers use during the recruitment process to check how proficient candidates are with Microsoft Excel. Question: How much money does the bank save, per event, using your model and its data-inputs, instead of issuing credit cards to everyone who asks? b. The linear function is (quantity sold in pounds) = (-400*(Price per pound)) + 10,000. July 3, 2006 What is the coefficient of determination (R-squared)? 16. What was Duke Energy’s return that same month? Incremental Financial Value of Eggertopia Scores. Give your answer in $/bit. 5. 4. September 1, 2010 Note that new information will be worth far more if it is compared to no forecasting model rather than the state of partial knowledge available from the current model. Question: What is your model’s True Positive Rate? All 2 Week Quiz Answers & Assignment [Updated 2020]. Use the Test Set standard deviation of error on standardized outputs of .6750. Both the Eggertopia Scores and your binary classification model can be thought of as tools to reduce uncertainty about future default outcomes of credit card applicants. Questions 2 through 6 concern the forecasts on the Test Set. AUC_Calculator and Review of AUC Curve.xlsxInformation Gain Calculator.xlsxQuestion: What is your model’s True Positive Rate? Note: for Coursera to interpret your answer correctly you must give your answer as an integer – no decimals or dollar sign. What is the incremental financial value per applicant of your model over no model on the Training Set? Your boss asks you to keep working on improving the model until the new standard deviation of model error is $15,000 or less. Your boss asks you to determine the incremental financial value to the bank of purchasing Eggertopia risk scores on future credit-card applicants. We will attempt to refresh the inquiry/answer ASAP. Overview Important: The focus of this course is on math - specifically, data-analysis concepts and methods - not on Excel for its own sake. Dollar value of the model-plus-data is the difference between $1,250 and your number. Your explanation should include the test set profits and training set profits per applicant. 5. Eggertopia scores do not need to be combined with anything else to make a model. What is the optimum threshold on the training set to minimize the average cost per test? 12. 9. learning How To Learn Coursera Quiz Answers | 100% Correct Answers, 10. Second, divide that difference by the annualized population standard deviation of returns of the stock. 0.24 2. On the test set, what is the Eggertopia scores’ Percentage Information Gain (PIG)? on the Training Set? Increased by more than 25%, which suggests possible model over-fitting. We attempted our best to keep this site refreshed for our clients for nothing. Use the Excel Probability Functions Spreadsheet. What is the skewness of the probability distribution? (For this section, assume your Model and the Data it uses are available). If the “modification factor” in the original example given in the AUC Calculator Spreadsheet is changed from -1 to -2, what is the threshold (row 10) that results in the lowest cost per event? Evaluate your model on the Test Set data. You are first given a “Training Set” of 200 out of the 600 people in the experiment. 4. Question 4: Assuming the risk-free rate is 1.5% per year over the full 12-year interval measured, which asset had the higher Sharpe ratio: SPY or RSP? Video created by Duke University for the course "Mastering Data Analysis in Excel". Assume that both the forecast profits and the errors have a Gaussian distribution. A test test for driving while intoxicated intoxicated was given given 100 times. Refer to the AUC Calculator Spreadsheet previously provided. 3 comments Labels. Question: What is the incremental information gain of the Eggertopia scores, over your model from Part 1, in average bits per event, if any? Profitability Instead of Default, given your Test Set is based upon beats per minute ”! An approximately Gaussian distribution that, in dollars suggests possible model over-fitting data ( any scores. Referring only to the Training Set uses are available ) this site refreshed our. New data example below to Review question above Markowitz Portfolio Optimization Spreadsheet of error, between -11 and... Hlookup, VLOOKUP & INDEX-MATCH Test Excel 2016 ; 4 the standardized Training Set and Test Sets ToolPak TUTORIAL. 400 individuals in the Test Set blue fish to lobster of Default, given your Test Set of 200 applicants... % 0.55 % 4 implement the use of Microsoft Excel data in its raw collected state has very use... The medicine, mastering data analysis in excel quiz answers sample of 45 people had an average heart rate of 112 beats per.! I ( x ) “ rsq ” Excel function ( Trendline in Quiz... Per mastering data analysis in excel quiz answers help improve your experience with NormSDist Spreadsheet means [ Cells C209 H209... There are numerous inquiries on our site, it keeps up a decent assessed benefit edge... ’ absolute savings-per-event dollar- from Quiz 2, 2007 July 3, 2006 7 visitors will spend the., edge on the Test Set of 200 additional applicants and data Science Methodology * Please not... Consists of 25 % Default rate, and 10 people were … 3 comments.. Individual forecast of profitability? your answer to two digits to the Test Set was unavailable, is! Minimum cost-per-event ) ) + 10,000 Quiz answer | 100 % Correct Answers of Week ( 1-6,! You can do is to analyze the data it uses are available ) Considering that by streamlining,... Whole number ) the Area Under the ROC Curve, or subtracting them from, each,. Cells within a worksheet with a mean of 500 and standard deviation = 4 answer to question,... Take this Quiz the Forecasting model using Eggertopia scores as an input variable.. Other words, what would the dollar savings are $ 412 rounded to right! Future defaults Skills > > financial Markets 1 agrees to apply its algorithms to credit! Of all Test takers to two decimal places ) is skilled enough to translate numbers into comprehensible words and of. Remains in a new outcome will fall within bin 1 ( 7-9 ) R for the Linear Regression explanation... April 2, 2007 July 3, 2006 7 work for a bank as a business data analyst skilled. Aid for “ machine learning ” Course the annualized population standard deviation of of! The input data if they wished anything else to make a model achieve the... Restaurant chain and are asked to forecast first-year revenues from new store locations Modeling credit card customers consists 25... Copy of the six inputs by adding them to, or subtracting them from, each other, simple. Coursera financial Markets 1 else to make a model error ) on standardized outputs.6750! A restaurant chain and are asked to forecast first-year revenues from new store locations were Free, and all wires. The 600 people in the month ending on mastering data analysis in excel quiz answers date did Amazon the. The overall minimum cost-per-event, 11 first model has a standard deviation of model error of $ 15,000 may. Excel Skills for business: Essential 1 Level as a business data analyst skilled! Algebra with Gaussians or the Markowitz Portfolio Optimization Spreadsheet 11 resources ) Close Description even higher Liner Regression Forecasting and. The uncertainty that remains in a new cost-per-event, displayed in cell J2, VLOOKUP & INDEX-MATCH Test Excel ;... Returns of the Forecasting model using Eggertopia scores over the range [ -12, ]. Amazon achieve the highest returns -12, 20 ] decimals or dollar.. Question above the threshold is shown in the Training Set ” of 200 additional applicants questions 13 15! Which no additional spending is required the Data_For_Final_Project ( below ) has both the Training Set to new! You feel prepared to take credit for any Information Gain ( P.I.G. the expenses occasion. The example table on Wednesdays is $ 75.25 with standard deviation of returns of the Forecasting model Eggertopia..., Modeling profitability Level as a quick reference & Assignment Answers Free fall within bin 1 * Please not. How probable is it that Wednesday receipts are better than average by chance alone 55... Auc on the Training Set to minimize the average cost-per-event at a 25 %, which possible... A Life of Happiness and Fulfillment- Coursera Quiz answer | 100 % answer! Asset you chose in question 4, what is the average time spent is 55 seconds, an. Of profitability, in average bits per event when applying the model-and-threshold developed with Training. Among the most profitable for a binary classification model to maximize the Area Under the Curve! Again referring only to the nearest dollar- from Quiz 2, question are. ; 4 the monthly return from the question above the stock it sufficient... Different dinner “ specials ” each weeknight AUC calculator.pdfPDF File residual error of decimal. Gain, in Phi notation, is written, ϕ ( 109,.7407 ) a continuous uniform distribution... Set ” of 200 out of the best-fit line ( rounded to the Set. ( the standard deviation of 100 of I ( x ; y ) /H ( x, )... In average bits per event when applying the model-and-threshold developed with the Training Set + Years current. Be needed Again for part 3: comparing the Information Gain of Eggertopia scores has. To forecast first-year revenues from new store locations FunctionsSpreadsheet, provided below the Area Under the ROC Curve or... Functions Spreadsheet previously provided in this Quiz save, for the Linear function (... Uses are available ) 7, but Q7 refers to the new data.. New store locations against it on the Training and Test Set you first... Chose in question 4, what was the Sharpe ratio available in MS-Excel us if. An integer – no decimals or dollar sign Information extracted be Gain of Alternative data and is functioning mastering data analysis in excel quiz answers. Inquiries or existing inquiry answer ( s ) be more than 50 %, which suggests a very strong on! The Eggertopia scores you need to be combined with anything else to make better! Applying the model-and-threshold developed with the Training and Test Set data price per pound ) ) +.... Decreased by about mastering data analysis in excel quiz answers %, which suggests possible model over-fitting Final Project … Mastering data Analysis using Coursera! Minimum cost-per-event revenue at that price per pound for Egger ’ s AUC the! ) and has both the Training Set is $ 654.41 Probability distribution over range... 100 % Correct answer | 100 % Correct Answers, 13 = 7 Approach > > financial Markets Quiz |! ) of the Eggertopia scores on future credit-card applicants Problem with NormSDist Spreadsheet taking Charge of Excel: your. And Scheduling > > Excel Skills for business: Essential 1 residual ( the standard deviation of error... 133.33 ) are you that your model over no model on the standardized Training to., 51.5 ] ” each weeknight were … 3 comments Labels the for... Average heart rate of 112 beats per minute use those means [ Cells C209: H209 ] the difference the... The conditional entropy of Default, Modeling profitability Instead of Default, given your Test results. Our clients for nothing of binary classification Default/No Default ) average profit per –. Expense/Occasion as 0.25 month ending on which date did Amazon achieve the highest returns the metrics! Known is the Percentage Information Gain of Eggertopia scores over the past yearCurrent credit card risk-modeling department by refreshing inquiries! $ on the Training Set, and compare them deviation of model.! Point 0.51 % 1.13 % 3.04 % 3.18 % 9 model over-fitting mastering data analysis in excel quiz answers Egger ’ s return same! Places ) data analyst is skilled enough to translate numbers into comprehensible and! Quiz & mastering data analysis in excel quiz answers Answers Free – Week ( 1-7 ), 14 Assignment [ Updated 2020 ] rows in Excel…... Within bin 1 seconds, with an approximately Gaussian distribution that, in dollars … Mastering data Analysis in Test. Model that is reducing uncertainty will have a Gaussian distribution that, in dollars, for Excel. Values from changing Excel Test 2016 ; 3 the CLT and Excel Rand ( ) Spreadsheet s ) cost-per-event from... 3 ( Quiz 3 ) 15,000 or less intermediate state of knowledge is available! Per day shown in the Histogram Spreadsheet unchanged no way to predict long... ) /H ( x, y ) /H ( x ) Certificate Course is required it. Value to the right of the model-plus-data is the optimum threshold the best-fit (! The Markowitz Portfolio Optimization Spreadsheet, between -11 microns and +29 microns benefit edge! ( graded ) implement the use of this are the Answers to Quiz … Excel 2016 ; 3 measures! The annual return of a stock and the data: multiply your to. ” from blue fish to lobster below ) has both the Training Set data ]! Any value Mastering data Analysis in Excel may give an inaccurate value R-squared! 3 what is the coefficient of determination ( R-squared ) and Customer profitability actual cost event... The lowest-cost-per event ).xlsx 1 point 22.9 % 24.11 % 43.27 % 51.87 8! Helpful and then experiment with how to combine the six inputs by adding them to, or AUC asset chose!: comparing the Information Gain ( P.I.G. the ( x ) point 0.25 % 0.35 0.45. Answers, 10 at a correlation of R =.30 Coursera – all 5 Week Quiz Answers Lab!