Excel Mid Term Exam
Attempt the Question and Save the Workbook as your firstlastnameMidtermExam
and submit to the correct dropbox.
Open the workbook for Rockview Resort and Spa from eLearn. Review and complete the
worksheet as follows:
A. Data Entry (1)
1. The estimated percentage of revenue for Indirect Expense Categories should be as follows:
Administration 3%, Depreciation 2.5%, Energy 1.8%, Insurance 1.5%, Maintenance 1.1%
and Marketing 2.8%.
Ensure you have correct number formatting for accurate data entry.
B. Formulas (5)
Calculate Direct Expenses, Indirect Expenses, and Net Income for each profit center at the
resort. Use cell references for all formulas (no numeric values), and absolute references
wherever possible.
1. In the row under Cost of Sales, add the label Direct Expenses. The Direct Expenses for
each room are 1/5 of the gross profit (Revenues minus Cost of Sales).
2. Each Indirect Expense is to be calculated on the given estimated percentages, of Revenue
from each room.
(E.g. Administrative expense is 3% of banquet revenue for the banquet room and 3% of the
business centre revenue for the business centre, etc.)
3. Calculate all row and column totals - for revenues, costs, and expenses.
4. Net Income is then calculated by deducting all costs/expenses(Direct & Indirect) from
revenues.
C. Format (4)
1. Apply appropriate class-recommended number formatting, borders, column widths and
alignment to all values and labels, remembering to spell-check.
2. Merge and centre the table heading and subheading across the width of the table.
If necessary, expand columns to show the content of the entire cell. Columns B to J should be
a consistent width.
3. Ensure column headings display as one word on each line within the row.
Freeze the headings (top three rows.)
4. Add your name and the file name in the footer.
D. Functions (6)
1. Under the sub-title, add a row and enter a function that displays the current date. Format
appropriately.
2. Under net income, in column B, calculate the lowest net income, and the number of indirect
expense accounts using functions.
3. In the row labeled ‘Bonus’, enter an IF function to calculate 1% of the net income in each
column (excluding the total) that has a net Income over $100,000. Otherwise, it should
display 0. Format appropriately.
4. At the bottom of the sheet, use a PMT function with cell references to calculate the monthly
payment that would be needed to pay off a loan to cover the total marketing expenses.
E. Chart (4)
Once your data and formulas have been entered and verified:
1. Create an appropriate chart to reflect the proportion of net income from each profit center
(room). Fit the chart across columns D – J (approximately), beside the percentage increase
data.
2. Provide a title relevant to the data in your chart. Show the legend to the right of the chart,
with a solid line border in a colour of your choice.
3. Display the data labels as percentages only, at outside edges of chart graphic.
4. Add a shape that says, “The lounge is most profitable.”
