YO19_Excel_Ch10_Prepare_Expansion_Analysis_PartA

Project Description:

Barry Cheney, the Golf Course Manager at the Red Bluff Golf Course &amp; 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 Start Excel. Open the downloaded file named Excel_Ch10_Prepare_ExpansionAnalysis_1of2.xlsx. Grader has automatically added your last name to the beginning of the filename. 0 2 The Red Bluff Golf Course & Prop Shop offers golf lessons to its clients for a fee of \$140.00 per lesson. The costs for each golf lesson comprises both fixed and variable costs. Conducting a break-even analysis will determine the minimum number of clients Red Bluffs needs to have signed up for golf lessons to offset the costs. On the Break-Even Analysis worksheet, in cell D6 calculate the gross revenue by multiplying the total clients by the golf lesson fee. 3 3 In cell D13, calculate the total fixed costs associated with providing golf lessons. 3 4 In cell D15, calculate the total commission the golf instructors will earn by multiplying the gross revenue by the instructor commission. 3 5 In cell D16, calculate the total cost of supplies by multiplying the amount of supplies per client by the total clients. 3 6 In cell D17, calculate the total variable costs by adding the total instructor commission and the total cost of supplies. 3 7 In cell D18, calculate the total expenses by adding up the total fixed costs and the total variable costs. 3 8 In cell D19, calculate the net income by subtracting the total expenses from the gross revenue. 3 9 In cell D4, conduct a simple what-if analysis by changing the value to 50, then 60, and finally 65, noticing the changes to the net income in cell D19. 2 10 Incorporating a scroll bar form control into the break-even analysis will make it easier to determine the number of clients necessary for Red Bluff to break even. Insert a Scroll Bar (Form Control) onto the Break-Even Analysis worksheet within the range E4:E17. Modify the Format Control properties based on the following: Current value: 65 Minimum value: 50 Maximum value: 120 Incremental change: 1 Page change: 10 Cell link: D4 5 11 Conditional formatting can be used to highlight the Net Income value, in cell D19, in two different ways. One set of formatting can be applied if the net income results in a loss; another can be applied if the net income results in a profit. In cell D19, apply conditional formatting to highlight the cell to display Light Red Fill with Dark Red Text if the value is less than 0 and Green Fill with Dark Green Text if the value is greater than 0. 6 12 A one-variable data table can be used to determine how much the monthly payment on a loan would be based on varying interest rates. Conditional formatting can then be applied to add emphasis and increase the readability of the monthly payment. On the LoanConditions worksheet begin to create a one-variable data table by typing various interest rates ranging from 5% through 11% in increments of 1% in cells D3:D9. 4 13 In cell E2, reference the cell that calculates the monthly payment amount for the loan. Apply a custom format to cell E2 so that the cell appears to be Monthly Payment. 3 14 Finish the one-variable data table to see the monthly payment amount for each interest rate. 5 15 Apply conditional formatting to the range E3:E9 using the gradient fill, green data bar option. 4 16 A one-variable data table can be used to determine how the changes in client demand for golf lessons will affect the expenses, revenue, and net income. You will also apply some conditional formatting to add emphasis and increase the readability of the Net Income. On the Break-Even Analysis worksheet begin to create a one-variable data table by referencing the cell that calculates the total expenses in cell H4. In cell I4, reference the cell that calculates the gross revenue. In cell J4, reference the cell that calculates the net income. 6 17 Apply custom formatting to cells H4:J4 so that the results of the referenced cells are hidden. 5 18 Finish the one-variable data table to see how the expenses, revenue, and net income change when the total number of clients vary from 10 to 100. 5