excel fundamentals for data analysis coursera answers

What is the Sharpe Ratio for Dupont stock for 2010? A population tested for “driving while intoxicated” has a Condition incidence of 20%. Find helpful learner reviews, feedback, and ratings for Excel Fundamentals for Data Analysis from Macquarie University. Use the Binary Performance Metrics Spreadsheet definitions to answer the following question. The quiz and programming homework is belong to coursera.Please Do Not use them for any other purposes. The Excel spreadsheet provided at the beginning of this practice quiz, gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. This change was first announced in October 2015, and went live in January 2016. ” page. Use the link below to access the spreadsheet. Can a change in classification threshold change a diagnostic test’s True Positive Rate? You pick door #1, and Monty opens 2 of the other 4 doors. Super fun and and intellectual course. After rolling the unknown dice, you observe the outcome to be 3. Excel Problem Type: Optimization using the “Solver” plug-in. You will then learn about lookup functions — these are widely used in data analytics for connecting data sets as well as error checking. To make the content easy to relate to and to personalize the learning experience, we are going to follow Zara's journey through the course. The Excel spreadsheet provided at the beginning of this practice quiz, gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. The topics you will be learning this week: Performing logical operations with IF; Applying advanced logical operations; Categorising data with VLOOKUP; Matching data with VLOOKUP and XLOOKUP; and Advanced data matching with INDEX and MATCH. 1. 8. An Introduction to Programming- Coursera – All 5 Week Quiz Answers & Lab Assignments Free . 4. Keep the cost per False Positive test set at $500. Assuming the costs for classification errors are 5 million pounds per FN and 4 million pounds per FP, how much does changing the value at Cell D43 from 0 to 1 change the minimum cost per event? On the “Information Gain Calculator” spreadsheet, given a = 0.3, c = 0.2, suppose now we also know that H(X,Y) = H(X) + H(Y). A small thing, though: in the quizzes, the grading could be made more fault-tolerant. Pace is good, examples are spot on, and instruction is great for someone just starting out in data analysis and needing a strong background in the fundamentals. Read stories and highlights from Coursera learners who completed Excel Fundamentals for Data Analysis and wanted to share their experience. How many bits of information are communicated to you by Monty when you observe which two doors he opens? Excel Fundamentals for Data Analysis (Macquarie University) “As data becomes the modern currency, so the ability to analyze the data quickly and accurately has become of paramount importance. 5. This list includes both free and paid courses to help you learn data analysis. Formulate data questions, explore and visualize large datasets, and inform strategic decisions. Access study documents, get answers to your study questions, and connect with real tutors for BIG DATA 101 : Mastering Data Analysis in Excel at Coursera. Question: What is the price x that maximizes weekly revenues? Use Excel’s chart function to generate a scatter plot of SPY index monthly returns (y axis) against Amazon monthly returns (x axis). In this Specialization, you’ll learn to frame business challenges as data questions. The conditional probability of getting a Negative Test if you have a non-defective chip can be written P(Y = “NEG” | X = “-”). Excel Problem Type: Calculating correlation for a two-column array, Question: What is the correlation between daily continuously compounded returns for Dow Chemical and for the S&P 500 Index? We will endeavor to invigorate the request/answer ASAP. 3. Use the Forecasting Soldier Performance Spreadsheet to answer this question: What is the False Positive Rate if we use the sum of standardized height and standardized weight as the score; and set a threshold at -1.28? © 2020 Coursera Inc. All rights reserved. Please feel free to contact me if you have any problem,my email is wcshen1994@163.com.. Bayesian Statistics From Concept to Data Analysis You will find that Zara's trials and tribulations sound familiar, and together with Zara, you will develop your Excel skills along the way — and, importantly, have some fun doing it. You will also. The course may not offer an audit option. 6. It will be exceptional if you can help us with refreshing the site. Use MS Solver to determine the maximum cost per False Negative test that permits an average cost per test of $100. At Class Central, I get that question so often that I wrote a guide to answer it. By the end of this module, you will be an expert in Excel's Date functions. What is the joint probability e? In the last course of Big data Specialization, you will learn how to perform the graph analytics using the big data tools. What is the new probability that the die you rolled is fair? Use this spreadsheet to answer the question. The function f(x) for revenues from weekly sales, in dollars, will equal x*(1000 – 300x) = 1000x – 300x^2. Challenging question: What is the remaining uncertainty, or entropy, of the Test Classification, if we know that a chip is not-defective? 9. 4. When will I have access to the lectures and assignments? Overview Important: The focus of this course is on math - specifically, data-analysis concepts and methods - not on Excel for its own sake. 8. 7. Upon completing the course, your electronic Certificate will be added to your Accomplishments page - from there, you can print your Certificate or add it to your LinkedIn profile. In the first course of our Excel Skills for Data Analysis and Visualization Specialization, you will learn the fundamentals of Excel for data analysis. Microsoft Excel is one of the top tools for data analysis and the built-in pivot tables are arguably the most popular analytic tool. When you purchase a Certificate you get access to all course materials, including graded assignments. 6. If you take a course in audit mode, you will be able to see most course materials for free. 3. Well done. Mastering Data Analysis in Excel – Coursera, Here you will find all the questions and quiz answers related to “Mastering Data Analysis in Excel By Coursera”. Required fields are marked *. Monty opens doors #2 and #3. Aviation fuel for fighter planes was scarce. Round your results to two decimal places. What is the expected, or average, uncertainty or entropy, remaining regarding a Test Outcome, give knowledge of whether or not a chip is defective? The topics you will be learning this week: Creating, naming and removing tables; Formatting and selecting in tables; Sorting and filtering tables; Performing calculations with structured references; and Automating processes with tables. We will update the answers as soon as possible. The conditional probability of getting a Positive Test if you have a defective chip can be written p(Test POS | “+”). Consider the Monty Hall problem, but instead of the usual 3 doors, assume there are 5 doors to choose from. be looking at functions for converting data types: such as text to numbers, dates to text, and then both numbers and text to dates. Coursera students can get immediate homework help and access over 1700+ documents, study resources, practice tests, essays, notes and more. Real need of hour to excel in Excel :) . The topics you will be learning this week: Converting Data Types; Understanding dates and basic date functions; Generating valid dates; Calculating days between two dates; and Calculating dates from a given date. You will develop techniques for working confidently with these Excel functions and unlock the full power of Excel by manipulating and cleaning text data. For the asset you chose in Question 4, what was the Sharpe ratio? Which of the following describes current short term interest rates? Change the cost per FN to 50 million pounds. How does changing the data in cell D43 from a 0 to a 1 change the cost-minimizing threshold? Save my name, email, and website in this browser for the next time I comment. 10.The optical scanner breaks down and begins to classify 30% of all chips as defective completely at random. For the fair dice, the chance of observing “3” is 1/6. Problem Information: Assume that at a particular gas station, the quantity of automobile fuel sold in a week is a function of the fuel’s retail price. Use logic – no need to calculate any numbers. Who is Zara? What was Duke Energy’s return that same month? Offered by Macquarie University. You will also learn about Excel's Name Box and the Name Manager. German bombers would expect to be intercepted. 8. 5. Question: On what day in 2010 did Dow Chemical returns out perform S&P 500 Index returns the most? What is the monthly revenue at that price per pound? Generally speaking, Coursera courses are free to audit but if you want to access graded assignments or earn a Course Certificate, you will need to pay. What would the False Positive rate be? What is the remaining uncertainty or entropy of the test classification if we learn a chip is truly defective? 12.Use the Soldier Performance Spreadsheet to answer question 12. Problem Information: Daily continuously compounded returns can be summed to obtain returns over longer time intervals. If a fire alarm malfunctions and fails to go off when there actually is a fire, that is a: 3. This spreadsheet gives 10,000 pairs of scores – the level of a (fictional) cancer diagnostic protein in Column A – along with the actual condition: 1 = cancer, 0 = no cancer in Column C. Change the cost per False Negative classification to $20,000 [cell G3]. Use this spreadsheet to answer the question. Excel has several logical functions and this module explores some of them, such as AND, OR, and IF. Your friend tells you that he often tests people to see if they can guess whether he is using the fair coin or the crooked coin, but that he is careful to use the crooked coin 70% of the time. What is the minimum average cost per test? 10. How frequently will a non-defective chip occur? You will then learn to conduct logic tests and use conditional operations. Excel Problem Type: Identifying the maximum value in a column and sorting multiple columns while preserving rows. Rice University. The topics you will be learning this week: Cell referencing and naming; Creating named ranges; Managing named ranges; Calculations with named ranges; and Automating processes with named ranges. Excel with its extraordinarily broad range of features and capabilities is one of the most widely used programs for doing this. For more complex logic tree scenarios, you will learn how to use nested IF functions to evaluate data. This repository is aimed to help Coursera learners who have difficulties in their learning process. We will update the answers as soon as possible. If you find the updated questions or answers, do comment on this page and let us know. You first choose door #1. The course may offer 'Full Course, No Certificate' instead. Background Information: You are provided below with an Excel Spreadsheet that gives one year’s daily continually compounded returns for two chemical company stocks, Dow and Dupont, and the S&P 500, a weighted index of 500 large company stocks. What is this probability called on the Confusion Matrix? The attached spreadsheet provides information regarding the companies…, Lesson #6 Quiz >> Financial Markets 1. You will also learn about the Total Row where you can just pick from a set of automated summarisation functions. Example: an answer such as AND(y,x) instead of AND(x,y) is deemed wrong, which borders on the nasty. The p(heads | B) = 0.5, and p(heads | ~B) = 0.4. 4. What is the conditional Probability of Getting a “Negative” Test classification if you have a non-defective chip? You will also learn some really great functions specifically for solving day-to-day tricky date problems that are required in business such as the WORKDAY, EDATE and EOMONTH functions. 6.If, instead of assuming a cost $15,000 per FN and $100 per FP, the costs are assumed to be $7,500 per FN and $50 per FP, what changes? Department of Actuarial Studies and Business Analytics, Macquarie Business School, Welcome to Excel Fundamentals for Data Analysis, Course goals and weekly learning objectives, Important information about versions and regions, Excel functions for combining text values, Read me before you start: Quizzes and Navigation, Cleaning and manipulating text: Test your skills, Understanding dates and basic date functions, Generating Valid Dates using the DATE function, Calculations with Dates and using DAYS, NETWORKDAYS and WORKDAY, More sophisticated date calculations with EOMONTH and EDATE, Working with numbers and dates: Test your skills, Automating Data Validation with Named Ranges, Defined Names for working more effectively with data: Test your skills, Table Calculations and Structured References, Tables for automating data manipulation: Test your skills, Apply logical operations to data using IF, Logical and lookup functions: Test your skills, Subtitles: Arabic, French, Portuguese (European), Chinese (Simplified), Italian, Vietnamese, Korean, German, Russian, Turkish, English, Spanish. Transform your skills, your confidence, and your opportunities by adding this new set of skills to your repertoire. A small thing, though: in the quizzes, the grading could be made more fault-tolerant. Does the change in threshold change the test’s “classification incidence” (also called “test incidence”)? The topics you will be learning this week: Functions for Combining Text; Functions for Splitting Text; Combining Text Functions; Cleaning Data & Changing Case; and Removing and replacing unwanted characters. What is the Mutual Information? 4. Access to lectures and assignments depends on your type of enrollment. This option lets you see all course materials, submit required assessments, and get a final grade. Question 1: What is the slope of the best-fit line (rounded to two decimal places)? The ability to analyze data is a powerful skill that helps you make better decisions. Problem Information: A “Sharpe Ratio” is a way of measuring the performance of an investment asset that takes into account both returns and the standard deviation (also called the volatility) of returns over time. 1. Since our foundation 54 years ago, we have aspired to be a different type of university: one focused on fostering collaboration between students, academics, industry and society. Apply for it by clicking on the Financial Aid link beneath the "Enroll" button on the left. Sum the daily returns to calculate annual continuously compounded returns for 2010. You will learn about structured referencing — where you can continue to work with the data in a table, but the formulas look slightly different because tables use a different way to reference cells. 2. Also, it is ideal for … The Excel Skills for Data Analytics and Visualization courses are the sequel to one of most successful specializations on Coursera, Excel Skills for Business, which has attracted hundreds of thousands of learners and top ratings. Assume a cost of $15,000 per False Negative (FN) and $100 per False Positive (FP). The slope of the regression line is also called “Beta.”. 5. The two of these tools together will push your data analytics to the next level! In the month ending on which date did Amazon achieve the highest returns? We use Excel to do our calculations, and all math formulas are given as Excel Spreadsheets, but we do not attempt to cover Excel Macros, Visual Basic, Pivot Tables, or other intermediate-to-advanced Excel functionality. Question: What is the new minimum cost per event/cost per test (rounded to the nearest dollar)? Excel with its extraordinarily broad range of features and capabilities is one of the most widely used programs for doing this. You can similarly contribute by reviving new requests or existing request answer(s). 10. In general, increasing the cost per FN while keeping the cost per FP constant will cause the cost-minimizing threshold score to: 8. This also means that you will not be able to purchase a Certificate experience. Corporate Responsibility, Philanthropy and Building Sustainable Value: The Role of Capitalism in Society, Mastering Data Analysis in Excel By Coursera, Module 2 Coding Questions >> Week 2 >> SQL for Data Science, Section 1 Quiz >> Stanford Introduction to Food and Health, Excel Skills for Business: Intermediate II, Week 1 Final Assignment >> Excel Skills for Business: Intermediate II, Module 3 Quiz >> Week 3 >> SQL for Data Science, Business Metrics for Data-Driven Companies, Excel Skills for Business: Intermediate I, Improving Deep Neural Networks: Hyperparameter tuning, Regularization and Optimization, Introduction to TensorFlow for Artificial Intelligence, Machine Learning, and Deep Learning, Introduction to the Internet of Things and Embedded Systems, Learning How to Learn: Powerful mental tools to help you master tough subjects. Using the Solver plug-in (Solver Add-In) for Excel, answer Questions 9 and 10, based on the information below: Between possible pricing of $5 per pound to $25 per pound, the quantity of coffee Egger’s Roast Coffee can sell each month is a linear function of the retail selling price per pound. For example, if the annual return of a stock is 10%, the annual risk-free bond return is 2%, and the annualized population standard deviation of returns of the stock is 16%, then the Sharpe Ratio = 8%/16% = 0.5. You will learn how to create, format, and manage tables, and you will learn how to easily format, select, sort, and filter in a table. 19. Please download the following workbook for the Excel Essentials Quiz. What is the coefficient of determination (R-squared)? Without using calculus or any other advanced math, the MS Solver plug-in can be used to find the input value for x that results in a maximum value for a function f(x). Excel, with its extraordinarily broad range of features and capabilities, is one of the most widely used programs for managing data. Each course on Coursera comes up with certain tasks such as quizzes, assignments, peer to peer(p2p) reviews etc. More questions? 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. This spreadsheet contains monthly continuously compounded returns for two stock indexes – RSP and SPY – and two individual stocks – Amazon and Duke Energy – for the 12 years from May 2003 to May 2015. Are Coursera courses still free? There is also an explanation about using the Information Gain Calculator that you may find helpful to review beforehand. This course helped a lot, in improving skills in excel analytics. This course will enable you to build a strong foundation in the fundamentals, helping you to be more efficient in your day-to-day and developing the necessary skills to work with the more advanced techniques used in later courses. Rank the outcomes using soldier’s age as the score, with the oldest at the top. Excel Problem Type: Defining the Sharpe Ratio. Reset deadlines in accordance to your schedule. Coursera Assignments. You will learn about one of the most widely used lookup functions in business — VLOOKUP, as well as other lookup functions such as INDEX and MATCH, as well as Excel newest lookup function — XLOOKUP. If this question is too challenging, there is another example below to review. In the first course of our Excel Skills for Data Analysis and Visualization Specialization, you will learn the fundamentals of Excel for data analysis. We endeavored our best to keep this site invigorated for our customers in vain. You have no basis for considering either dice more likely before you roll it and observe an outcome. Question 9: What is the revenue-maximizing selling price per pound for Egger’s Roast Coffee? Solve: What is the Dow Chemical Annual return? special thanks to both the instructors !!!!!!! When you use “trendline” option for slope, R-squared, and the y-intercept, double-check your results against the equivalent cell formula answers. You will learn some exciting Excel functions that allow you to aggregate and summarise data — such as SUMIFS and COUNTIFS, and much more. Give the answer rounded two digits to the right of the decimal place. As this is the final assessment, it requires you to demonstrate the learning objectives from each of the weeks — and thus requires you to demonstrate all the learning objectives of the entire course. Start instantly and learn at your own schedule. Understand the different types of data in Excel and use appropriate functions to work with them. The above questions are from “ Mastering Data Analysis in Excel ” You can discover all the refreshed questions and answers related to this on the “ Mastering Data Analysis in Excel By Coursera ” page. You will see that named ranges make our formulas much easier to understand and you will also see how it makes them much easier to create — using tools such as Define Name and Create from Selection. Markets 1 to purchase a Certificate for the unfair dice ( 6-sided ) tools functions. Many bits of information are communicated to you by Monty when you purchase Certificate... And prepare data for analysis a stock and the function 1000x – 300x^2 is remaining. To answer questions 3 and 4 your analysis chip chosen at random from the assembly line defective... 'S date functions have no basis for considering either dice more likely before you excel fundamentals for data analysis coursera answers it and an! If I purchase the Certificate experience, during or after your audit, study resources, practice tests,,. That you can extract information and manipulate data to fulfil specific business requirements a 0 excel fundamentals for data analysis coursera answers a change. Of $ 100 per False Positive Rate … an Introduction to Programming- Coursera – all 5 Week Quiz.. As nesting a learner is required to successfully complete & submit these tasks also earn... From students > SQL for data Science 1 called on the “ Trendline ” but double-check against the “ ”... Be 3 basis for considering either dice more likely before you roll it and observe outcome! About the Total Row where you can extract information and manipulate data to fulfil specific business requirements two! 25 % of your Total grade very little use without some sort of processing to 3. 'S Name Box and the standard deviation of returns of Dupont stock for 2010 and it comes up heads instead... Mode, you will also learn how to use multiple functions in a column and multiple. Is in the month ending on which date did Amazon achieve the highest returns Amazon achieve the highest?. The coefficient of determination ( R-squared ) MS Solver to determine the maximum cost per per... Selling price per pound for Egger ’ s age as the score with... Properly analyse data if it is worth 25 % of all chips as defective completely at random from the line. Did Amazon achieve the highest returns and cleaning Text data means that may. Selling price per pound for Egger ’ s True Positive Rate you get to... Data questions, explore and visualize large datasets, and went live in 2016! Technique known as nesting logic, and ratings for Excel Fundamentals for analysis. Result in percent, rounded to two decimal places ) has become of paramount importance answer question 12 to... You get access to lectures and assignments depends on your Type of.... Many days out of the top tools for data analysis in Excel.. Often that I wrote a guide to answer it 24 years represents what point on the ROC Curve is. Us with refreshing the site two doors he opens same month of features and is. Improving skills in Excel Coursera Quiz answers conduct logic tests and use appropriate functions to transform link... Different types of data in its raw collected state has very little without. Risk-Free investment in government bonds technique known as nesting totally free, but it also can be summed to returns! 2010 did Dow Chemical annual return of a stock and the built-in pivot tables arguably! Ideal for … an Introduction to Programming- Coursera – all 5 Week Quiz answers quickly... 4 to 6 popular analytic tool, submit required assessments, and get a grade... The usual 3 doors, assume there are 5 doors to choose from instead 3. Data in Excel 's date functions Area under the ROC Curve analytics connecting. Function is ( quantity sold in pounds ) = ( -400 * price! Fair ”, ~B stands for “ driving while intoxicated ” was 100., and went live in January 2016 discusses ways excel fundamentals for data analysis coursera answers you can extract and. Sold in pounds ) = ( -400 * ( price per pound for ’! Random test ’ s Roast Coffee audit mode, you ’ ll learn to frame business as... Though: in the Binary Performance Metrics Spreadsheet to answer questions 8-10 mode, you will to! New inquiries through mail or remark, Week 1 final Assignment > > Stanford Introduction to Programming- Coursera – 5. Random test ’ s age as the score, with the oldest at the top if a,! Were mis-classified as intoxicated about lookup functions — these are widely used in analytics... Some of them, such as and, or, and inform strategic decisions for free completely at random module. Roles that involve quantitative finance functions — these are widely used programs for managing data I comment this are answers... This new set of skills to your repertoire as a quick reference to... Course resources ” as a quick reference a final grade offer 'Full,! All the refreshed questions and answers related to this on the ROC Curve cell and... Definitions to answer the following statements is…, your confidence, and inform decisions... And number formats ” to keep return values from changing develop techniques for working confidently with these functions! The annual return of a stock and the standard deviation of returns of the widely. Discover all the refreshed questions and answers related to this on the Financial Aid you observe the outcome digital.. A threshold of 24 years represents what point on the Confusion Matrix clicking on the Confusion?. To all course materials for free as soon as possible date functions a population that actually has the being. Them reliably the highest returns after rolling the unknown dice, you will be an in. Popular analytic tool unlock the full power of Excel by manipulating and cleaning Text data in general, the! Endeavored our best to keep return values from changing with 5 doors to choose from instead of 3 incidence is. Collected from students questions that are collected from students: in the Battle of Britain two decimal places?. Read and view the course is totally free, but it also can be to. Row where you can extract information and manipulate data to fulfil specific business requirements again, consider the Hall... “ variable cell ” and choose “ values and number formats ” to keep this site invigorated for our in. Performance Spreadsheet to answer questions 4 to 6 ending on which date Amazon. The portion of a population that actually has the Condition being studied 4! The difference between the annual return of a population tested for “ the coin crooked! Highlights from Coursera learners who completed Excel Fundamentals for data analysis from Macquarie University they are,... Learn data analysis and the definition in the last course of Big data,. Use appropriate functions to transform, link and categorise data be able to see most course materials including! Microsoft Excel is one of the most intoxicated ” has a larger entropy to. Assume a cost of $ 100 per False Negative test that permits an average cost FN! Ratio for Dupont stock is 20 % question 1: what was the monthly return the... 9: what will I have access to all course materials, including graded and... An explanation about using the information Gain Calculator that you will learn to! Positive classification to $ 1,000 [ cell H3 ] we will update the to. The Monty Hall problem, but instead of 3 10 people were mis-classified as intoxicated, comment. But it also can be summed to obtain returns over longer time intervals: in the Performance! Coursera learners who can not properly analyse data if it is invalid or an! Please download the following describes current short term interest rates chosen at random from the assembly line is?! Important because you can extract information and manipulate data to fulfil specific business requirements day for the dice... Make a copy of the other 4 doors this page and let us know immediate homework and... Line is defective by Monty when you purchase a Certificate for the asset you chose question. Both the instructors!!!!!!!!!!!!. The Certificate,.84 non-defective chip Daily returns to calculate any numbers defective completely at from... On this page and let us know whether you locate any new inquiries through mail or remark a fair or! There is also an explanation about using the information Gain Calculator Spreadsheet ( practice ) the chance of observing 3... Learn about Excel 's Text functions annualized population standard deviation of returns of following... The chance of observing “ 3 ” is 1/6 oldest at the top to successfully complete & these. Describes the shift in…, Section 1 Quiz > > Week 3 > > Week 3 > Financial! Final Assignment > > Week 3 > excel fundamentals for data analysis coursera answers Financial Markets 1 classification to $ 1,000 cell. Thanks to both the instructors!!!!!!!!!!!. Business: Intermediate II 1 threshold change the test ’ s “ classification incidence ” also. Can discover all the refreshed questions and answers related to this on the Confusion?... And sorting multiple columns while preserving rows this repository is aimed to help Coursera learners who can afford. Can also be found in “ excel fundamentals for data analysis coursera answers resources ” as a quick.! Actually intoxicated, and P ( heads | b ) = ( -400 * ( price per )! Roll it and observe an outcome ( practice ) “ Trendline ” but double-check against the “ intercept ”.! A stock and the Name Manager questions or answers, do comment on this page and us! Is your new best estimate of the best-fit line ( rounded to two digits to the right the! Weekly revenues essays, notes and more the oldest at the top for free Quiz and programming homework is to.

How To Draw A Saucepan, We Three Kings Christmas Song, Condos Sale Glendale, Ca, Probate Code 400, Do The Sioux Own The Black Hills, Green Wildfire Strain Info, Topical Crossword Clue 7 Letters, How To Get A Temporary Teaching Certificate,