As the assistant to the Dean of STEM (science, technology, engineering, and mathematics) at West Shore Community
Question:
As the assistant to the Dean of STEM (science, technology, engineering, and mathematics) at West Shore Community College, it is your responsibility to review the budgets for the departments in the division and help with a budget forecast for the upcoming academic year. You’ve decided to use Excel formulas and functions to help with this analysis.
a. Open IL_EX_1-4.xlsx from the location where you store your Data Files, then save it as IL_EX_1_Budgets.
b. Move the labels in the range A6:A11 to the range A5:A10.
c. Enter Total in cell A11, then use AutoSum to calculate the total 2020 expenses for all departments in cell B11.
d. Enter Average in cell A12, then use the AutoSum arrow to enter a function in cell B12 that calculates the average 2020 expenses for all departments. (Hint: make sure you include only the department data.)
e. Use the fill handle to copy the formulas in the range B11:B12 to the range C11:C12.
f. Using cell references, enter a formula in cell D5 that calculates the 2022 Budget for the engineering department, using the increase shown in cell F2 over the 2021 expenses in cell C5. Use absolute cell references where necessary. (Hint: Multiply the percentage in cell F2 by the 2021 expenses in cell C5, then add that amount to the 2021 expenses in cell C5.)
g. Use the fill handle to copy the formula in cell D5 to the range D6:D10.
h. Use the fill handle to copy the formulas in the range C11:C12 to the range D11:D12.
i. Enter a formula in cell F5 that calculates the percentage increase in total expenses from 2020 to 2021. (Hint: The 2020 total is in cell B11 and the 2021 total is in cell C11.)
j. Change the page orientation to landscape, then turn on gridlines for printing.
k. Enter your name in cell A14.
l. Preview the worksheet in Backstage view. Compare your screen to FIGURE 1-26.
m. Save your work, then submit the worksheet to your instructor as directed.
n. Close the workbook and exit Excel.
FIGURE 1-26
Thermodynamics An Engineering Approach
ISBN: 978-0073398174
8th edition
Authors: Yunus A. Cengel, Michael A. Boles