Create and manage scenarios. Use Goal Seek.

The company has determined kilowatt-hours used for last year and now wants to predict how usage can be controlled over the next year. It has identified a general growth percentage of 1.5%. This means its usage is expected to increase by at least that percentage. To balance that, each office applies a savings factor that reduces that growth factor. The directions for completing the formulas are on the sheet. When the projections are completed, youâ€™ll use Goal Seek to see how or if the company could reach a targeted kilowatt-hours saving level with a probable growth rate.

• Open Excel_LA9-29 start file and save it as your initials9-29.
• In cell D15, calculate first-quarter kilowatt-hours for next year using the forecast formula explained on the worksheet in cell B5. This formula uses absolute and mixed references. Hint: Formula in cell D15 should be =C15*(1+\$H\$4)*\$H6. Copy the formula without formatting to complete this section of the sheet for each quarter.
• In cell H15, use SUM function with a range that does not include values from last year. Copy the formula to cell H18. Show a Next Year Total in cell H19.
• Add a scenario named 1.5% Growth with the changing cell as cell H4. Include your name in the comment.
• Use Goal Seek to find a growth rate that will result in 5,000,000 in total kilowatt- hours (cell H19). Use cell H4 as the changing cell. Save the Goal Seek solution as a scenario named New Growth, and include your name in the comment.
• Create a scenario summary report for cells D15:G18. Make sure the report sheet is placed to the left of EnergyFrocast sheet.
• Make a copy of the EnergyForecast worksheet, hiding columns A:C. Show formulas. Place the formula sheet to the right of the EnergyForecast sheet.
• Save and close the workbook. Submit your work.