Solved by verified expert:i attached all the needed files below. The Excel file is where to do the work. Please use my file to do the work. Thanks..
ex16xlch06gradercapas___valentine_budget_13_instructions.docx
duong_exploring_e06_grader_capstone2.xlsx
Unformatted Attachment Preview
Office 2016 – myitlab:grader – Instructions
Excel Project
EX16_XL_CH06_GRADER_CAP_AS – Valentine Budget 1.3
Project Description:
In the following project, you will perform What-If Analysis to calculate budget information for your university’s
Valentine’s Day formal.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
Download and open the file named exploring_e06_grader_Capstone2.xlsx.
0.000
2
Use Goal Seek to achieve a $0 balance by changing the Ticket Price per Person.
6.000
3
Beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at
increments of 20 students vertically down column E.
3.000
4
Enter cell references to the Total Income, Total Expenses, and Balance formulas (in that
order) for a one-variable data table in cells F2, G2, and H2 respectively.
3.000
5
Apply custom number formats to make the formula references appear as the following: F2Revenue, G2 – Expenses, H2 – Balance.
3.000
6
Create a one-variable data table for the range E2:H18 using the Number of Attendees as the
column input cell. Format the results with Accounting Number Format with two decimal places.
7.000
7
Copy the Number of Attendees substitution values from the one-variable data table (in cells
E3:E18), and then paste the values starting in cell E22.
4.000
8
Type $50 in cell F21 and complete the series of substitution values from $50 to $100 at $10
increments.
2.000
9
Enter the cell reference to the Balance formula (C34) in the cell E21 for a two-variable data
table, then complete the table using cell B9 as the row input cell and B4 as the column input
cell. Format the results with Accounting Number Format with two decimal places.
7.000
10
Apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without
creating a deficit.
2.000
Updated: 10/23/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
11
Apply custom number format to cell E21 to display # Attend.
2.000
12
Create a scenario named 500 Attend using the Number of Attendees, Caterer’s Meal Cost
per Person, Ticket Price per Person, and Ballroom Rental variables as the changing cells.
Deselect Prevent changes. Enter these values for the scenario: 500, 15.95, 75, and 12500.
5.000
13
Create a second scenario named 400 Attend, using the same changing cells. Deselect
Prevent changes. Enter these values for the scenario: 400, 17.95, 85, and 12500.
5.000
14
Create a third scenario named 300 Attend, using the same changing cells. Deselect Prevent
changes. Enter these values for the scenario: 300, 19.95, 90, and 11995, respectively.
5.000
15
Create a fourth scenario named 200 Attend, using the same changing cells. Deselect Prevent
changes. Enter these values for the scenario: 200, 22.95, 95, and 11995, respectively.
5.000
16
Generate a scenario summary report using the Total Income, Total Expenses, and Balance as
the results. Mac users should click OK in any error messages that are displayed until the
summary report is generated.
6.000
17
Load the Solver add-in if it is not already loaded. Click the Budget worksheet and launch
Solver. Set the objective to calculate the highest balance possible.
6.000
18
Use the Number of Attendees and the Ticket Price per Person as changing variable cells.
6.000
19
Set a constraint so that the Number of Attendees entered in the Input Section of the
workbook does not exceed the specified limitation in cell B12.
6.000
20
Set constraints so that the Ticket Price per Person entered in the Input Section meets the
requirements set in the range A14:B15.
6.000
21
Set an appropriate integer constraint.
6.000
22
Set a constraint that ensures the Valet Parking expense is less than or equal to the product of
the Maximum Parking Stalls and the Valet Parking per Car (in that order). Note, Mac users
should NOT perform this step.
0.000
23
Solve the problem, but keep the original values in the Budget worksheet. Generate the Answer
Report.
5.000
Updated: 10/23/2017
2
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
24
Excel Project
Points
Possible
Instructions
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: Scenario Summary, Answer Report 1, Budget. Save the workbook. Close the
workbook and then exit Excel. Submit the workbook as directed.
Total Points
Updated: 10/23/2017
3
0.000
100.000
Current_Instruction.docx
Valentine’s Day Ball
Input Section
No. of Attendees
% Attendees Using Valet
Cost per Chair Setup
Valet Parking per Car
Caterer’s Meal Cost per Person
Ticket Price per Person
Limitations
Maximum Attendees
Maximum Parking Stalls
Minimum Ticket Price
Maximum Ticket Price
Income
Student Club Contributions
Ticket Revenue
$
$
$
$
$
$
$
400
50%
2.00
19.95
20.95
75.00
500
240
50.00
100.00
8,500
30,000
Total Income
Expenses
Advertising
Ballroom Rental
Chairs/Table Setup
Valet Parking
Decorations
DJ Cost
Cleanup Costs
Meal Cost
Contingency
Total Expenses
Balance
$
$
38,500
$
44,090
$
(5,590)
3,345
12,500
800
3,990
4,575
3,000
2,500
8,380
5,000
One-Variable Data Table: Attendees
Two-Variable Data Table: Attendees and Price Per Ticket
…
Purchase answer to see full
attachment
