CGS 2100 Valencia College Week 6 Ridge Mountain Resort Excel Worksheet
Lab OverviewScenario/SummaryThis week it seems like everyone needs an answer to a question. The Accounting Manager for Operations has asked you to update some analysis prepared earlier in the year. In an attempt to predict year-end changes in net income, you have been asked to prepare some reports on changes in condominium rental fees, ski lift tickets, and X Game tickets and pricing.The Sales Department for Luxury Condominiums wants to know how increasing their rental pricing by varying percentages will affect the existing pricing. The manager would like to know how that will increase the Summit Ridge Mountain Resort's total revenue. A one-variable data table might be good to create these results.The supervisor in charge of setting prices for lift tickets also needs some help. She wants to price the lift tickets so that they are affordable but yet offer the resort a chance to improve its net income at the end of the season. A one-variable data table might be able to answer this question. Resort Management has a target income for the X Games. They need help determining the best combination of pricing and quantity of tickets to sell for the X Games to be a success. A two-variable data table might be best to display these results.And there are more puzzling questions. Resort Management has reviewed variables such as lift ticket prices, restaurant revenues, retail revenues, maintenance costs, and payroll. They would like you to create a Scenario Summary to determine optimistic, mid-range, and pessimistic scenarios for the resort. The Operations Manager would like to know how these changes might affect revenue, expenses, and net income. A Scenario Summary would be a good choice for this analysis.Last, all of the Warming Hut Management need a little help with some investigation on their data. They have quite a bit of data collected but need some assistance making sense of it. It may be time to polish your skills with Pivot Tables, because the management has many questions and concerns. Your boss is confident about your ability to be creative and customize some Pivot Tables that are unique to each Hut. What-If questions need solid answers. It is time to provide a professional response. DeliverablesAfter completing the steps below, turn in one Excel 2016 workbook. Rename the workbook with your lastname_first initial_Week6_Lab. xlsx. Example: If your name were Jane Doe, your workbook would be Doe_J_Week6_Lab.xlsx.LAB 6 – What-If-AnalysisStepTaskPoints PossiblePoints ReceivedComments1Complete Income Statement1a - gComplete formulas & formatting4 2Build One-Variable Data Tables2aOne-Variable Data Table for condo rentals3 2bOne-Variable Data Table for ski lift tickets3 2cApply conditional formatting2 2dAnswer question in G175 3Build Two-Variable Data Table3aEnter Net Income and format3 3bApply conditional formatting3 3c-dAnswer question in G175 4Scenarios and Summary4aName Cells for Scenario2 4bCreate three scenarios4 4cCreate Scenario Summary2 4dMove sheet & apply formatting2 5Create Pivot Tables5a, bBuild Pivot Table 13 5cBuild Pivot Table 23 5dBuild Pivot Table 23 5eCreate a chart for one pivot table2 5fApply professional formatting2 6Create Documentation Sheet6aCreate Documentation Sheet2 6bOrganize worksheet contents1 6c, dFormat Documentation Sheet1 Comment: What you learned from completing this Lab5 TOTAL POINTS600 Week 6 Grading RubricLab ResourcesMicrosoft Office: Excel 2016Lab StepsPreparationYou will be using Microsoft Excel 2016 for this lab.Be sure you have read the required chapter materials and reviewed the hands-on exercise videos located on the Lesson page before you begin the lab.Please do not rely solely on the hands-on exercise videos to complete this week's lab. The videos provide detailed examples walking you through the hands-on exercises. Applying the hands-on exercise examples will provide both practice and instruction of what to complete.Begin: Open and SaveDownload the spreadsheet Copy of Student_File_Week6_LabV2.xlsxStep 1: Complete Income StatementIt is time to build the formulas needed to complete the income statement. Begin working on the Income Statement worksheet and complete formulas for all of the cells marked in gray. Enter the formula to calculate the Condo Rentals Revenue in C13 Multiple the Quantity of Condo Rental Days * the Price for Condo Rental.Enter the formula to calculate the Ski Lift Revenue in C14 Multiple the Quantity of Ski Lift Tickets * the Price for Ski Lift Tickets.Enter the formula to calculate the Winter X Games Revenue in C17 Multiple the Quantity of X Games Tickets * the Price for X Games Tickets.Enter the formula to calculate the Total Revenues in C20 and Total Expenses in C34. Make sure the cell references for Total Revenues and Total Expenses are placed in cells C37 and C38 respectively.Enter the formula to calculate the Net Income in cell C39. Apply professional formatting to all of this data using the image below as a guide.Income StatementStep 2: Build the One-Variable Data TablesNow that the Income Statement is complete, begin to address the questions about the condo rentals and ski lift tickets.Build a one-variable data table based on condo rental days. The initial values for Revenue and Net Income for cells I6 and I7 should be cell references from the income statement. Build a one-variable data table based on the quantity of ski lift tickets. The initial values for Revenue and Net Income for cells I14 and I15 should be cell references from the income statement.Apply conditional formatting to highlight Net Income of at least $250,000 for each of the one-variable data tables. In cell G17, a question exists. "If management has a target net income of at least $250,000, which of the above SPECIFIC scenarios in the condo rentals or ski tickets one-variable data table would you recommend using? Explain your reasoning." Make sure you place your answer in the text box beginning in cell G20.Apply professional formatting to all of this data using the image below as a guide.One-Variable Data TablesStep 3: Build the Two-Variable Data TableOnce the one-variable data tables are complete, begin to address the questions about the X Games tickets sold. Build a two-variable data table based on the quantity and price of the X Games tickets sold. Enter a reference to Net Income in cell I27 from the income statement.Apply conditional formatting to highlight Net Income of at least $250,000 for each of the two-variable data table.In cell G37, a question exists. "If management has a target net income of at least $250,000. Which of the above SPECIFIC combinations of price and quantity of X Games Tickets in the two-variable data table would you recommend using? Explain your reasoning."Make sure you place your answer in the text box beginning in cell G40.Apply professional formatting to all of this data using the image below as a guide.Two-Variable Data TableStep 4: Create the Scenario SummaryYou addressed quite a few questions. Now it is time to create a Scenario Summary. Assign names to all of the income statement cell values in column C in the assumptions, revenues, expenses, and summary sections using the labels in column B. For example, select cells B5:C10, and then on the Formula tab in the Defined Names Group, select "Create from Selection" and use the left column as the name (repeat on lower sections).Build three scenarios by changing cells C8, C15, C16, C29, and C30 using the following data: Optimistic, Mid-range, and Pessimistic.Scenario DetailsGenerate the Scenario Summary using C37:C39 as the result cells.Move the Scenario Summary sheet after the Income Statement and apply professional formatting to all of this data using the image below as a guide. Completed ScenariosStep 5: Create Pivot Tables From Warming Hut DataSelect the Warming Hut Sales Worksheet. You notice the data are not formatted or organized well. Use the Warming Hut Sales data to build Pivot Tables.Build the first Pivot Table to summarize total sales by category and by location. Name this sheet Pivot Table 1.Build the second Pivot Table to summarize total sales by category and by season for only the Summit View location. Name this sheet Pivot Table 2.Build the third Pivot Table to summarize total sales by month and by product for only the Mogul Hill location. Name this sheet Pivot Table 3. Choose one of the created Pivot Tables and add a professional chart to the same worksheet. Apply professional formatting to all of this data using the image below as a guide. Step 6: Create a Documentation SheetClean up the formatting of your Excel workbook, taking into account professional appearance.The Minimum Requirement (per the Grading Rubric)Insert a new spreadsheet into the workbook. The documentation sheet should be the first sheet in the workbook.Make certain all contents of the workbook are properly noted on the documentation sheet. Make certain each tab has a descriptive name for each tab (sheet) in the workbook.Create the professional documentation worksheet. Be sure to include a description of each worksheet. An image is provided below.TranscriptSummit Ridge Mountain ResortIncome Statement & Sales AnalysisAuthor : Student NameDate: 8/16/2018Purpose: Detailed analysis concerning SRMR income statement variables and Warming Hut SalesContents:Income Statement : Summit Ridge Mountain Resort Income Statement with one & two variable data tablesScenario Summary : Scenario Summary showing Revenue, Expenses and Net Income for optimistic, mid range and pessimisticWarming Hut Sales: Sales by product, category, month and season for all Warming Hut locationsPivot Table 1: Total Sales summarized by location and by categoryPivot Table 2: Total Sales summarized by season and by category for just the Summit View locationPivot Table 3: Total Sales summarized by month and by product for just the Mogul Hill location Finish and SubmitSave your Excel file. Make sure you are aware as to where your files are physically saved. Saving your file often is good practice (Ctrl + s).Your Excel file should contain seven worksheets.Documentation PageIncome StatementScenario SummaryWarmingHutSalesPivot Table 1Pivot Table 2Pivot Table 3Submit one workbook. When submitting the workbook, provide a comment in the comments area explaining what you learned from completing this lab activity. File naming convention: If your name is Jane Doe, then your file should be named very similar to Doe_J_Week6_Lab.xlsx.PreviousNext