ASSIGNMENT: You are to create a new blank Excel workbook saving the workbook with the file name as lastname_firstinitial_statement. Organize and enter the information below into a Microsoft 2013 Excel worksheet. Calculate the Total Monthly Budget, Actual, and Variance both horizontally and vertically. You are to then create a Chart for this workbook.
a) Make sure the worksheet view is set to 100%.
b) Verify the "active cell´is selected in the upper left hand corner of the worksheet. This should be cell A1. If not, make this the active cell by positioning the cursor in cell A1 and then clicking the cell.
c) In this cell, A1, type Your Name. Press the Enter key to store this information.
d) In cell A2, type Financial Budget/Income Statement. Press the Enter key to store this information.
(1) Press the Enter key again to skip to cell A4.
e) In cell A4, type Month. Press the Tab key to move the active cell to B4.
(1) Beginning in cell B4, list the 12 months of the year beginning with January and ending with December pressing the Tab key after each month is entered. The active cell should now be N4:
(2) In active cell N4, type Total and press the Enter key.
a. Save your workbook.
b. Make sure the active cell is A5. If not, make cell A5 the active cell.
f) NOTE: When entering number values, the numbers should be formatted to show only the number,No decimals or dollar signs are to be shown. Use Excel to format the numbers to show commas to separate hundreds, thousands of dollars, etc. Formatting for Negative numbers should be bracketed with right and left parentheses.
For this assignment, use the tool bar for "Number"that is located on the Home tab. You can apply the numbering scheme desired either before or after entering the numeric datadetailed below by simply highlighting all of the cells to be formatted and applying the desired editing at one time.
g) In cell A5, type Budget and press the Tab key.
(1) Beginning in cell B5, type 10000, 12000, 9000, 11000, 15000, 15500, 20000, 21500,24500, 25650, 29550, 35500 pressing the Tab key after each entry. The active cell should be in cell N5.
(2) In cell N5, sum cells B5 through M5. Press the Enter key.
(3) Save your workbook after completing this operation.
(4) Make A6 the active cell.
h) In cell A6, type Actual. Press the Tab key to make the active cell B6.
(1) Beginning with cell B6, type 1475, 9590, 11175, 16850, 14900,16750, 23750, 22480, 24350, 26975, 28400,37500pressing the Tab key after each entry. The active cell should be at cell N6.
(2) In cell N6, sum cells B6 through M6. Press the Enter key.
(3) Save your workbook after completing this operation.
(4) Make A7 the active cell.
i) In cell A7, type Variance and press the Tab key.
(1) Beginning in cell B7, calculate the Variance for each month.
(2) Variance equals Actual minus Budget.
(3) Negative numbers should be bracketed with right and left parenthesis symbols.
(4) In cell N7, sum cells B7 through M7. Press the Enter key.
(5) Save your workbook after this operation.
j) Merge & Center the information you entered in cell A1 changing the font to 16.
k) Merge & Center the information you entered in cell A2 changing the font to 14.
l) Highlight cells B4 through N4 changing the text to Bold and centering the names of the month.
m) Highlight cells A4, A5, A6, and A7 changing the text to Bold.
(1) Save your workbook after completing these operations.
(2) Your workbook should look like Figure 1 below.
n) Create a chart by:
(1) Selecting the "INSERT" tab
(2) Highlighting cells B5 through M7
(3) Selecting the "INSERT COLUMNS CHART" from the Charts tool bar
(4) Selecting the "3-D Chart" from the available charts
(5) Save your workbook after completely this operation
o) Submit your completed workbook for this assignment into Blackboard.