Excel Lab9-31

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.