U_Assessment_Extreme_H2O_Instructions - Protected View - Saved to this PC- Ab ceferences Mailings Review View Help RCM an contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing 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. 1 On the Break-Even Analysis worksheet, enter the correct formula to calculate the Gross Revenue in cell D7 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 D 18. 1 6 2 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. 7 2 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 columns (range 04.119) using a data table 8 Format the range G4.119 as Currency 1 9 1 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. 10 2 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 11 2 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 $5,000 or greater will have a light green background and dark green text. D. Focus F n bel Vackground and dark green text Format cell F6 to hide the result of the formula in the cell. 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 Created On 12/15/2019 YO19_Excel_Ch 10_Assessment - Extreme H20 11 Grader. Instructions Excel 2019 Project Step Instructions Points Possible 14 2 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. 15 2 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 S1 increments. Enter the text Price in cell 14. 16 Save and close ExcelCh10_PS2_Float x/sx Exit Excel Submit your files as directed. 0 Total Points 20 Focus O - Clipboard Wilge Center x Delete Font Cell Alignment Conditional Format as Formatting Table Styles Styles Et Format D6 Number > Cells B D E F 1 G K 1 Extreme H20 Break-even Analysis for River Float Project Number Expenses Revenue 75 Net Income Guests 225 50 55 60 65 2 3 Revenue 4 Average Number of Guests (per day) 5 Days of Operations per Year 6 Price 7 Gross Revenue 8 Expenses 9 Fixed Costs 10 Site Development 11 Equipment 12 Insurance 13 Total Fixed Costs 14 Variable Costs 15 Labor expense (per day) 16 Transportation (per guest) 17 Total Variable Costs 18 Net Income 70 75 $32,200 $3,450 $1,400 80 85 90 95 100 $275 $0.85 105 110 115 19 120 20 21 22 23 * Break-Even Analysis PriceAndGuest DaysAndPrice + 53 Type here to search Cells Editing Analysis Ser F F. G K M N P 0 R Two-Variable Data Table Price Even Analysis PriceAndGast DaysAndPrice 5:58 PN (40) 4/17/20 WupUL General ... Insert Paste FO B 1 la. A AY O KI IM DX Delete 2 Merge & Center $ % Se Conditional Format as Formatting Table Styles Styles Sort & Find & Filter Select It Format Clipboard Analyze Data Font Alignment N Number Cells Editing Analysis Se A1 Extreme H20 A B D E F G H K L M IN O R S T 1 Extreme H20 Break-even Analysis for River Float Project own - 7 8 9 10 11 12 13 14 15 16 17 18 19 20 HA 21 DaysAlPrice PriceAndGuest Break-Even Analysis 5:59 4/17/ O Type here to search U_Assessment_Extreme_H2O_Instructions - Protected View - Saved to this PC- Ab ceferences Mailings Review View Help RCM an contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing 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. 1 On the Break-Even Analysis worksheet, enter the correct formula to calculate the Gross Revenue in cell D7 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 D 18. 1 6 2 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. 7 2 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 columns (range 04.119) using a data table 8 Format the range G4.119 as Currency 1 9 1 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. 10 2 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 11 2 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 $5,000 or greater will have a light green background and dark green text. D. Focus F n bel Vackground and dark green text Format cell F6 to hide the result of the formula in the cell. 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 Created On 12/15/2019 YO19_Excel_Ch 10_Assessment - Extreme H20 11 Grader. Instructions Excel 2019 Project Step Instructions Points Possible 14 2 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. 15 2 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 S1 increments. Enter the text Price in cell 14. 16 Save and close ExcelCh10_PS2_Float x/sx Exit Excel Submit your files as directed. 0 Total Points 20 Focus O - Clipboard Wilge Center x Delete Font Cell Alignment Conditional Format as Formatting Table Styles Styles Et Format D6 Number > Cells B D E F 1 G K 1 Extreme H20 Break-even Analysis for River Float Project Number Expenses Revenue 75 Net Income Guests 225 50 55 60 65 2 3 Revenue 4 Average Number of Guests (per day) 5 Days of Operations per Year 6 Price 7 Gross Revenue 8 Expenses 9 Fixed Costs 10 Site Development 11 Equipment 12 Insurance 13 Total Fixed Costs 14 Variable Costs 15 Labor expense (per day) 16 Transportation (per guest) 17 Total Variable Costs 18 Net Income 70 75 $32,200 $3,450 $1,400 80 85 90 95 100 $275 $0.85 105 110 115 19 120 20 21 22 23 * Break-Even Analysis PriceAndGuest DaysAndPrice + 53 Type here to search Cells Editing Analysis Ser F F. G K M N P 0 R Two-Variable Data Table Price Even Analysis PriceAndGast DaysAndPrice 5:58 PN (40) 4/17/20 WupUL General ... Insert Paste FO B 1 la. A AY O KI IM DX Delete 2 Merge & Center $ % Se Conditional Format as Formatting Table Styles Styles Sort & Find & Filter Select It Format Clipboard Analyze Data Font Alignment N Number Cells Editing Analysis Se A1 Extreme H20 A B D E F G H K L M IN O R S T 1 Extreme H20 Break-even Analysis for River Float Project own - 7 8 9 10 11 12 13 14 15 16 17 18 19 20 HA 21 DaysAlPrice PriceAndGuest Break-Even Analysis 5:59 4/17/ O Type here to search