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

Download and open the file named Excel-6-9-12_Start.

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 of 20 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 $10 increments.

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

Load the Solver add-in if it is not already loaded.  

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.  

Generate the Answer Report.

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.  

Add borders to all cells.

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, insert a formula to calculate total completion rate based on data in the Passing worksheet.  

Completion rate is calculated by dividing the total completed passes by the total attempted passes.

3.000

27

Click the Defense worksheet and create a validation rule for cells B4:F12 that requires a whole number and restricts entering number less than 0.  

Create an input message that reads the following: Enter all stats as whole numbers. (include the period).  

Choose the Stop style and enter the following error message: Stats cannot be less than 0 (no period).

3.000

28

Click the Offense-Defense Totals worksheet and enter functions in cells A7:C7 to calculate the total tackles, total forced fumbles, and total interceptions, using data on the Defense worksheet.  

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

3.000

29

On the Offense-Defense Totals worksheet, create a hyperlink in cell C3 that links C3 on the Offense-Defense Totals worksheet to cells B3:C3 on the Offense worksheet.  

On the Offense-Defense Totals worksheet, create hyperlinks in cells A6 (Total Tackles), B6 (Forced Fumbles), and C6 (Interceptions) that link these cells on the Offense-Defense Totals worksheet to the corresponding cells on the Defense worksheet.  

Test all four hyperlinks to confirm the links work as intended.

4.000

30

Simultaneously create a footer on the Offense, Defense, and Offense-Defense Totals worksheets with the sheet name code in the center and the file name code on the right side.  

Apply landscape orientation to these same worksheets.

Center these same worksheets horizontally on the printouts.

Ungroup the worksheets.

6.000

31

CH012 – Project Description: 

As the department head of the Information Systems Department at a university, you are responsible for developing the class teaching schedules for your faculty. You have a tentative Fall 2015 schedule developed in sequence, but you want to ensure that you are not double-booking classrooms or faculty. To help you review room and faculty schedules, you will need to sort the original list in various ways. In addition, you want to create a model to use as a template for future semesters and to share with other department heads.

0.000

32

Add a new worksheet at the end of the workbook, naming the worksheet Macro Code.

Group all worksheets and apply Retrospect theme.  

With worksheets still grouped, apply Aspect theme color.

Ungroup the worksheets.

On the Sequential worksheet apply Heading 3 cell style to the column labels in the range A2:G2.

 

3.000

33

Add the Developer tab to the Ribbon is necessary.

Select cell A1 on the Sequential worksheet.  

Record a macro named RoomSort to be stored in this workbook.   

Locate to cell A2 and, using the Sort dialog box, sort the data by Room in alphabetical order.

Add a second level sort that sorts the Days column using the following custom sort order: MWF, MW, M, W, TR, T, R, S.

Add a third level sort that sorts the Start Time column from earliest to latest.

Return to cell A1 on the Sequential worksheet.

Stop the recording.

3.000

34

Insert a button (form control) on the Sequential worksheet, using the range H2:I3 in which to draw the button.

Assign the button to the RoomSort macro.  

Edit the text that appears on the button to display the text RoomSort. 

3.000

35

Click on the Macros button on the Ribbon, select the RoomSort macro, and then click Edit.  

Copy the displayed code beginning with the text Sub RoomSort () and ending with the first instance of the text End Sub.  

Paste the contents in cell A1 of the Macro Code worksheet.

Close any open window for the macro.

2.000

36

Ensure that the worksheets are correctly named and placed in the following order in the workbook:
          Scenario Summary, Answer Report 1, Budget
          Offense, Defense, Offense-Defense Totals
          Sequential, Macro Code 

Save the file as an Excel workbook and submit to MyITLab for grading. 

3.000

 

Total Points

100.000