excel

YO16XLCH10GRADERPCHW_-_Business_Planning_Analysis_Part_A_14_Instructions.docx

Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH10_GRADER_PC_HW - Business Planning Analysis Part A 1.4

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.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the file named e05ch10_grader_pc_ExpansionAnalysis_1of2.xlsx. Save the file with the name e05ch10_grader_pc_ExpansionAnalysis_1of2_LastFirst using your last and first name. 0.000 2 On the Break-Even Analysis worksheet, in cell D6 calculate the gross revenue by multiplying the total clients by the golf lesson fee. 3.000 3 In cell D13, calculate the total fixed costs associated with providing golf lessons. 3.000 4 In cell D15, calculate the total commission the golf instructors will earn by multiplying the gross revenue by the instructor commission. 3.000 5 In cell D16, calculate the total cost of supplies by multiplying the amount of supplies per client by the total clients. 3.000 6 In cell D17, calculate the total variable costs by adding the total instructor commission and the total cost of supplies. 3.000 7 In cell D18, calculate the total expenses by adding up the total fixed costs and the total variable costs. 3.000 8 In cell D19, calculate the net income by subtracting the total expenses from the gross revenue. 3.000 9 In cell D4, conduct a simple what-if analysis by changing the value to 50, then 60, and finally 64, noticing the changes to the net income in cell D19. 2.000 10 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: 64 Minimum value: 60 Maximum value: 100 Incremental change: 1 Page change: 10 Cell link: D4 5.000 11 In cell D19, apply conditional formatting to highlight the cell to display red text if the value is less than 0 and Green Fill with Dark Green Text if the value is greater than 0. 6.000 12 On the LoanConditions worksheet begin to create a one-variable data table by typing various interest rates ranging from 4% through 10% in increments of 1% in cells D3:D9. 4.000 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.000 14 Finish the one-variable data table to see the monthly payment amount for each interest rate. 5.000 15 Apply conditional formatting to the range E3:E9 using the gradient fill, green data bar option. 4.000 16 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.000 17 Apply custom formatting to cells H4:J4 so that the results of the referenced cells are hidden. 5.000 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.000 19 Apply conditional formatting to the range J5:J14 to highlight cells with a light red fill with dark red text if the value is less than 0 and apply a green fill with dark green text formatting if the values are greater than 2000. 6.000 20 Create a 2-D Line chart using the data in cells G3:I3 and G5:I14. Position the chart so that the top left corner is in cell F16. Add a primary horizontal axis title with the text Client Demand. Include a chart title of Cost-Volume-Profit. 10.000 21 On the FeesAndDemand worksheet begin to create a two-variable data table by referencing the cell that calculates net income in cell E3. 3.000 22 Apply a custom format to cell E3 so that the formula result of the referenced cell is hidden. 4.000 23 Finish the two-variable data table to see how the net income changes when lesson fees and client demand vary. 5.000 24 Format the cells F4:L16 as Currency. Apply conditional formatting to the range to highlight cells with a light red fill with dark red text if the value is less than 0 and apply a green fill with dark green text formatting if the values are greater than 3500. 6.000 25 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Please note: This project continues in part B which can be found in the Product Shared Library. 0.000 Total Points 100.000

Updated: 10/11/2017 1 Current_Instruction.docx