Assignment 1
Problem 1
Like so many other aspects of the business world, the field of data analysis and statistics has been impacted by the Big Data phenomenon and the digital revolution. Using a few paragraphs, describe how the digital age has affected your life, education and possibly job, especially with respect to data analysis and statistics.
Problem 2
Worksheet “Credit Cards” in “Assignment 1.xlsx” contains demographic information as well as the total annual charges for 5,000 credit card holders. I planted 5 fairly obvious “mistakes” (e.g., outliers, blanks, “impossible” values) in the data. Can you find them? Please report the account number and what you think the mistake is for each.
Problem 3
Eastman Publishing Company is considering publishing an electronic textbook about spreadsheet applications for business. The fixed cost of manuscript preparation, textbook design, and web site construction is estimated to be $160,000. Variable processing cost are estimated to be $6 per book. The publisher plans to sell single-user licenses to the book for $46.
- Build a spreadsheet model to calculate the profit/loss for a given demand using the principles of good spreadsheet design (e.g., clearly label and differentiate inputs and outputs, make good use of color and shading, etc.). What profit or loss can the company expect with a demand of 3,500 licenses? Include a screenshot/copy of your spreadsheet in your answer report.
With a demand of 3,500, there will be a loss of $20,000.
- Use a 2-way data table to vary demand (i.e., number of licenses) from 1,000 to 6,000 (in increments of 500) and price for a license between $45 and $50 (in increments of $1) to assess the sensitivity of profit to demand and price. You can consult the section on Data Tables in Chapter 6 of the Excel Boot Camp Manual for more information. Include a screenshot/copy of your data table in your answer report.
- For the data table in part b), we implicitly make a crucial assumption about price and demand. Can you explain this assumption and does it make sense?
For demand of 3,500, the breakeven access price is $51.71.
- Use the GoalSeek function to determine the price per license that the publisher must charge to break even with a demand of 3,500 copies. What is this break-even point? Again, you can consult the section on the GoalSeek function in Chapter 6 of the Excel Boot Camp Manual for more information.
Scenario 4 yields the highest profit ($40,000) and scenario 2 the lowest (-$118,000).
