# Excel Project Excel–CH6-CH9-Ch12: Project Flashcards

Set Details Share
created 8 years ago by Gimineaytron
3,335 views
Excel Project Excel–CH6-CH9-Ch12: Project
Subjects:
excel project excel–ch6-ch9-ch12: project
Page to share:
Embed this setcancel
COPY
code changes based on your size selection
Size:
X

1

Excel Project Excel–CH6-CH9-Ch12: Project

2

Excel Project Excel–CH6-CH9-Ch12: Project

Excel–CH6-CH9-Ch12: Project

*Hi Aown,
Would you be able to help me redo this assignment from ground up? I did it before but it was full of errors. Thanks.
Offered Price: \$100
Due Date: 19/04/2016

Please confirm ASAP #O.K, will turn the ans. by Monday 18 Apr, 2016.

Project Description:

We have combined three chapters in Excel for this project: Excel - CH06: What-If Analysis, Excel - CH09: Multiple-Sheet Workbook Management, and Excel – CH12: Templates, Styles and Macros. Steps 1 through 19 include features from Excel Chapter 6, with Step 1 providing a description of the worksheets related to Chapter 6. Steps 20 through 30 include features from Excel Chapter 9, with Step 20 providing a description of the worksheets related to Chapter 9. Steps 31 to 40 include features from Excel Chapter 12, with Step 31 providing a description of the worksheets related to Chapter 12.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

CH06 - Project Description:

In the following project, you will perform What-IF Analysis to calculate budget information for your University s Valentine s Day formal.

0.000

2

Save the file as: Excel-6-9-12_LastName (replacing your last name with the text LastName).

0.000

3

On the Budget worksheet, use Goal Seek to achieve a \$0 balance by changing the Ticket Price per Person.

3.000

4

Beginning in cell E3, complete a series of substitution values vertically in column E, ranging from 200 to 500 at increments of20 attendees.

3.000

5

Enter cell references to the Total Revenue, Total Expenses, and Balance formulas (in that order) for a one-variable data table in cells F2, G2, and H2, respectively.

Apply custom number formats to make the formula references display as follows:
F2 – text to display: Revenue
G2 – text to display: Expenses
H2 – text to display: Balance

3.000

6

Create a one-variable data table for the range E2:H18 using the Number of Attendees as the appropriate input cell.

Format the results with Accounting Number Format with two decimal places.

Type #Attend in cell E2 and align right the cell contents.

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

Type \$50 in cell F21 and complete the series of substitution values for Ticket Price per Person from \$50 to \$100 at \$10increments.

3.000

8

In cell E21, enter the cell reference to the Balance formula (C33).

Complete the two-variable data table for the range E21:K37, using Number of Attendees and Ticket Price per Person as the appropriate input cells.

Format the results with Accounting Number Format with two decimal places.

5.000

9

Select the 3 cells in the two-variable data table that are closest to break even without creating a deficit.

Apply a Light Blue fill color and Dark Red text to the 3 selected cells.

Apply custom number format to cell E21 to display #Attend.

3.000

10

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.

Enter these values for the scenario: 500, 15.95, 75, and 12500.

2.000

11

Create a second scenario named 400 Attend, using the same changing cells.

Enter these values for the scenario: 400, 17.95, 85, and 12500.

2.000

12

Create a third scenario named 300 Attend, using the same changing cells.

Enter these values for the scenario: 300, 19.95, 90, and 11995, respectively.

2.000

13

Create a fourth scenario named 200 Attend, using the same changing cells.

Enter these values for the scenario: 200, 22.95, 95, and 11995, respectively.

2.000

14

Generate a scenario summary report using the Total Revenue, Total Expenses, and Balance as the result cells.

On the Scenario Summary worksheet, replace existing text as follows:
C6 – No. of Attendees
C7 – Caterer’s Meal Cost per Person
C8 – Ticket Price per Person
C9 – Ballroom Rental
C11 – Total Revenue
C12 – Total Expenses
C13 – Balance

AutoFit the contents of column C.

Delete column A.

5.000

15

Click the Budget worksheet, activate Solver, and set the objective to calculate the highest balance possible.

Use the Number of Attendees and the Ticket Price per Person as changing variable cells.

3.000

16

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

Set an appropriate integer constraint.

3.000

17

Set constraints so that the Ticket Price per Person entered in the Input Section meets the requirements set in the range A13:B14.

3.000

18

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

3.000

19

Solve the problem, but restore original values in the Budget worksheet.

3.000

20

CH09 - Project Description:

You have volunteered to be the statistician for your high school s football team. As part of your assignment you will organize, and present statistics to the coach. Currently the information is stored in three different Excel workbooks: offense, defense, and special teams. You will standardize the worksheets into a summary document as well as correcting errors, and safe guarding against future issues.

0.000

21

Group the Offense and Defense worksheets.

Click cell A1:A2 and fill contents and formatting across the grouped worksheets.

3.000

22

With the sheets still grouped, select the range A3:F3.

Center the text in the cells.

Fill the cells with the color Blue, Accent 1, Lighter 60%.

Ungroup the worksheets.

Click the Defense worksheet and modify cell A2 to display the text Defense.

4.000

23

Click the Offense worksheet.

Trace the error in the cell that contains the #DIV/0 error.

Enter an IFERROR function that returns 0 in cell D6 for the existing value, C14/B14.

3.000

24

In cell A4 on the Offense-Defense Totals worksheet, insert a function that will total the number of touchdowns from passing, rushing, and receiving.

Take care to reference any relevant worksheets and relevant cells to calculate this total.

3.000

25

In cell B4 on the Offense-Defense Totals worksheet, insert a function that will total the number of yards from passing, rushing, and receiving.

Take care to reference any relevant worksheets and relevant cells to calculate this total.

3.000

26

In cell C4 on the Offense-Defense Totals worksheet,