We Helped With This Excel Programming Homework: Have A Similar One?

Create a worksheet using Excel

TABLE 1

Instructions Part # 1:

1.        Apply the Dividend Theme to the worksheet. Bold the entire worksheet.

2.        Change the column widths to best fit.  Continue doing this when necessary until you complete the workbook!

3.        Move row 7 (Square Footage information) to row 16.  Enter the row titles in (Table 2) in the range A7:A15. Right indent the text in the range A8:A13. Copy the row titles in range A8:A13 to the range A18:A23.  Add a column header to cell A17 called Planned Indirect Expenses.

TABLE 2

 4.       Center and italicize the column headings in the range B3:J3. Add a column header to cell J3 called Total. Add a bottom double border to the range B3:J3. Using the Sum function, create a formula to Sum the individual rows 4, 5, and 6 in the range J4:J6.

5.        Sum row 16 in cell J16. Format the range B16:J16 to the Comma format with no decimal places, use a thousand separator (,), and show negative numbers with a negative sign. Change row 16’s height to 35.  Horizontally and vertically center align range B16:J16 (Square Footage values).  Left-align and vertically center cell A16 (Square Footage title).

6.        Increase the font size in cells A7, A14, and A15 to 14-point.

7.        Enter the numbers shown in (Table 3) in the range B18:B23.  Format the range B18:B23 to the Comma format with no decimal places, use a thousand separator (,), and show negative numbers with a negative sign.

TABLE 3

8.        Use the following formulas to accomplish the prorating of the indirect expenses (HINT - absolute and mixed cell referencing will be important when writing the below formulas…though, not all formulas require it):

            a. Actual Administrative Indirect Expenses (cell B8) = Administrative Planned Indirect Expenses (cell B18) * Entertainment Room Total Net Revenue (cell B4) / Resort Total Net Revenue (cell J4).

            b. Actual Depreciation Indirect Expenses (cell B9) = Depreciation Planned Indirect Expenses (cell B19) * Entertainment Room Square Footage (cell B16) / Total Square Footage (cell J16).

            c. Actual Utilities Indirect Expenses (cell B10) = Utilities Planned Indirect Expenses (cell B20) * Entertainment Room Total Net Revenue (cell B4) / Resort Total Net Revenue (cell J4).

            d. Actual Insurance Indirect Expenses (cell B11) = Insurance Planned Indirect Expenses (cell B21) * Entertainment Room Square Feet (cell B16) / Total Square Footage (cell J16).

            e. Actual Maintenance Indirect Expenses (cell B12) = Maintenance Planned Indirect Expenses (cell B22) * Entertainment Room Square Footage (cell B16) / Total Square Footage (cell J16).

            f. Actual Advertising Indirect Expenses (cell B13) = Advertising Planned Indirect Expenses (cell B23) * Entertainment Room Total Net Revenue (cell B4) / Resort Total Net Revenue (cell J4).

            g. Total Indirect Expenses (cell B14) = Sum all of the Actual Indirect Expenses (range B8:B13).

            h. Net Income (cell B15) = Entertainment Room Total Net Revenue - (Entertainment Room Cost of Sales + Entertainment Room Direct Expenses + Entertainment Room Total Indirect Expenses).

            i.  Copy the range B8:B15 to the range C8:I15.

            j. Sum the individual rows 8 through 15 in the range J8:J15.

9.      Apply conditional formatting to range B8:I13 so that cells with a value greater than $15,000 appear with a sky blue background and a black text font color. 

10.      Add a bottom double border to the range B13:J13.

11.      Assign the Currency style with currency symbol, two decimal places, and show negative numbers with a negative sign to the following ranges: B4:J4; B8:J8; and B14:J15. Assign the Comma style with two decimal places, use a thousand separator (,), and show negative numbers with a negative sign to the following ranges: B5:J6 and B9:J13.