Everything must be done in Excel.
Data Tables 1. Currently, you sell 100,000 units of a product for $50 each. The unit variable cost of producing the product is $10. You are thinking of cutting the product price by 25 percent. You are sure this will increase sales by an amount from 10 percent to 50 percent. Perform a sensitivity analysis (in 10% increments) to show how profit will change as a function of the percentage increase in sales. Ignore fixed costs. Explain what the sensitivity analysis shows. 2. Suppose you know the annual interest rate will be 5 percent and you are going to put down 20% of the overall loan value. Create a table that shows for purchase prices from $300,000 through $500,000 (in $25,000 increments), the difference in monthly payments between a 15-year, 20-year, and 30-year mortgage. Pivot Tables - For these questions please use the Makeup Sales Worksheet from the Data Sources Excel file when needed. 3. Create a PivotTable that shows the total dollars sold by each salesperson in each year in descending order based on their total sales in the three year window. 4. Create a PivotChart of the total units sold of each in the three year window. Include slicers for the months and only select months that fall in the first half of the year (January - June). Advanced Excel Formulas - For these questions please use the Distances Worksheet from the Data Sources Excel file when needed. 5. Suppose you begin in Atlanta, visit the cities in the order listed (Boston, Charlotte, etc.), and then return to Atlanta. How far would you travel? 6. Suppose the price of a product will change at dates in the future, as follows: Date Price On or before February 15, 2019 $9 From February 16, 2019 through April 10, 2019 $10 From April 11, 2019 through January 15, 2020 $11 After January 15, 2020 $12 Write a formula that computes the price of the product based on the date the product is sold. Data Analysis - For these questions please use the Quarterly Revenues Worksheet from the Data Sources Excel file when needed. 7. Create a correlation table between the three firms' quarterly revenues. Be sure there are no blanks in the table. 8. Construct three separate four-quarter moving average trend curves for General Motors, Ford, and Coca-Cola. Describe what you learn from each trend curve. Goal Seek and Solver - For these questions please use the Computers Worksheet from the Data Sources Excel file when needed. Be sure to label your inputs so I know what you did. 9. I am managing a conference at my college. My fixed costs are $10,000. I must pay the college union $300 per conference participant food lodging costs. Fifty people have registered to end. How much must charge each registrant for me to break even? 10. A computer-manufacturing plant produces mice, keyboards, and videogame joysticks. The per-unit profit, per- unit labor usage, monthly demand, and per-unit machine-time usage are given. Each month, a total of 13,000 labor hours and 3,000 hours of machine time are available. How can the manufacturer maximize its monthly profit? 11. Extra Credit: What do you miss most about in-person classes? What do you like most about virtual classes? Data Tables 1. Currently, you sell 100,000 units of a product for $50 each. The unit variable cost of producing the product is $10. You are thinking of cutting the product price by 25 percent. You are sure this will increase sales by an amount from 10 percent to 50 percent. Perform a sensitivity analysis (in 10% increments) to show how profit will change as a function of the percentage increase in sales. Ignore fixed costs. Explain what the sensitivity analysis shows. 2. Suppose you know the annual interest rate will be 5 percent and you are going to put down 20% of the overall loan value. Create a table that shows for purchase prices from $300,000 through $500,000 (in $25,000 increments), the difference in monthly payments between a 15-year, 20-year, and 30-year mortgage. Pivot Tables - For these questions please use the Makeup Sales Worksheet from the Data Sources Excel file when needed. 3. Create a PivotTable that shows the total dollars sold by each salesperson in each year in descending order based on their total sales in the three year window. 4. Create a PivotChart of the total units sold of each in the three year window. Include slicers for the months and only select months that fall in the first half of the year (January - June). Advanced Excel Formulas - For these questions please use the Distances Worksheet from the Data Sources Excel file when needed. 5. Suppose you begin in Atlanta, visit the cities in the order listed (Boston, Charlotte, etc.), and then return to Atlanta. How far would you travel? 6. Suppose the price of a product will change at dates in the future, as follows: Date Price On or before February 15, 2019 $9 From February 16, 2019 through April 10, 2019 $10 From April 11, 2019 through January 15, 2020 $11 After January 15, 2020 $12 Write a formula that computes the price of the product based on the date the product is sold. Data Analysis - For these questions please use the Quarterly Revenues Worksheet from the Data Sources Excel file when needed. 7. Create a correlation table between the three firms' quarterly revenues. Be sure there are no blanks in the table. 8. Construct three separate four-quarter moving average trend curves for General Motors, Ford, and Coca-Cola. Describe what you learn from each trend curve. Goal Seek and Solver - For these questions please use the Computers Worksheet from the Data Sources Excel file when needed. Be sure to label your inputs so I know what you did. 9. I am managing a conference at my college. My fixed costs are $10,000. I must pay the college union $300 per conference participant food lodging costs. Fifty people have registered to end. How much must charge each registrant for me to break even? 10. A computer-manufacturing plant produces mice, keyboards, and videogame joysticks. The per-unit profit, per- unit labor usage, monthly demand, and per-unit machine-time usage are given. Each month, a total of 13,000 labor hours and 3,000 hours of machine time are available. How can the manufacturer maximize its monthly profit? 11. Extra Credit: What do you miss most about in-person classes? What do you like most about virtual classes
