Open the downloaded workbook named Test 2 pt 1 start.xlsx and save it as Test 2 pt 1_LastFirst.xls
business
Description
BISM200 Test 2-1 pt 1
Project Description:
Compiling monthly Income Statements.
Steps to
Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Open the downloaded workbook named Test 2 pt 1 start.xlsx and save it as Test 2 pt 1_LastFirst.xls |
0 |
|
2 |
Import Income Statement Jan.csv,
removing the first two columns. Ensure that the Use First Row as Header
option is used, if applicable. Also if the import results in a Table, convert
the table to a range. Name the new sheet Jan |
1 |
|
3 |
On a new tab, import Income Statement Feb.csv, using the
same method above. Name the new sheet Feb
Perform the same actions for Mar and Apr files. |
3 |
|
4 |
Group Jan, Feb, Mar, and Apr.
Clear Format only. Copy field A1 thru A21 of the Income Statement to Column A into the grouped worksheets.
Group all worksheets including Income Statement. Apply Title format to A1,A2, and A3.
Apply Heading
1 format to A5, A11, and A21. Format numeric fields to Number
format with 2 decimals and comma separator. Format Column Width for A, B, and
C to a value of 25. |
2.5 |
|
5 |
On the Income Statement Sheet, insert a hyperlink at B27
to
hardware.ca site. Use Financial
Breakdown as the label on the spreadsheet. Add a ScreenTip stating
YTD
Financial Data (no period). |
3 |
|
6 |
In cell C6 in the Income
Statement sheet, enter a 3-D formula to sum the monthly amount from each of
the Jan Feb Mar and Apr sheets. Fill results for all data fields on the
Income Statement sheet. |
2 |
|
7 |
In cell C23, use a 3D formula to sum the Net Income from
each of Jan Feb and Mar worksheets. |
1 |
|
8 |
Enter a formula in C25 to return
the average sales per month for Jan Feb Mar and Apr. You may get a divide by
zero message for the cell, if the formula is correct, it will count. |
1 |
|
9 |
Apply Bold format to C10, C19, and C21 on the Income
Statement worksheet. |
1 |
|
10 |
Save and submit your final
results for grading. |
0 |
|
Total Points |
14.5 |
