Introduction to Spreadsheets and Models Coursera Quiz Answers

All Weeks Introduction to Spreadsheets and Models Coursera Quiz Answers

Introduction to Spreadsheets and Models Week 1 Quiz Answers

Quiz 1: Module 1 Quiz: History, Formulas, Functions and Errors

Q1. The story of Bricklin and Frankston and the emergence of personal computers provides evidence suggesting that ______________

• Spreadsheets were not originally used for accounting applications
• The personal computer existed before Apple
• Hardware sells software
• All of these are true
• Software sells hardware

Q2. If the range A1:A4 consists of the values 1,2,3,4 and the range B1:B4 consists of the values 9,8,7,6, what is the value of the sum-product (B1:B4, A1:A4)?

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, and write a statement in cell D5 that would trigger a 100-unit order from a supplier.

Answer: The value of the sum product (B1:B4, A1:A4) is 50.

Q3. Review the step function in the spreadsheet below. Given the conditional pricing formula shown in

C3, what’s the value of the order total in C5?

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low Enter a formula in a spreadsheet cell and write a statement in cell D5 that would trigger a 100-unit order from a supplier.

Answer: The value of the order total in C5 is 275.

Q4. Evaluate the formula 3^(1+2)*4-5/7 using the order of calculation followed by Excel or Sheets. Round to 2 decimals, and use a decimal point (“.”) in your answer.

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low Enter a formula in a spreadsheet cell and write a statement in cell D5 that would trigger a 100-unit order from a supplier.

Answer: The result of the formula 3^(1+2)*4-5/7 is approximately 33.71.

Q5. Below is a spreadsheet showing product sales for the past 6 months. Assume we want to write a formula in cell C10 that we can copy later across 6 columns representing the past 6 months of sales. The formula needs to apply the unit cost of goods sold found in cell C2. Enter the formula for cell C10 using spreadsheet syntax and the appropriate absolute and relative addresses to calculate the total cost of goods sold for that period.

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: The formula for cell C10 to calculate the total cost of goods sold for that period would be: =$C$2*B10

Q6. Which of the following formulas from the spreadsheet below will result in a circular reference error? Select all that apply.

• Formula in cell H10=sum(H5:H10)
• Formula in cell C10=sum(C7:C10)*$C$2
• Formula in cell C10=sumproduct(C5:H7, C2)
• Formula in cell C14=C10*C4-C10*C2
• All of these will result in a circular reference error

Q7. In the cashflow spreadsheet below, which of the following cell addresses contains a potentially important output function for the cashflow model? Select all that apply.

• B8
• B13
• B6
• B7
• B12

Q8. In this analysis of historical sales, which cell formula tells you the level of variation in the spread of sales numbers from low-performing months to high-performing months?

None of these show the spread of sales numbers

• C8
• C10
• C11
• C9

Q9. Consider the following spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low.

Using appropriate syntax for entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier when the inventory for the period falls below 75 units.

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: =IF(C4<75, 100, 0)

Q10. The term array is closest in meaning in Excel to:

• Spreadsheet
• Standard Deviation
• Cell
• Sumproduct
• Range

Introduction to Spreadsheets and Models Week 2 Quiz Answers

Quiz 1: Module 2 Quiz: Classic Models

Q1. How would you improve the transparency of the spreadsheet model below by separating data from formulas? For example, how would you rewrite the formula shown in C10 in a form that would be both more transparent and could be easily copied through the multi-year period in columns C through F?

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: =B10 * UnitCost

Q2. The spreadsheet below models the costs and potential revenues of manufacturing speakers, assuming that the full order is sold. In the spreadsheet, four different sets of cells have been designated as A, B, C, and D.

Assume there is an expedited manufacturing option that results in a 50% reduction in time for customers to receive their goods. However, that expedited service increases the order cost by 25%.

Which designated set of cells is the logical place to insert that potential 25% cost increase as a variable?

• D
• B
• C
• A
• None of these

Q3. Refer again to the spreadsheet in Q2. Again, assume there is an expedited manufacturing option that results in a 50% reduction in time for customers to receive their goods. Still, that expedited service increases the cost of the order by 25%.

Which designated set of cells is the logical place to insert a yes/no variable to indicate that the order is to be expedited?

• A
• None of these
• C
• D
• B

Q4. Refer again to the spreadsheet in Q2. If we wanted to add a variable to this model to calculate the average unit cost as an important output to monitor, which designated set of cells is the logical place to insert that output?

• None of these
• B
• D
• C
• A

Q5. Refer again to the spreadsheet in Q2, and assume that the following range names have been created: number_ordered (cell B5); discount51 (cell B11); discount101 (cell B12); and cost_per_cabinet (cell B15).

What is a formula for cell B16 that would use these range names?

Answer: =IF(number_ordered<51,0,IF(number_ordered>100, cost_per_cabinet*discount101, cost_per_cabinet*discount51))

Q6. What is a useful source of data for projections and forecasts, such as sales forecasts or costs of goods sold?

• Data from market research firms
• Your competitive market research
• All of these are true
• Your own historical data
• Public corporations’ operating data

Q7. Assume that you have a company that assembles final products from a large variety of components that are supplied by factories located in various parts of the world. In the past you have experienced disruptions in the flow of your supply of components as the result of bad weather, such as a strong El Niño phenomenon, as well as other uncontrollable events. You have backup suppliers, but each has different capacities and delivery schedules. So you created a model that includes sets of assumptions about changes in suppliers and delivery times in the event of unusual disruptive events. The Excel tool specifically designed for your use in this case is:

• Sensitivity analysis
• What-if analysis
• Data analysis toolpak
• Linear programming models
• Scenario manager

Q8. Joseph used the spreadsheet below to check the sensitivity of Innovative Speakers profit to increases in prices of components by suppliers next year. First he raised the cost of cabinets by 20% in cell B12, then noted the profit change in B6. Next he raised the cost of diaphragms in C12 by 20%, then noted the profit change. He repeated these steps for electronics and assembly. What mistake is Joseph making?

• All of these are true
• Didn’t reset assumptions to original values
• No use of historical cost data
• Didn’t test changes in retail price
• Didn’t test conditional logic in model

Q9. In the model below, Amy used sensitivity analysis in a cashflow projection to check how her assumptions affect her need to finance operations through credit or loans during the coming year.

This question has two parts. First, think about which one outcome variable is most clearly related to the need to plan for financing operations. Then identify which assumption she should make sure she has estimated correctly, since the outcome variable is most sensitive to changes in that assumption. The range B4:F6 show current best assumptions. Range B8:F18 show 10% changes in the three assumptions. Provide as your answer the assumption variable you’ve identified.

• Unit price
• Margin
• Advertising budget
• Return rate
• All of these are true

Q10. The primary limitation of deterministic models is that:

• They are often inaccurate representations of the variance in the real world
• Their forecasts aren’t situated in historical data
• They can’t model exponential growth or decline
• All of these

Introduction to Spreadsheets and Models Week 3 Quiz Answers

Quiz 1: Module 3 Quiz: Probability, Correlation, and Regression

Q1. Randbetween generates random numbers based on what kind of probability distribution:

• Normal
• Uniform
• Bernoulli
• Discrete
• All of these

Q2. The Data Analysis Toolpak in Excel and Sheets generates random numbers based on what kind of probability distribution:

• Discrete
• Normal
• Bernoulli
• Uniform
• All of these

Q3. Below is a probability tree outlining 3 steps to introducing a new product – a market research study, a test market initiative, and a national marketing campaign. Calculate the probability of success of all three steps. Express probability as a decimal with two decimal places. Example 50% = .50

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: The combined probability is 0.4 * 0.6 * 0.7 = 0.168, which is expressed as 16.8% when rounded to two decimal places.

Q4. Using the probability tree for Question 3, what is the combined probability of a new product appearing to be successful in a market research study as well as in a test market, and yet still failing in a national marketing campaign? Express probability as a decimal with two decimal places. Example 50% = .50

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: The combined probability is 0.4 * 0.6 * 0.3 = 0.072, which is expressed as 7.2% when rounded to two decimal places.

Q5. The spreadsheet below shows the average outside temperature and retail food sales by a street vendor. What’s the relationship between temperature and sales?

• The relationship cannot be determined with this data
• The correlation is moderate
• The correlation is strong
• The correlation is weak

Q6. A new baby thermometer uses an innovative design in which a monitor patch measures a baby’s temperature each second and transfers that reading with a timecode to a smartphone application. This is an example of the use of

• Cannot be determined from this information
• Constant time
• Continuous time
• Discrete-time

Q7. Below is the spreadsheet model we looked at in this module for measuring the exponential growth of an epidemic. Predict the number of users of a new social network at a future date assuming similar exponential growth. How many users of the new service would we expect to have in seven and a half months?

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: To predict the number of users of a new social network in seven and a half months based on exponential growth, you would need the growth rate or percentage increase per unit of time. Without that information, it’s not possible to make an accurate prediction.

Q8. A bank savings account that pays an interest rate based on the balance at the end of each month is an example of

• Proportionate or geometric growth
• Constant growth
• Arithmetic growth
• Exponential growth or decay
• None of these

Q9. Write the formula in cell C13 that shows an exponential forecast for next year based on the past 10 years’ data.

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: =B2 * (1 + Growth Rate)^Number of Years

Q10. Below is a regression analysis report that compared the clarity rating of diamonds for sale on the Singapore diamond exchange to the price each diamond brought. What percent of changes in price is due to the changes in quality? Express your answer as a decimal with two decimal places, for example, 50% = .50

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer:To determine what percent of changes in price is due to changes in quality, you would typically need to look at the coefficient of determination (R-squared) from the regression analysis. The R-squared value represents the proportion of the variance in the dependent variable (price) that can be explained by changes in the independent variable (quality). This value would provide the answer, expressed as a decimal between 0 and 1.

Introduction to Spreadsheets and Models Week 4 Quiz Answers

Quiz 1: Module 4 Quiz: Simulations, Scenarios, and Optimization

Q1. A sales division in a large IT consulting company prepares proposals and bids on engagements with companies who are considering purchasing new information systems. There is some randomness at work in this process, with varying numbers of competitors in each case and other factors affecting the customers’ decisions. Generally, they win 20% of the contracts that they bid on. If you were to build a model of the division’s activity and wanted to include a random variable for winning contracts, what type of distribution would you use?

• Discrete
• Bernoulli
• Normal
• All of these
• Uniform

Q2. Please check all that apply.

Monte Carlo simulations are useful to include in models when:

• Stakes are high
• Manual what-if testing isn’t feasible
• A small amount of extra precision is valuable
• There are many variables with complex interactions

Q3. There are four main steps in building a Monte Carlo simulation: select probability distribution(s); run the simulation model through a large number of trials; analyze results of multiple trials to assess risks and opportunities; and generate ______ variables

spreadsheet showing sales of Product A, current inventory of Product A and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: Random

Q4. In the simulation model shown below, what is the primary purpose of the standard deviation formula in cell H11?

• To select a probability distribution for generating random variables
• To add a constraint needed for an optimization function
• None of these are the primary purpose
• To help assess risk

Q5. In a linear programming model, all of the following are examples of variables Solver changes to generate an optimal solution, except:

• Production levels of alternative products
• Profit margin targets
• Allocation of scarce resources
• How much to purchase of various components from suppliers
• Composition of a product

Q6. Which of the following can be set as the objective of a linear programming optimization model using Excel’s Solver function (check all that apply)?

• Max
• Average
• A specific target value
• Random variables
• Min

Q7. In the Innovative Speakers case, Solver was used to generate an optimal outcome by trying every possible value in a range of cells in the model.

That range in the model was:

• All of these
• F8:H10
• C4:E4
• C13:E13
• H4

Q8. In the model shown below, decisions about investments in research and development across three possible product development projects are constrained by the overall R&D budget.

In the Solver Parameters form shown, what is the expression required to implement the staffing constraint?

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer: =C13*H4 + D13*I4 + E13*J4 <= F8

Q9. Please check all that apply:

Which of the following business scenarios are examples of problems that can be addressed by linear program models?

• Setting truck routes for small deliveries
• Determining the mix of economy or coach, business class, and first-class seats on an airplane
• Planning staffing levels for a call center given uncertain customer demand
• Allocating a fixed R&D budget across multiple development projects

Q10. Assume that in the Innovative Speaker simulation, the standard deviation of 1000 trials was 8404 and the mean was 16,808, as shown below.

What are the chances that the actual cash flow could still be negative? Express your answer as a decimal with 3 decimal places. For example, 50% should be written as .500

spreadsheet showing sales of Product A, current inventory of Product A, and purchases of Product A from a supplier when inventories run low entering a formula in a spreadsheet cell, write a statement in cell D5 that would trigger a 100-unit order from a supplier

Answer:

Z = (0 – Mean) / Standard Deviation

Then, you can use the Z-table or a calculator to find the probability that Z is less than the calculated value. In this case:

Z = (0 – 16,808) / 8,404 = -2

Using a Z-table or calculator, you can find that the probability of Z being less than -2 is approximately 0.0228 (or 2.28%). So, the chances of the actual cash flow being negative are approximately 2.28%.

Get All Course Quiz Answers of Finance & Quantitative Modeling for Analysts Specialization

Fundamentals of Quantitative Modeling Coursera Quiz Answers

Introduction to Spreadsheets and Models Coursera Quiz Answers

Financial Acumen for Non-Financial Managers Coursera Quiz Answers

Introduction to Corporate Finance Coursera Quiz Answers