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

Red bluff needs and wants

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

Project Description:
Barry Cheney, the Golf Course Manager at the Red Bluff Golf Course & Pro Shop, has been considering expanding the clubhouse to accommodate a steady increase in business. This expansion could include more space for the pro shop and more guest accommodations. Barry will need to provide a detailed analysis of past sales along with sales forecasts to assure William Mattingly, the resort’s CEO, that the money spent on the improvements and expansion will have positive financial benefits for Red Bluff. To increase management’s understanding of the current capacities, Barry has collected data about traffic, sales, and product mix. He has asked you to analyze this data, using Excel’s What-If Analysis tools.

Steps to Perform:
Step

Instructions

Points Possible

1

This exercise starts on page 565 of your text. Start Excel. Download and open the file named Excel_Ch10_Prepare_ExpansionAnalysis_2of2.xlsx. Grader has automatically added your last name to the beginning of the filename. Click Enable Editing, if necessary.

0

2

Goal Seek is another scenario tool that maximizes Excel’s cell-referencing capabilities and enables you to find the input values needed to achieve a goal or objective. Goal Seek can be used to determine the number of boxes of golf balls and high-performance golf polo shirts Red Bluff needs to sell to meet its sales goals as well as the selling price of golf shorts and golf umbrellas required to meet its sales goals. On the SalesForecast worksheet, use Goal Seek to determine the quantity of stock golf balls needed to sell in order to meet the sales goal (extended price) of $27,500.

0.4

3

Format cell C5 as a Number with 0 decimal places.

0.3

4

Use Goal Seek to determine the selling price required for golf shorts to meet the sales goal (extended price) of $14,000.

0.4

5

Use Goal Seek to determine selling price of large golf umbrellas to meet the sales goal (extended price) of $7,500.

0.4

6

Use Goal Seek to determine the quantity of high performance golf polos needed to sell in order to meet the sales goal (extended price) of $20,100.

0.4

7

Use the Format Painter to copy the formatting from cell C5 and apply the formatting to cells C6:C8.

0.3

8

Barry Cheney would like you to determine how much net income or profit the Red Bluff Golf Course & Pro Shop is forecasted to generate on the basis of varying retail sales, revenue from services rendered, and variable costs. Formulas can be used to setup best case, worst case, and most likely scenarios. On the ProfitForecast worksheet, in cell D6, calculate the forecasted gross revenue by adding up the revenue from retail sales as well as golf lessons and fees.

0.3

9

In cell D13, calculate the total forecasted fixed costs.

0.3

10

In cell D15, calculate the forecasted employee commissions by multiplying the gross revenue by the employee commission rate.

0.3

11

In cell D16, calculate the forecasted total expenses by adding up the total fixed costs and total variable costs.

0.3

12

In cell D17, calculate the forecasted net income by subtracting the total expenses from the gross revenue.

0.3

13

Create a Best Case Scenario where the total revenue in retail sales is $115,000 and total revenue in golf lessons and fees is $340,000.

0.5

14

Create a Worst Case Scenario where the total revenue in retail sales is $35,000 and total revenue in golf lessons and fees is $90,000.

0.5

15

Scenarios can be viewed individually, but it is often more helpful to view them side-by-side. Generate a Scenario Summary report to show the results of the current scenario, Best Case Scenario, and Worst Case Scenario. Make sure that the result cell is set to D17, Net Income.

0.4

16

Format the Scenario Summary report so that it is easier to read by completing the following. Merge & Center cells B6:C6 and replace the text $D$4 with Retail Sales. Merge & Center cells B7:C7 and replace the text $D$5 with Lessons and Fees. Merge & Center cells B9:C9 and replace the text $D$17 with Net Income.

0.4

17

Edit the Best Case Scenario so that the changing cells are D4:D5 and C15. Keep the current scenario value for cell C15 as 0.17. Edit the Worst Case Scenario so that the changing cells are D4:D5 and C15. Change the scenario value for C15 to 0.095. Create a new Most Likely Scenario using D4:D5 and C15 as the changing cells. Set the scenario value for D4 to 25000, D5 to 162500 and C15 to 0.10.

0.5

18

Create a Scenario PivotTable report to show the gross revenue, commission, and net income of each scenario. Use cells D6, C15, and D17 as the result cells.

0.4

19

Format the Scenario PivotTable report so that it is easier to read by completing the following. In cell A1, replace existing text with Retail Sales and Golf Lessons and Fees. Adjust the width of Column A to 34. In cell A2, type Scenario PivotTable Report. Merge & Center cells A2:D2, apply a Bold style, and adjust the font size to 16. In cell A3, type Scenarios.

0.6

20

In cell B3, type Gross Revenue, in cell C3, type Commission, and in cell D3, type Net Income. Center the data in cells B3:C3 and adjust the width of columns B:C to 14.

0.4

21

Format cells B4:B6 and D4:D6 as Currency with 0 decimal places. Format cells C4:C6 as Percentage with 1 decimal place.

0.3

22

The golf course manager wants you to find the total number of clients, the number of hours of lessons per day, and the number of instructors on duty needed to maximize net income. The worksheet you were given was previously set up with functions to calculate the net income. The Solver tool in Excel can optimize the net income while meeting several constraints within the worksheet. If necessary, load the Solver Add-in. On the GolfLessons worksheet, begin the process of creating a solver model by setting the objective cell to D20. Be sure the objective is set to Max and use cells D4:D5 as the changing cells.

0.4

23

Continue creating the solver model by defining the following constraints. There can only be 4 or fewer instructors scheduled at any given time. Instructors can give anywhere from 7 to 14 lessons per day. The total clients and instructors on duty must be an integer.

0.4

24

Generate a Solver Answer report by modifying the settings as follows. Use the GRG Nonlinear solving method. Under Options, click the All Methods tab and verify the Use Automatic Scaling check box is unchecked. On the GRG Nonlinear tab, click the check to Use Multistart and then click to uncheck the Require Bounds on Variables check box. In the Solver Results dialog box, verify that the Keep Solver Solution option is selected. Create an Answer report.

0.4

25

On the GolfLessons worksheet, restore the original values in the worksheet by typing 1 in cells D4 and D5. Save the Solver model, starting in cell A23.

0.3

26

Edit the constraint in the Solver model that restricts the number of clients to 14 or less. Change it so that the number of clients can be 25 or less. Also, edit the constraint that restricts the number of instructors to 4 or less. Change it so that the number of instructors can be 4 or more. Add a constraint so that the number of instructors on duty can be no more than 7.

0.4

27

Save the revised Solver model, starting in cell B23.

0.2

28

Generate another Solver Answer report with the revised constraints. Be sure to check the Restore Original Values option.

0.2

29

Save and close Excel_Ch10_Prepare_ExpansionAnalysis_2of2. Exit Excel. Submit your files as directed.

0

Total Points

10

Created On: 12/18/2019 1 YO19_Excel_Ch10_Prepare - Expansion Analysis Part B 1.0

McDonald_Excel_Ch10_Prepare_ExpansionAnalysis_2of2.xlsx
SalesForecast
Red Bluff Golf Course & Pro Shop
Forecast of Product Sales
Inventory Items Qty. Price Extended Price Goal
Stock Golf Balls $37.00 $0 $27,500
Golf Shorts 300 $0 $14,000
Large Golf Umbrella 125 $0 $7,500
High Performance Golf Polo $75.00 $0 $20,100
&F

ProfitForecast
Red Bluff Golf Course & Pro Shop
Profit Scenarios
Revenue
Retail Sales $65,000.00
Golf Lessons and Fees $220,000.00
Gross Revenue
Expenses
Fixed Costs
Manager Salaries $5,135.90
Utilities $469.70
Equipment Depreciation $1,234.20
Insurance $360.80
Total Fixed Costs
Variable Costs
Employee Commissions 17%
Total Expenses
Net Income
&F

GolfLessons
Red Bluff Golf Course & Pro Shop
Solver for Golf Lessons
Input Variables
Total Clients 1
Instructors on Duty 1
Revenue
Lesson Fee $190.00
Gross Revenue $190.00
Expenses
Fixed Costs
Utilities $469.70
Equipment Depreciation $1,234.20
Insurance $360.80
Total Fixed Costs $2,064.70
Variable Costs
Instructor Commission 10% $19.00
Supplies per Client $14.95 $14.95
Total Variable Costs $33.95
Total Expenses $2,098.65
Net Income -$1,908.65
Saved Solver Models
&F

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:

Quality Homework Helper
Quick Finance Master
Assignment Solver
Engineering Solutions
Math Guru
Smart Homework Helper
Writer Writer Name Offer Chat
Quality Homework Helper

ONLINE

Quality Homework Helper

I will be delighted to work on your project. As an experienced writer, I can provide you top quality, well researched, concise and error-free work within your provided deadline at very reasonable prices.

$47 Chat With Writer
Quick Finance Master

ONLINE

Quick Finance Master

As an experienced writer, I have extensive experience in business writing, report writing, business profile writing, writing business reports and business plans for my clients.

$42 Chat With Writer
Assignment Solver

ONLINE

Assignment Solver

I am an elite class writer with more than 6 years of experience as an academic writer. I will provide you the 100 percent original and plagiarism-free content.

$38 Chat With Writer
Engineering Solutions

ONLINE

Engineering Solutions

I have done dissertations, thesis, reports related to these topics, and I cover all the CHAPTERS accordingly and provide proper updates on the project.

$19 Chat With Writer
Math Guru

ONLINE

Math Guru

I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You

$24 Chat With Writer
Smart Homework Helper

ONLINE

Smart Homework Helper

Being a Ph.D. in the Business field, I have been doing academic writing for the past 7 years and have a good command over writing research papers, essay, dissertations and all kinds of academic writing and proofreading.

$31 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

Poetry analysis paragraph example - History of California-6 - Why doesn't sand dissolve in water - How to make a poster on endangered species - Need help in US History quiz as fast as possible. - What was humpty dumpty's cause of death geometry - Week 10 - Short Essays - Army prt 4 for the core - Mask you live in discussion questions - Presenting problem case study example - 1992 tarantino crime thriller crossword - Clyde building supplies broughshane - Is stationary a fixed or variable cost - Russian TV - Week Research Paper 2 - Info Tech Strat Plan - Comparison of ethical theories - Air force medical waiver guide - Research journal of english language and literature rjelal - Analysis of food colour lab report - What are the key sources of trader joe's competitive advantage - Was my relationship abusive quiz - Playstation error np 31970 0 - Vce physics cheat sheet - ITGE_Discussion4 - Final Case study - Service gap analysis of pizza hut - Management skills audit template - Jessica ho hong kong - 2 components of fitness - Genghis khan restaurant chatswood - Olg winner's circle rewards login - Mean median mode ungrouped data - Historical background to kill a mockingbird - Butane gas cooker recall - Kay magill company had the following adjusted trial balance - Sample nursing family case study - Personal Anecdote - Funny Incident - I want you to answer my 7 Lab Questions. - AstroloGy bAbA 7340613399 OnLinE reaL VashIKaraN sPecIaLIsT IN Rampur - Tda technical design authority - MGMT Discussion 2.2 (200/250 words) - Starbucks in israel case study - Assignment - Stakeholder management and communication plan - A gift of fire 5th - Consular electronic application center - Which of the following will result in a future value greater than $100? - Bs 1362 fuse operation characteristics - Project management at six flags new jersey - Price discrimination is indistinguishable from dumping - Employment law gcse business - Advantage of gram stain over simple stain - Darkest dungeon brackish tide pool - Informative speech on walt disney - Knight company reports the following costs and expenses in may. - Crows or ravens in australia - Trends and issues in hospitality and tourism industry - Assignment 450 words ( choose one of the movies listed) use the book provided. - Supply and demand curve in excel - In this assessment, you will learn about the differences between clinical and personal recovery and therapeutic communication that fosters personal recovery for people who experience psychotic disorders - You may need to modify the data set you created for the Week 1 Discussion. You will need to have two quantitative variables that - Should cellphones be banned in educational institutions - MKT 398 - Catechism of the catholic church chicago citation - Ethernet cable color order - Bound green group practice - Noneffective Communication - Write a research paper about the Stratosphere layer on 6 pages with double space. - What happens in a positive test for oxygen gas - I wanna be yours poem - Articles using ethos pathos and logos - Many companies are improving interpersonal relations by - A firm pursuing a best cost provider strategy - A valid multiple regression analysis assumes or requires that - Compare and contrast culture - Definition of urban sprawl - Evidence-Based Practice and the Quadruple Aim - Alfresco community edition limitations - Vieques tiene playas muy limpias cierto falso - REP 2 - Linear algebra matlab projects - How the smartphone destroyed a generation - 8 pin mini din rs232 pinout - Importance of personal presentation hygiene and conduct in a salon - Tattoo machine liner spring setup - 8 tyee street box hill - Identify four main types of listening - Math data collection sheets - P4 - Case study assignment - Leadership Theories - Leadership Matrix - King's theory of goal attainment in practice - Bec caruana dance all night - The toshiba accounting scandal how corporate governance failed - Formal pieces of writing - The conspirator movie worksheet answers - Need the answer after 2 hours - Tax year 2013 to 2014 dates - Pvc pipe instrument instructions - Acids bases and salts worksheet answer key