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.
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|
Week 6 Grading Rubric