VIEWS: 824 PAGES: 4 POSTED ON: 2/12/2012
Review Assignment Data File needed for the Review Assignment: Light.xlsx Creative Ventures has another product that is has been selling called a Light Styk. Todd and Brent have used last year’s sales data to project next year’s income statement. They want you to analyze the cost- volume-profit relationship of Light Styks product. They also want you to determine the price for the Light Styks that maximizes the conmpany’s net income. Complete the following: 1. In the Income Statement worksheet, create a one-variable data table to calculate the revenue, expenses and net income for sales volume ranging from 0 units sold up to 30,000 units sold in increments of 5000 units. Format the table so that it is easy to read. 2. Use the data table you created in Step 1 to creat a scatter chart with straight lines pisplaying the revenue and total expenses plotted against units sold. Store the chart in a chart sheet named CVP Chart. Based on the chart, project the break-even point for the company’s sale of Light Styks. Add appropriate titles to the chart and chart axes. 3. Create a two-variable data table that calculates net income based on different units sold and the sales price values. Assume that the units sold values range from 0 to 30000 units in increments of 5000 units, and that the possible sales price values are $10, $12, $14 and $16. Format the table so that it is easy to read and interpret. 4. Plot the results of the two-variable data table in a scatter chart with straight lines. Save the chart in a chart sheet named Net Income Chart. Edit the chart series so that the series names ($10, $12, $14 and $16) are displayed in the legend for each chart. Add appropriate titles to the chart and chart axis. 5. Create range names for the values in the nonadjacent range B7:B8;B12:B16;B20:B22;B26:B28 based on the corresponding values in column A. 6. Todd and Brent want to study the four scenarios shown below. Add these scenarios to the Light Styks workbook: Input Cells Status Quo Expanded Reduced Sale Units Sold (B7) 14,000 12,000 16,000 18,000 Price per Unit (B8) $11 $15 $10 $8 Advertising (B20) $10,000 $30,000 $10,000 $10,000 Administrative (B21) $10,000 $25,000 $5,000 $5,000 Miscellaneous (B22) $5,000 $20,000 $5,000 $5,000 7. Create a scenario summary report on the four scenarios you created, reporting their effect on total revenue, total expenses and net income. 8. Create a Scenario PivotTable based on the four scenarios. Format the Values in the PivotTable as currency. 9. Create a Scenario PivotChart based on the four scenarios. Place the PivotChart in a chart sheet named Scenario PivotChart. Add an appropriate chart title and axes titles. 10. Todd and Brent want to calculate the optimal price for their Light Styks product, assuming the price of elasticity of demand value of 1.4. With the Status Quo scenario displayed in the worksheet, copy the values in the range B6:B28 into the range C6:C28. Change the label in cells C6, C11, C19 and C25 to Optimal Price. 11. In cell C7, project the units sold based on the sales price by entering the formula =B7(1+B4*(1- C8/B8)). Verify that with the price of $12.97 in cell C8, the units sold value in C7 is 10,486. 12. Use Solver to calculate the sales price that results in the maximum net income subject to the following constraints: units produced (C12) must be less than or equal to 30,000 and greater than or equal to 5,000. 13. Save the solver answer report to a worksheet named Net Income Answer Report. 14. In cell A30 of the Income Statement worksheet, enter Maximum Net Income and then format the cell using the 20% - Accent3 cell style. Save the parameters of the Solver model to the range A31:A36. 15. Save and close the workbook Case Problem 1 Data File needed for this Case Problem: Grade.xlsx Professor Karen Reynolds teaches calculus at High Desert University in Tempe, AZ. The class has 220 students who are distributed among dozens of sections and discussion groups. Professor Reynolds wants to use Excel to determine the appropriate cutoff points for her grading curve. Generally, she wants to set the cutoff points so that the following distribution of grades is observed in the student body: F 5% D 10% C 35% B 35% A 15% Professor Reynolds has five possible grading curves. For example, in Grading Curve 1, she will assign A’s to test scores from 80 to 100. She wants you to evaluate each one and determine which one results in a distribution of grades closest to her proposed distribution. After you choose which of the 5 scenarios firs the data best, she wants you to use Solver to determine whether there is a grading curve that is even closer than any of her proposed scenarios to the desired distribution of grades. Complete the following: 1. Open the Grade.xlxs and then save workbook as Grade Curve.xlxs. 2. The Test Score worksheet contains a table of individual student scores and a table for the grading curve. In the Test Score worksheet, the range F4:G8 will contain the lower and upper ranges for each letter grade. Add the missing upper range values in the range G4:G7 by inserting formulas in those cells so that the upper range in the range G4:G7 by inserting formulas in those cells so that the upper range for each letter grade is one point lower than the lower range of the next letter grade. 3. In cell D4, enter the VLOOKUP function to return the letter grade for the first student in the list. (HINT: THE LOOKUP VALUE IS THE STUDENTS FINAL SCORE, THE TABLE ARRAY IS THE CELL RANGE $F$4:$h$8, THE COLUMN INDEX NUMBER IS 3, AND THE LOOKUP SHOULD FIND THE CLOSEST MATCH IN THE FIRST COLUMN OF THE LOOKUP TABLE.) Copy the formula in the cell D4 into the range D5:D223 to calculate the grades for the rest of the students’ scores. 4. In the cell I4, use the COUNTIF function to count the total number of letter grades in the range $D$4:$D$223 equal to “F”. Copy your formula into the range I5:I8 to count the total number of the other letter grades assigned under the current grading scale. In cell I9, calculate the total number of all letter grades, verifying that the total equals 220. 5. In the range J4:J8, calculate the percent of each letter grade assigned to the student body. In cell J9, calculate the total percentage of all letter grades, verifying that the total percentage is 100 percent. 6. In the range L4:L8, use the ABS function to calculate the absolute value of the difference between the observed percentage of each letter grade and Professor Reynolds’ optimal percentage. In L9, calculate the total value of these absolute differences. 7. Assign the range names LowF through LowA for the values in range F4:F8. Assign the range names HighF through HighA for the values in the range G4:G8. Assign the range names PercentF through PercentA for the values in the range J4:J8. Assign the range name DifferenceFromCurve to the value in cell L9. 8. Enter the five grading curve scenarios shown below into scenarios named Grading Curve 1 through Grading Curve 5. Use the range F4:F8 as your changing cells. Create a scenario summary report evaluating the results from each of the five scenarios, displaying the values from the range J4:J8;L9 as your result cells. Scenario F D C B A Grading Curve 1 0-19 20-39 40-59 60-79 80-100 Grading Curve 2 0-29 30-49 50-69 70-89 90-100 Grading Curve 3 0-49 50-64 65-79 80-94 95-100 Grading Curve 4 0-39 40-59 60-74 75-84 85-100 Grading Curve 5 0-59 60-69 70-79 80-89 90-100 9. The closeness of each grading curve to Professor Reynolds’ optimal grading curve is expressed in the value of cell L9. If there was a perfect correspondence, the value of cell L9 would be 0. Pick the grading curve that has the lowest value for cell L9 and show that grading curve in the Test Score worksheet. 10. Using the scenario values you selected in the last step as a starting point, create a Solver model to minimize the value in cell L9 by changing the values in the range F5:F8, subject to the constraint that all the values in range F5:F8 must be integers. 11. Store the grading curve returned by Solver as a new scenario named Optimal Grading Curve. Create a second scenario summary report displaying this grading curve along with the five others you’ve investigated. 12. Save and close the workbook. Case Problem 2 Date File needed for this case problem: Loan.xlxs Kevin Webber is considering taking out a second mortgage for an addition on his home. He decides to use Excel to analyze several possibilities for the loan including the size of the loan, the interest rate, and the number of years required to pay back the loan. He wants your help in developing a two-variable data table and scenarios to examine how varying these factors affects the required monthly payment and the total cost of the loan. Complete the following 1. Open the Loan.xlxs workbook and save as Loan Table.xlxs. 2. In the Mortgage worksheet, define names for the values in the range B3:B8 based on the name values in the left column. 3. Enter Monthly Payment in cell D1. Enter Years in cell E2, and then merge and center the range E2:I2. Enter the values 10 through 30 in increments of 5 in the range E3:I3. Enter the values 4.5% through 6.0% in increments of 0.1% in the range D4:D19. 4. In cell D3, enter a reference to the value in cell B7. Format the cell to display the test string Interest Rate. 5. Create a two-variable data table in the range D3:I19 using cells B5 and B4 as the column and row input cells, respectively. 6. Format the resulting two-variable data table so that the result values appear as currency, and the row and column labels appear on a light yellow background. Add gridlines to the table. 7. Add scnearios to the worksheet to display all combinations of the loan assuming that the number of years required for payment is 30; the interest rate is 5.5%, 6% or 6.5%; and the amount of the loan is $200,000, $250,000 or $300,000. (HINT: THERE WILL BE NINE SCENARIOS) 8. Create a scenario summary report that displays the value of the monthly payment and total cost of the loan for each of the scenarios you created. 9. Format the scenario summary report that displays the value of the monthly payment and the total cost of the loan for each of the scenarios you created. 10. Save and close the workbook.