Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Week 6 lab summit ridge mountain resort student

08/10/2021 Client: muhammad11 Deadline: 2 Day

BIS 155 Week 6 ILab Tutorial

Warming Hut Management
This 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.

Deliverables
After 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-Analysis
Step Task Points Possible Points Received Comments
1 Complete Income Statement
1a – g Complete formulas & formatting 4
2 Build One-Variable Data Tables
2a One-Variable Data Table for condo rentals 3
2b One-Variable Data Table for ski lift tickets 3
2c Apply conditional formatting 2
2d Answer question in G17 5
3 Build Two-Variable Data Table
3a Enter Net Income and format 3
3b Apply conditional formatting 3
3c-d Answer question in G17 5
4 Scenarios and Summary
4a Name Cells for Scenario 2
4b Create three scenarios 4
4c Create Scenario Summary 2
4d Move sheet & apply formatting 2
5 Create Pivot Tables
5a, b Build Pivot Table 1 3
5c Build Pivot Table 2 3
5d Build Pivot Table 2 3
5e Create a chart for one pivot table 2
5f Apply professional formatting 2
6 Create Documentation Sheet
6a Create Documentation Sheet 2
6b Organize worksheet contents 1
6c, d Format Documentation Sheet 1
Comment: What you learned from completing this Lab 5
TOTAL POINTS 60 0
Week 6 Grading Rubric

Lab Resources
Microsoft Office: Excel 2016
Options for Accessing Microsoft Excel 2016

Use a personal copy on your PC. You can request a copy of Microsoft Office 2016 via the Student Software Store icon on the Course Resources Page.
If you are a MAC user, click to read the MAC User Information.
If you do not have Excel 2016 installed locally, then access the software by going to the Course Resources page, Lab Resources section, and click the Virtual Lab Citrix icon.
Lab Steps
Preparation
You 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 Save
Download the spreadsheet Week 6 Lab – Summit Ridge Mountain Resort Student.xslx. (Links to an external site.)Links to an external site. You will be prompted to save the file. Click yes.

Open the saved file from your Download folder on your computer.

Note: If you are using the Remote Lab environment, you will need to follow the instructions for uploading the file. These instructions can be found on the Lab page when you click on the Lab icon on Course Resources.

To save the spreadsheet with a new file name,

open the workbook in Excel 2016; and
in Excel, click File and then Save As and rename it as lastname_first initial_Week6_Lab.xlsx (Jane Doe would save the file as Doe_J_Week6_Lab.xlsx).
Step 1: Complete Income Statement
It 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 Statement
Step 2: Build the One-Variable Data Tables
Now 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 Tables

Step 3: Build the Two-Variable Data Table
Once 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 Table
Step 4: Create the Scenario Summary
You 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 Details
Generate 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 Scenarios
Step 5: Create Pivot Tables From Warming Hut Data
Select 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.
Click Image to Expand

Step 6: Create a Documentation Sheet
Clean 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.
Click Image to Expand

Transcript
Finish and Submit
Save 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 Page
Income Statement
Scenario Summary
WarmingHutSales
Pivot Table 1
Pivot Table 2
Pivot Table 3
Submit 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.

4 years ago

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Calculation Guru
Ideas & Innovations
Academic Master
Quality Homework Helper
Phd Writer
Instant Homework Helper
Writer Writer Name Offer Chat
Calculation Guru

ONLINE

Calculation Guru

Hello, I an ranked top 10 freelancers in academic and contents writing. I can write and updated your personal statement with great quality and free of plagiarism

$30 Chat With Writer
Ideas & Innovations

ONLINE

Ideas & Innovations

I have read and understood all your initial requirements, and I am very professional in this task.

$42 Chat With Writer
Academic Master

ONLINE

Academic Master

I will cover all the points which you have mentioned in your project details.

$38 Chat With Writer
Quality Homework Helper

ONLINE

Quality Homework Helper

I have read and understood all your initial requirements, and I am very professional in this task.

$42 Chat With Writer
Phd Writer

ONLINE

Phd Writer

You can award me any time as I am ready to start your project curiously. Waiting for your positive response. Thank you!

$40 Chat With Writer
Instant Homework Helper

ONLINE

Instant Homework Helper

I have read your project details. I can do this within your deadline.

$26 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Information System - Conductivity of solutions lab - Chcics304b work effectively with carers - Vacuum impregnation process for motors - Advance Taxation - Sap hybris commerce documentation - Global variables in labview - BComm7 - Ieee 802.15 4 architecture - Miss tutor - Unix file permissions cheat sheet - Www measureup com portal - Hitotsu futatsu mittsu yottsu itsutsu muttsu - What is a conditional equation - London grand bend bicycle tour case study - I need a assignment done that has a part A and B done for week 5 and Week 7 for My Marketing in an Global Enviroment - Aqa gcse problem solving questions 2008 - Swms working in ceiling space - Sentencing models - Edu 540 week 7 - Unit V Assessment - Http www learner org courses envsci interactives carbon - Close reading questions and answers - We need more humanities majors summary - Harvard referencing la trobe - Multiple choice questions on strategic planning - How much stomach acid can be neutralized by an antacid - Assignment: Treatment Options for Service Members-w7-6212-A - Berger the developing person through childhood and adolescence - Cultural interview paper questions - Richard rodriguez the lonely good company of books - Swot analysis of samsung 2018 - First national bank davis ok - Fern howard emergency lighting - Identifying unknown chemicals in science labs - Vcaa music group performance list - Human body temperatures have a mean of - Hot springs are heated geothermally by underlying __________ - Dateline ira bernstein - DQ 1 - Week 2 Discussion - What are the elements of music - Sociology - Iso 10474 en 10204 - St stithians past papers grade 11 - Khp and naoh titration lab report - Iodine solution produces no colour with - Ness alarm systems perth - A christmas carol conclusion - Check resmed 30 90w power supply - Poster slogans on education - Research Paper - William fine method risk assessment - Ieee journal of the electron devices society - Art - Chain ratio method - Discussion 2 - Coca cola mission vision statement - Conversion cycle in accounting information system - Miss clairol by helena maria viramontes - Argumentative essay about education should be free for everyone - Accenture myperformance self input example - Complete subject and predicate examples - Example of soap note nurse practitioner - Duty of trust and confidence cipd - Shore to ship power - 978 0 07 803475 6 - Where did cadbury originate - Macville risk management policy - The evolution of strategy at procter & gamble case study - 100 pair cable colour code - Ceremony burn this flag - Briar rose sleeping beauty analysis - Discussion 5 - Supposed healing tonic sold by hucksters - Enterprise risk management - Tesco call centre dundee - Ppl jeff nippard pdf - Oceanview marine company 30 22 - La gerche forest walk - Naplan persuasive writing examples - Tenth green pty ltd bunbury - Soc 110 week 5 team proposal - Hp bl460c gen8 quickspecs - Focused cough assessment - Proposal (Government essay) - Flow measurement lab report discussion - How old was rebekah when she married isaac - Week 5 Research Paper: Develop a Computer/Internet Security Policy - For Guru Olivia Only - Cost classification in management accounting pdf - Salesforce recruitment and selection process - 51 waterperry drive canning vale - Mountain man beer case study solution - Navane discontinued - Word chapter 1 grader project homework 3 project g - International business 16th edition pdf - Standard deviation with negative numbers - Icd 10 right knee surgery - Fire eye flame scanner manual