Step

Instructions

Points Possible

1

Start Excel. Open the downloaded file named Excel_Ch10_PS2_Float.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

0

2

The company would like to determine the number of customers it should book per day in order to break even. They would like to analyze this using a break-even analysis. On the Break-Even Analysis worksheet, enter the correct formula to calculate the Gross Revenue in cell D7.

1

3

Enter the correct formula to calculate the Total Fixed Costs in D13.

1

4

In cell D17, enter the correct formula to calculate the Total Variable Costs based upon the average number of guests per day and the days of operations per year.

1

5

Enter the correct formula to calculate the Net Income in D18.

1

6

Management thinks an average of 85 guests a day would be a realistic average. Use Goal Seek to find out what the price would need to be to come out exactly even with 85 guests per day and $ 0 in Net Income.

2

7

A data table with conditional formatting will help to better visualize the expenses, revenues, and net income of the business. Fill in the Expenses, Revenue, and Net Income cells (range G4:I4) and the complete the data table using the range F4:I19.

2

8

Format the range G5:I19 as Currency.

1

9

Copy the information in range A3:D18 from the Break-Even Analysis worksheet and paste it in range A4:D19 on the worksheet PriceAndGuest. Keep the source column widths.

1

10

Create a data table in the range F5:N22. The price (row 6) needs to start at $4 and increase to $11 in $1 increments, and the number of guests (column F) needs to start at 35 and increase in increments of 5. Be sure to add a reference to cell D19 in cell F6.

2

11

Format the data in the data table so any net profit under -$5,000 has a light red background and dark red text, data that is above or equal to -$5,000 and below $5,000 will have a light yellow background and dark yellow text, and anything greater than $5,000 will have a light green background and dark green text.

2

12

Format cell F6 to hide the result of the formula ( ;;;) in the cell including if the result was positive, negative, or zero.

1

13

Copy the information in range A3:D18 from the Break-Even Analysis sheet to the range A3:D18 on the sheet DaysAndPrice. Keep the source column widths.

1

14

Add a scroll bar to the right of the inserted cells in column F that will change the days of operations per year between the values of 225 and 275 one day at a time. Enter the text Days of Operations in cell F4 and center the text.

2

15

Add a scroll bar to the right or below of the first scroll bar in column I that will change the price between the values of $4 and $11 by $1 increments. Enter the text Price in cell I4.

2

16

Save and close Excel_Ch10_PS2_Float.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

20