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