Learning Center
Plans & pricing Sign in
Sign Out

Ch10 Modules


  • pg 1
									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
    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
    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

    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.
     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
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.

To top