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.