Reference no: EM13972724

The U.S. Bureau of the Census compiles a broad array of detailed finance data for the federal government, states, school districts & other local governments, and public pension systems. This multipart tutorial-based exercise teaches you to find and download data on state and local revenues, expenditures, and debt; eliminate columns with unneeded data; combine two data sets into one; and then create worksheet pages on which you will use formulas to adjust each state's finance data by population and personal income. In addition to building your budget analysis capacity, the exercise is designed to reinforce and teach important Excel skills used in budgeting & financial analysis.

Part 1: Obtaining State and Local Government Finance Data

(1) Boot up Internet Explorer or an alternative web browser of your choice.

(2) Click on the link below or type into your browser the URL for the most currently available state and local finances data (2013): https://www.census.gov/govs/local/

The downloadable data is provided as two separate files, due to the large file sizes. The first file covers the United States totals and the states Alabama through Mississippi and the second covers the states Missouri through Wyoming.

(3) Click on the underlined file description: "US Summary & Alabama - Mississippi." A file download query like the one shown below will appear.

(4) Click on "Save as", which should bring up a directory from your computer.

(5) Select a location to save the file.

(6) Save the file with the default Census Bureau file name (13slsstab1a.xls).

(7) Save the file a second time as state- local_finance_data_2013_yourlastname.

Part 2: Preparing the 1st Part of the Database: Deleting Unwanted Data & Consolidating Files

(1) If you have exited the program, boot Excel.

(2) Click File, and then click "Open".

(3) The state-local finances file should appear as a choice. Click on it to load the file. (Or use file, open, browse, and select the file.)
Because the file was downloaded from the Web, Excel will open the file in "protected view" mode, which lets you see the file but does not let you do any work. You will see an "enable editing" message at the top of the worksheet page (see next page for a screen shot).

(4) Click on the "enable editing" button to get into the spreadsheet.

Before we can work efficiently with the file, we need to turn off the "freeze panes" feature. Frozen columns and/or rows permit column headings to be visible as you scroll down a page, and row labels to be visible as you scroll across the page.

5) In Excel 2013 for Windows, you access the "freeze panes" option by clicking on the View tab. In Excel for Mac, this option is
available via the "Layout" tab.

º In Excel 2013, click on "Freeze Panes" to display the drop down menu shown at right, and then click on "Unfreeze panes.
º If you are using a Mac, click on the freeze pane button shown below boxed in blue, and then select "unfreeze."

6) Now you are ready to delete the columns we won't be using. You will be deleting all columns except those labeled as "1" AND you will delete all columns for the District of Columbia.

7) To delete the columns, click on the number 2 under United States Total, then scroll across and select 3, 4 and 5. YOU DO NOT NEED TO CLICK ON CELLS AFTER THE FIRST SELECTION; JUST SCROLLING OVER THEM WILL INCLUDE THEM IN THE SELECTION.

8) Next, press the CTRL key. This will let you select more cells that are not adjacent to your initial choice. NOTE: When using a Mac, use the Command key.

9) While holding the CTRL key, click on the number 2 under Alabama, then scroll across to select 3, 4, and 5.

10) Continue holding the CTRL key and move to the next state, Alaska. Click on the number 2, and then roll your mouse across 3, 4, and 5 to select these columns.
• You could continue with a few more states, but if you accidentally pick up a column
#1 or select a row other than the one where the columns numbers appear, you will need to start over. So let's delete the columns we've selected at this point.

11) Release the CTRL key (Command key in Mac Excel.) The selected cells will remain selected as long as you do not click on the spreadsheet.

12) Now you want to have your mouse hover over the selected cells. Don't press the left mouse key, just hold the mouse so it is positioned over the cell selection. You will see the cross shown below boxed in blue.

13) Click the right mouse key, which will bring up a menu that includes the option to delete rows or columns, as shown at right.

14) Click on "Delete", which will bring up the "Delete" dialog shown on the next page.

15) Select "Entire column" as shown at right, then click OK.

16) Click on the number 2 under Arizona, and then scroll across to pick up 3, 4, and 5. Press the Control key (command on Mac), and then select 2, 3, 4, and 5 for Arkansas, and then 2, 3, 4, and 5 for California.

17) As you did earlier to delete columns: without releasing the control key, right click to bring up the menu, click on "delete", and then click on "entire column."

18) Click on the number 2 under Colorado, drag your mouse over 3, 4, and 5 to select them, and then depress CTRL. Click on 2 under Connecticut, select 3, 4, and 5, press CTRL, and repeat the process for Delaware, stopping when you get to the District of Columbia.

19) Delete the unwanted cells for Colorado, Connecticut, and Delaware.

20) For the District of Columbia, select columns 1 through 5 and delete these columns. (We do not want DC included with states and local governments.) Here is what you will see after deleting DC:

If everything looks good, now is a good time to save the file.

21) Save the file.

22) At this point I usually create a backup file so I can go back to this point if I mess up later. (Always do this if you use Excel's sort feature because it is really easy to mess up on sorts.) I do this by adding "BU" to the file name and then reloading the working file (the state-local finances file in progress).

Now you are ready to delete some more rows, beginning with Florida.

23) Click on 2 under Florida, and then select 3, 4, and 5. Press Control, and then pick up 2-5 for a couple more states. Delete the unwanted columns as you did earlier.

24) Repeat until you have only the number 1 column for each state.

Check: When you have finished deleting all the columns, Mississippi should appear in column AA.
• If Mississippi is located in a column to the right of AA, scroll back over to find columns you missed deleting, and delete them now.
• If MS is located in a column to the left of AA, you accidentally deleted too many columns.
Fixing this problem is trickier. Figure out where you went wrong. Assuming you accidentally deleted data for one state (or a couple), here is what I would do.

º Insert one column where the state-local data for the missing state will go. Go back into the original Census file and pick up just the state-local data, which is in the column labeled as "1". Paste it into your working file.

If your errors are more extensive, figure out where you went wrong and then delete all the columns to the right of that point. Go back to the original file and copy all the columns beginning with the first state you need. So for example, if you messed up on Indiana, this is where you would begin the copying. Paste the copied data into your working file, and then go back through and delete columns 2-5 for each state. (And also delete all data for DC.)

When everything is all set:

25) Save the file and also save to a new backup file. Note: You can never have too many backup files. I use "Take1", Take2, etc. to keep backups in order.

26) Keep the file open if you will continue working with it soon, otherwise Exit from Excel.

PART 3: Preparing the 2nd Part of the Finances Database: Deleting Unwanted Data & Consolidating Files

You now are ready to work with the file for the second half of the states. Doing this after you did the first half of the states will be much easier because you know ahead of time what you'll be doing and how. But again, don't try to work too quickly!

The step-by-step through this section is abbreviated, since you just did the first half of the states. If you need more guidance than provided here, go back to earlier sections where the processes are explained in more detail.

1) Click File, then click Open. Locate the "13slsstab1b" Census file, click on the file name to select it, and then click Open. You will need to enable editing.

2) Save the file using the Census Bureau name.

3) Turn off the "freeze pane" feature.

4) You will see that this file begins with Missouri and is set up the same way as the first file.

5) Under Missouri, highlight the cells labeled 2 through 5, then press the Control key. Next, with the CTRL key still depressed, highlight cells 2-5 for Montana, and then highlight cells 2-5 for Nebraska.

6) With your cursor hovering over a selected cell, right click to bring up the menu, then click "Delete."

7) When the Delete dialog appears, click next to "Entire column" and then click OK.

8) Once you have deleted the unwanted columns, scroll left to get back to Nevada, the state you will work on next.

9) Repeat the deletion procedure for the remaining states.
• After you delete the columns for the last few states, you will see a blank spreadsheet. Just scroll left to get back to the data.

10) When you have deleted columns 2 through 5 for each state, Wyoming will appear in Column AB.
• If Wyoming is located in a column to the right of AB, scroll left to find columns you did not delete. Look at the numbers at the tops of columns; you only want to see the number 1.
• If Wyoming is located in a column to the left of AB, you accidentally deleted too many columns. See the instructions in Part 2 for how to correct this error.

11) When you are satisfied with the data, save your work & stretch.

Part 4: Combining Spreadsheets

Now you will copy the finance data for the second half of the states and paste it into the file for the first half of the states.

1) Beginning in Cell C10 of the "Pt2" data set, which is where the name "Missouri" appears, select the block of cells beginning with C10 and then over and down through Cell AB188.

2) With your mouse hovering over the selected data (you will see the cross symbol), right click and then click on copy to place the data on the clipboard.

3) Switch to the first notebook, click on Cell AC10, and then click on "Paste". The data for the second set of states will be pasted into the notebook.

4) Let's get rid of column A, which is not needed. (It is where there are typed in row numbers.) Click on cell A15, right click, select "delete", and the Delete dialog appears select "Entire Column" and then click OK.

5) Click on File, then click on "Save as", click on the arrow to the right of "Save as type:", select "Excel Workbook", and then click on "Save."

You have updated the file to the current format. You will receive the following warning from Excel 2013, due to a change in naming requirements in Excel 2013 that is not backward compatible:

6) Click on "OK to All". This change will not take effect until you close and reopen the program, so you will continue to get error messages through the next two steps.

7) Double click on the tab labelled 2013_US_MS and change the name to 2013- Amounts.

8) Save the file. You will get the warning message again, so click "OK to All" again.

9) Reopen Excel and then reopen the file. You won't get the warning message again.

You may have noticed when you pasted in the second data set that there were a bunch of hatch marks. This occurs because is not enough space to display some numbers.
You can adjust the width of one or two columns by clicking on the separator between two columns, and then dragging the separator bar to widen the column.

10) Click on the Column letter for Missouri, which is "AB", and then roll your mouse across the column letters through Wyoming in BA.

11) With your mouse hovering over the selection, right click. The menu shown at right should appear.
• If you have selected the columns by clicking on anything other than the letters that head them, the menu will not include the choice "Column Width." If you get the wrong menu, position your mouse over the letters and scroll across to select.

12) Click on Column Width. Per the note with #11, the "column width" option only appears on the menu when one or more columns is selected.

13) When the column width dialog appears, click in the column width window and type 12, then click OK. If you still see hatch marks, increase the width to 14.

14) Save the file.

Part 5: Cleaning Up the Worksheet

1) Let's get rid of some of the extraneous rows under the state names. In Column A, click on "Description."
• This cell actually is several cells merged together.

2) Right click, and then click on "Format Cells."

3) When the dialog appears, click on the Alignment tab, uncheck "Merge cells" and "Wrap text", and then click OK. "Description" will now appear in cell A10.

EXCEL TIP: If you want to merge cells, scroll across the cells to merge, right click to get to the format cells dialog, and then click next to "Merge cells." If your title will need more than one row, also click next to "Wrap text." (You may need to adjust row height to see the extra lines of data or text.)

4) Select cells A11 through A14. Right click to bring up the menu and then click Delete. When the Delete dialog appears, click next to "Entire row", and then click OK. Your file now should look like the screen shot below.

5) Save the file and also save a backup.

6) If things have been going good and you are satisfied with this file, you can delete earlier backup files now.

Part 6: Bringing in Some Data To Adjust Finances & Aid Analysis

Comparing amounts directly is impossible because states differ greatly in size. However, once the data is adjusted by a relevant variable, we can make comparisons. So you now will bring in some data on states' populations and personal income levels. We will use these amounts to adjust states' amounts to per capita amounts and percentages of personal income.

You'll begin by inserting two rows where you will paste the population and personal income data.

1) Click in Cell A11 and then roll your mouse down to also select row 12.

2) Right click and then click "Insert."

3) When the Insert dialog appears, click next to "Entire row", then click OK.

4) You will see the two extra rows; they will be selected.

Now you will obtain the population and personal income data.
5) Retrieve from Blackboard and open the Excel file called "population-personal-income- 2013.xlsx". When the file opens, click on "Enable Editing".

You will copy the state population and personal income data from this file and paste it into the state-local finances data set. Notice, however, that the states are listed in rows in the population data set, whereas they appear in columns in the state-local finances data set. You will need to transpose the data so the block of data is arrayed in rows.

6) Highlight the population and personal income headings and the data in cells B1 through
C52. (You will not be copying the state names in column A.)

7) Click on the Copy icon to place this data on the clipboard.

8) Switch to the state and local finance data set.

9) In the state local finances data file, click on Cell A11 (which is empty), right click, and when the menu appears click on the words "Paste Special."

10) When the paste special dialog appears, click in the box to select "Transpose" and then click OK.

11) VERY IMPORTANT: CHECK THE LOCATIONS OF KEY DATA.

12) When everything is all set, save a backup file, save your [working] file, and then take a stretch break.

Part 7: Setting Up Worksheets for Population and Income Adjusted

Finances

Now you will create two new worksheets. On the first you will adjust the state-local finance data to per capita values by dividing by population. On the second worksheet, you will express the amounts as percentages of personal income. The easiest way to prepare the new worksheets is to begin with the existing worksheet.

1) Click on the page tab 2013-Amounts, right click to bring up the menu shown at right, and then click on "Move or Copy", which will bring up the move-copy dialog.

2) When the dialog appears, set it up like the one shown on the next page.

3) Click OK. You will now see 2 tabs:

4) Double click on the 2013-Amounts (2) tab, delete the word "Amounts", and type PC (for per capita). The tab now will read "2013-PC."

On this new page, you will delete all the finance data, but not the labeling or population and personal income data.

5) Click on the tab labeled 2013-PC, click on cell B13 (which should show the amount of Revenue for the US Total), then drag your mouse over through Wyoming and down to the bottom of the data in cell AZ186, and then press the delete key.

6) Create a duplicate worksheet page by right clicking on the 2013-PC page tab to bring up the menu, clicking on "Move or Copy", and then setting up the dialog so the current page will be copied and new page will be added at the end of the file. See the earlier screen shots if you need help doing this.

7) Double click on the 2013-PC (2) tab, delete the letters "PC", and type PY (for personal income). The tab now will read "2013-PY".

8) Save the file.

You now have the worksheets ready to adjust the finance data for population and personal income. You will write formulas that reference the finance amounts on the first worksheet page and the population and personal income data respectively.

Part 8: Developing the Per Capita Finances Page
1) Click on the tab "2013-PC", and then click on Cell B13 to select it (as shown above in the screen shot). To create the first per capita amount, in Cell B13 type the following formula:

='2013-Amounts'!B13/('2013-PC'!B$12/1000)

2) Hit "Enter" to record the formula. Your result should be $10,772 per capita. This result tells us that for the US as a whole, states and local governments collect revenue of $10,772 per person. (You'll learn more about the contents of this data over the next couple of course weeks.)

3) Format the result as currency, with no decimal places.

º To do this, have the cell you want to format selected, click on the "Home" tab (in Excel 2013), then click on the arrow next to the quick number format window, shown below boxed in red. Click on "Currency" to select this format.

Next, use the decrease decimals button to reduce the number of decimals to zero.

If you would like to use a different font, format the result in cell B13 as you'd like it to appear.

Now you will copy the formula down the column, but only to cells where there is data on the amounts page.

4) Click on Cell B13, where you just created the per capita amount. Copy this formula to the clipboard.

5) Click on Cell B15, then press CTRL. Now click on cell B17 and slide the mouse to select B18 through B20, then select B22 through B31. Continue selecting cells in the B column that have labels in Column A. When you get to the bottom and have selected cell B186, click on the paste icon to copy the formula to these cells.

The data will have filled in. There will be some labeled rows that have zeros opposite them.

These had no data on the amounts page.

6) Delete zeros that are opposite section headings. If in doubt, check the amounts page to see whether there is data there.

Now you are all set to copy the formulas to the rest of the spreadsheet.

7) Select all the cells from B13 through B186, and then click on the copy icon to place these

8) Click on cell C13, then scroll across to select D13 through AZ13.

9) Scroll across the column letter headings from C through AZ, right click, select "column width" and change the column width to 12. If you still see just hatch in many cells, make the column wider.

On the next page, see a screen shot of part of the filled in worksheet.

(10) SAVE YOUR WORK.

Part 9: Developing the Percent of Personal Income Finances Page

Now you are ready to create the percent of personal income data.

1) Copy the formula from Cell B13 on the 2013_PC worksheet page.

2) Click on the tab for 2013_PY, place your cursor in cell B13, and paste the formula. This is not the right formula, but it is easier to correct this one than to start from scratch.

3) Double click on cell B13 and change the formula so it reads as follows, and then press Enter.
='2013-Amounts'!B13/('2013-PY'!B$11)
Note: Because the personal income data is in thousands, we can use it directly (rather than dividing by 1000 as we did for the population-based adjustment).

Your result will be zero, because it needs to be formatted.

4) Format the result as a percentage with one decimal place. Now you will see 24.1%. This result tells us that for the US as a whole, states and local governments receive revenue of that is equivalent to 24.1% of personal income.

5) Once you have Cell B13 displaying correctly, copy the formula to the clipboard.

6) Click on Cell B14, press CTRL, select all the cells in Column B that are opposite labels in Column A, and then paste the formulas.

7) After you paste, go through and delete zeros that represent no data on the amounts worksheet, just like you did when creating per capita amounts.

8) Copy the column of cells B13 through B186 to the clipboard.

9) Click on Cell C13, scroll over with your mouse to select D13 through AZ13, and then click Paste.

Attachment:- Assignment.rar