DePaul University Auto Sum Functions and Simple Math Excel Worksheet Hello, I need help making an excel following the instructions in the files attached, D

DePaul University Auto Sum Functions and Simple Math Excel Worksheet Hello, I need help making an excel following the instructions in the files attached, Document1
20 pts
Goal: To practice relative, absolute and mixed cell referencing, to reinforce worksheet formatting, and
to reinforce use of AutoSum functions and simple math using cell references. Focus on writing formulas
and functions once and filling in the appropriate direction.
Download and save starting file from Canvas. Review picture of finished product, at the end of this
document.
1. Insert 2 rows, to create a title for your worksheet, above the data. Make up and type a name
for a fictious business and type in Cell A1. Set the font size at 16 and merge and center across
the data, to Column I. Check against my picture.
2. The worksheet is divided into 2 sets of information: Sales and Expense.
In the Sales section:
a. In Row 8: use the Autosum function to calculate the total for each month of sales.
b. In H4: use the Autosum function to calculate, for the City of Chicago, the total sales for
all 6 months. Fill this calculation down for all cities.
c. Format cells B4:H8 as Accounting Style.
d. In Column I: use a fillable formula to calculate how much each City’s total is, in
relationship to the total of the cities sales. (each city total/all cities total sales). Format
this column as a Percentage style, displaying 2 decimal places.
In the Expense section:
a. Create this table somewhere to the right, or underneath your data: (See finished
product for questions) This table can really be placed anywhere – but note, that you are
going to need to TYPE these values in, and use cell references from this section in
formulas later. . To make this easier, you could screen shot this table, and just put it as a
picture in the worksheet and use it as a visual guide. Delete this picture from
worksheet, once you have created table. Note that these labels match what is down
Column A – under expenses. Important to keep these in order, so that formula writing
will be correct.
b. Label row 9/10 (See my finished product) to display the months of each expense. Use
row 3 as a visual guide, or copy/paste.
c. In Cell B11: Create a mixed reference formula that takes each month’s total sales (row 8)
and multiplies by the Expense assumption % that is referred to in the table (you created
this, in Step 2A, above) This formula should be fillable (not rewritten) and be filled
across to Column G (Cell G11) and then that row should be filled down to Row 16. Each
Document1
20 pts
formula should be the applicable labeled month’s value multiplied by the appropriate
expense row. Use the F2 key to turn on the color coding of the formula builder and
ensure that the fill function is working the way it’s supposed to. (Cell G16 should be the
calculation of Jun total sales multiplied by depreciation expense 8.75%). You should
spend your time ensuring that B11 is calculated with mixed cell references and is fillable
in each direction. You should not need to type this formula multiple times – write once
and fill across and then fill down. Ensure these values are formatted as the Accounting
Style.
e. Row 17: use the Autosum function to calculate the total for each month of expenses.
f. In Column H, use the Autosum function to calculate the total for all of that row’s values.
d. In Column I: use a fillable formula to calculate how much each row of expenses is, to the
total of expenses. (total of row divided by total expenses.) Show as a two decimal
percentage.
e. Calculate the Net Income, in Row 19. This should be a fillable formula, write once and
fill to the right. The Net income is each month’s Total Sales minus each month’s Total
Expenses. Note that these values are formatted as Accounting but showing no decimal
places.
Review my formatting, and apply style, cell color, and cell bordering, as appropriate.
See next page for Finished Product.
Document1
20 pts
Sales:
Chicago
Boston
Los Angeles
New York
Expenses:
COGS Expense
Office Expense
Admin Expense
Wage Expense
Other Expense
Depreciation
Total Expenses
Net Income:
Jan
Feb
50445.00
60528.00
60331.00
40793.00
18466.97
19151.04
19844.70
17453.11
Mar
Apr
May
Jun
33166.00
50476.36
51836.07
69505.05
55784.57
54545.57
58168.20
59351.56
55308.52
63804.74
67016.38
57560.46
39589.79
48021.82
58023.56
43577.21

Purchase answer to see full
attachment