Click
Link Below To Buy:
Contact Us:
Hwcoursehelp@gmail.com
Shelly Cashman Excel 2016| Module 9: Project 1b
Rio Grande Music Camp
FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM
SOLVING
GETTING STARTED
Open the file SC_EX16_9b_FirstLastName_1.xlsx, available for
download from the SAM website.
Save the file as SC_EX16_9b_FirstLastName_2.xlsx by changing
the “1” to a “2”.
If you do not see the .xlsx file extension in the Save As
dialog box, do not type it. The program will add the file extension for you
automatically.
With the file SC_EX16_9b_FirstLastName_2.xlsx still open,
ensure that your first and last name is displayed in cell B6 of the Documentation
sheet.
If cell B6 does not display your name, delete the file and
download a new copy from the SAM website.
This project requires you to use the Solver add-in. If this
add-in is not available on the Data tab in the Analysis group (or if the Analysis
group is not available), install Solver as follows:
In Excel, click the File tab on the ribbon, and then click
the Options tab in the left pane of Backstage view.
Click the Add-Ins tab in the left pane of the Excel Options
dialog box.
Click the Manage arrow, click Excel Add-Ins in the Manage
list, and then click the Go button.
In the Add-Ins dialog box, click the Solver Add-In check box,
and then click the OK button.
Follow any remaining prompts to install Solver.
PROJECT STEPS
Alison Silverstein is the director of the Rio Grande Music
Campin the outskirts of Boulder, Colorado. As an office assistant at the camp,
you help Alison gather and analyze financial data. She asks for your help
completing an Excel workbook that contains errors and missing information. She
also wants to make it easier to enter correct data and determine the financial
effects of changing the rates and schedules for music lessons.
Go to the Music Programs worksheet. Correct the first error
in this worksheet, a divide by zero error, as follows:
Use the Trace Precedents arrows to find the source of the
error in cell B14, the income per Novicelesson.
Correct the error by editing the formula in cell B14, which
should divide the income per program (cell B13) by the lessons per program
(cell B4).
Copy the formula and the formatting from cell B14 to the
range C14:F14.
Correct the Name error in cell B17 as follows:
Use Error Checking to determine the source of the error in
cell B17, which should calculate the average income per program.
Correct the error by editing the formula in cell B17.
Go to the Program Enrollment worksheet. To make it easier to
add the correct program enrollment information, add data validation tothe range
B7:F9as follows:
The cells in the range B7:F9should allow only whole number
values greater than 0.
Add an Input Message usingProgram Enrollment as the Input
Message title and Enter the minimum, average, or maximum enrollment per lesson
type. (including the period) as the Input message.
Add an Error Alert using the Stop style for the Error Alert,
and useEnrollment Error as the Error Alert title and Program enrollment must be
greater than 0. (including the period) as the Error message.
Alisonthinks the camp can make as much as $20,000 in program
income from the Level II lessons. Use Goal Seek to determine how to achieve
this goal as follows:
Set the maximum program income for Level II lessons (cell D14)
to 20,000.
Change the program fee for Level II lessons (cell D3) to
determine the fee the camp needs to charge to achieve the income goal.
Alison also wants to make at least $8,000in income per programfrom
the Level I lessons. Use Goal Seek to determine how to achieve this goal as
follows:
Set the minimum program income for Level I lessons (cell C12)
to 8000.
Change the minimum program enrollment for Level I lessons
(cell C7) to determine the minimum number of Level I students the camp needs to
achieve the income goal.
Alison wants to average $15,000 in income per programfrom
the Novice lessons. Use Goal Seek to determine how to achieve this goal as
follows:
Set the average program income for Novice lessons (cell B13)
to 15,000.
Change the average program enrollment for Novice lessons
(cell B8) to determine the average number of Novice students the camp needs to
achieve the income goal.
Go to the Current Rates worksheet. Alison has already
created a scenario named Max Enrollment that calculates profit based on the
maximum number of students enrolled for each program. Add a new scenario to
compare the profit with average enrollment as follows:
Use Average Enrollment as the scenario name.
Use B8:F8 as the changing cells.
Accept the current values in the range B8:F8 as the values
for the changing cells because these cells show the average number of students
per program.
Add another new scenario to compare the profit with low program
enrollment as follows:
Add another scenario to the workbook, using Low Enrollment
as the scenario name.
Use B8:F8 as the changing cells.
Update the cell values in the range B8:F8 to match the low enrollmentvalues
shown in bold in Table 1 below:
Table 1: Cell Values for the Low Attendance Scenario
|
Cell
|
New Value
|
|
Novice_New_Students (B8)
|
16
|
|
Level_I_New_Students (C8)
|
20
|
|
Level_II_New_Students (D8)
|
18
|
|
Level_III_New_Students (E8)
|
18
|
|
Advanced_New_Students (F8)
|
20
|
Show the Max Enrollment scenario values in the Current Rates
worksheet.
Go to the New Rates worksheet, which contains three
scenarios showing the profit with a $20 or $25rate increase or a $10 rate
decrease. Compare the average profit per program based on the scenarios as
follows:
Create a Scenario Summary report using the range B10:F10 as
the result cells to show the average profit per program depending on the rate
changes. (Hint: The defined names of the range B10:F10 appear in the report.)
Use New Rates Scenario Report as the name of the worksheet
containing the report.
Alison also wants to focus on one or two types of lessons at
a time when comparing the average profit per program. Return to the New Rates
worksheet and create another type of report as follows:
Create a Scenario PivotTable report using the range B10:F10
as the result cells to compare the average profit per program depending on the
rate changes in a PivotTable. (Hint: The defined names of the range B10:F10
appear in the report.)
Use New Rates PivotTable as the name of the worksheet
containing the PivotTable.
Format cells B4:F6in the New Rates PivotTable worksheet using
the Accounting number format with 0 decimal places and $ as the symbol. (Hint:
Depending on how you complete this substep, the number format may appears as
Custom.)
Go to the New Schedule worksheet. Alison wants to determine
the number of morning and afternoon lessons the camp can offer to make the highest
weekly profit without exceeding the maximum room capacities, meeting the ensembles
scheduling needs, and considering other practical conditions.Use Solver to find
this informationas follows:
Use cell G16 (Total_Weekly_Profit) as the objective cell in the
Solver model, with the goal of determining the maximum value for that cell.
Use the range B4:F5, which shows the number of morning and
afternoon musiclessons, as the changing variable cells.
Use the constraints shown in Table 2 below.
Table 2: Solver Constraints
|
Constraint
|
Cell or Range
|
|
Each type of lessonis
scheduled at least once in the morning and once in the afternoon
|
B4:F5
|
|
Each morning and afternoon lesson
value is an integer
|
B4:F5
|
|
Each type of lesson is
scheduled 2 times per week or more
|
B6:F6
|
|
Each type of lesson is
scheduled 15 times per week or less
|
B6:F6
|
|
The total number of morning lessons
is 10 or less
|
Total_Morning_Lessons
|
|
The total number of afternoon
lessons is 15 or less
|
Total_Afternoon_Lessons
|
|
The total number of lessons
per week is 20
|
Total_Weekly_Lessons
|
|
Ensemble morning lessons are
scheduled 5 times per week or less
|
Group_Morning_Lessons
|
|
Ensemble afternoon lessons
are scheduled 8 times per week or less
|
Group_Afternoon_Lessons
|
|
The total number of ensemblelessons
scheduled per week is 15 or less
|
Group_Total_Lessons
|
Use Simplex LP as the solving method.
Save the Solver model in cell A26.
Solve the model, keeping the Solver solution.
Alison wants to document the answer Solver found, including
the constraints and a list of the values Solver changed to solve the problem.
Produce an Answer report for the Solver model as follows:
Solve the model again, this time choosing to produce an
Answer report.
Use New Schedule Answer Report as the name of the worksheet containing
the Answer report.
Mark the workbook as Final to indicate you are finished
revising the workbook.
Your workbook should look like the Final Figureson the
following pages. (The values in cells D3, C7, and B8 of the Program Enrollment
worksheet generated by Goal Seek Analysis has intentionally been blurred out in
Final Figure 2.) The New Schedule Answer Report and New Schedule worksheets are
not provided to prevent manual entry of the Solver results. Save your changes,
close the workbook, and then exit Excel. Follow the directions on the SAM
website to submit your completed project.
Final Figure 1: MusicProgramsWorksheet
Final Figure 2: Program Enrollment Worksheet
Final Figure 3: Current RatesWorksheet
Final Figure 4: New Rates Scenario ReportWorksheet
Final Figure 5: New Rates PivotTable Worksheet
Final Figure 6: New RatesWorksheet
No comments:
Post a Comment