Creating Charts in Excel: A Step-by-Step Guide, Study notes of Design

Download Creating Charts in Excel: A Step-by-Step Guide and more Study notes Design in PDF only on Docsity! CREATING CHARTS IN EXCEL CREATING CHARTS IN EXCEL CREATING THE PRESENTATION CHARTS IN MICROSOFT EXCEL 1 | P a g e Revised: 10/25/19 CREATING CHARTS IN EXCEL Creating charts in excel is fairly easy. Excel can help you choose the best chart to represent your data. With some practice, you can learn to modify charts and display your data in an eye-catching way. To use this tutorial, you will need data to work with. Recommend entering your organization’s audited financial statement data into the document. Otherwise, sample data available in Table 1: Example Data and Table 2: Table Formulas can be found at the end of this booklet. CREATING A COLUMN CHART Both the Bar and the Column charts display data using rectangular bars where the length of the bar is proportional to the data value. Both are used to compare two or more values. However, their difference lies in their orientation. A Bar chart is oriented horizontally whereas the Column chart is oriented vertically. Let’s make a simple Column Chart. CHARTING REVENUES: CHART THE TOTAL REVENUE FOR 2012-2017 IN A COLUMN CHART. 1. Select the data to be displayed in the table. 2. In the navigation pane, select INSERT > Recommended Charts from the Charts menu. Recommended charts makes it simple to select a chart that bests represents the data selected. Make sure that you select a chart that seems like it will make the most sense. This is just one way in excel to make a chart. 4 | P a g e Revised: 10/25/19 CREATING A COMBINATION CHART A combination chart is a chart that combines two or more chart types in a single chart. The combination chart displays the data sing a number of bars and/or lines, each of which represent a particular category. CHARTING MONTHLY OPERATIONAL COST AND FUND BALANCES CREATE A CHART THE THAT SHOWS THE ANNUAL DISTRICT OPERATIONAL COST AS A COLUMN CHART AND THE NUMBER OF MONTHS OF SUPPORTING FUND BALANCE AS A LINE CHART FOR 2012-2017. 1. Select the data you want to chart. 2. From INSERT > Recommended Charts, select All Charts > Combo. Chose the chart type you want to display for each data series. In this example, choose Clustered Column for “Monthly Cost…” and Line for “# of months Fund Balance…”. Select Secondary Axis for the line chart type. Click OK. Now you can see the $’s on the Left Axis and the #’s on the right. 5 | P a g e Revised: 10/25/19 EDITING CHARTS Excel is a great tool. There are many options available to format your charts so that they look exactly how you want them to. Remember these mottos: 1.) if it seems like something you should be able to do, you probably can, and 2.) If you can’t figure it out…GOOGLE IT. Google usually spits out the answer of how to change something. For the sake of these examples, I’m going to show you the most basic ways to edit the look of your charts. EDITING TITLES, AXIS AND LEGENDS. EDIT THE COMBO CHART YOU JUST CREATED TO ADD A CHART TITLE, ADJUST THE LEGEND AND MAKE THE TEXT LARGER 1. Select the combo chart you created in the example above. From the Ribbon, navigate to CHART TOOLS > DESIGN. 2. Select the dropdown menu arrow next to show the Quick Layout options available. This will display all of the ways that you can quickly adjust the chart elements such as the legend, title, and axis. For this example, let’s choose the layout which displays the information in a tabular legend. Now the data is displayed as a graph, but the key points are also summarized in a table below the graph. Best of both worlds. 6 | P a g e Revised: 10/25/19 3. Now let’s format the chart and axis titles. Select the “Chart Title” text box. Click inside the text box again to edit the text and type in a title name. 4. Once completed, right-click the selected text box and select Format Chart Title. 5. A formatting menu will pop-up on the right hand side of the screen. You can now see the many options available to recolor, resize, realign or add borders and shape to your chart title. 6. Repeat the process to add a name to the left axis title. Change the formatting of the text using either the Format Chart tool or by making edits from the tools located in the ribbon, similar to any Microsoft Word document. It works from either tool. You can see below, that I have made a number of edits to the original chart to make it more eye-catching for my use in a report. Table 1: Example Data BALANCE SHEET 2012 2013 2014 2015 2016 2017 Assets Cash and Investments $236,291 $365,448 $277,421 $218,443 $630,546 $694,405 Due from Other Governments $40,658 $14,521 $18,812 $254,891 $87,695 $42,898 Accounts Receivable $2,299 $1,980 $8,399 Accrued Interest $274 Inventory $4,000 $4,000 $4,000 $4,000 $3,000 $913 Total Assets $281,223 $383,969 $300,233 $479,633 $723,221 $746,615 Liabilities Accounts Payable $763 $920 $1,215 $1,692 $2,690 $10,546 Salaries Payable $5,214 $5,279 $7,453 $9,571 $14,672 $17,100 Deposit on Sales $2,688 $1,695 $1,035 $3,120 $19,487 $25,948 Due to Other Governments $3,377 Unearned Revenue $215,196 $304,998 $192,408 $258,110 $449,206 $417,796 Total Liabilities $223,861 $312,892 $202,111 $272,493 $486,055 $474,767 Fund Balance Nonspendable - Inventory $4,000 $4,000 $4,000 $4,000 $3,000 $913 Assigned - Compensated Absences $8,262 $12,821 $10,857 $16,105 $18,559 $19,879 Unassigned $45,100 $54,256 $83,265 $187,035 $215,607 $251,056 Total Fund Balance $57,362 $71,077 $98,122 $207,140 $237,166 $271,848 Total Liabilities and Fund Balance $281,223 $383,969 $300,233 $479,633 $723,221 $746,615 Unrestricted Fund Balance $45,100 $54,256 $83,265 $187,035 $215,607 $251,056 Annual Cost of District Operations $338,394 $356,708 $329,434 $391,713 $520,464 $620,392 Monthly Cost of District Operations $28,200 $29,726 $27,453 $32,643 $43,372 $51,699 # of Months Fund Balance will Support District Operations 1.6 1.8 3.0 5.7 5.0 4.9 1 | P a g e Revised: 10/25/19 PROFIT AND LOSS STATEMENT 2012 2013 2014 2015 2016 2017 Revenues Intergovernmental County $189,902 $191,628 $194,236 $208,482 $195,159 $249,877 Federal $4,445 $9,640 $19,364 $25,960 State Grant $255,355 $188,447 $225,831 $723,104 $1,026,294 $1,010,187 Total Intergovernmental $449,702 $389,715 $439,431 $957,546 $1,221,453 $1,260,064 Charges for Services $31,392 $28,833 $28,324 $36,243 $45,956 $47,841 Misc. - Interest Earnings $877 $235 $258 $292 $307 $141 Misc. - Other $8,708 $4,969 $5,603 $5,116 $10,187 $6,490 Total Miscellaneous $9,585 $5,204 $5,861 $5,408 $10,494 $6,631 Total Revenues $490,679 $423,752 $473,616 $999,197 $1,277,903 $1,314,536 Expenditures District Operations Personnel Services $290,676 $315,683 $280,757 $319,564 $434,141 $516,286 Other Services and Charges $46,468 $41,025 $46,765 $72,149 $81,024 $70,996 Supplies $224 Capital Outlay-depr. $1,250 $0 $1,688 $0 $5,299 $33,110 Total District Operations $338,394 $356,708 $329,434 $391,713 $520,464 $620,392 Project Expenditures District $22,409 $20,718 $22,419 $2,849 $27,539 County $1,407 $3,010 $16,597 Federal $2,126 State $138,679 $32,611 $91,185 $495,456 $724,564 $615,326 Total Project Expenditures $161,088 $53,329 $117,137 $498,466 $727,413 $659,462 Total Expenditures $499,482 $410,037 $446,571 $890,179 $1,247,877 $1,279,854 Excess of Revenues Over (Under) Expenditures -$8,803 $13,715 $27,045 $109,018 $30,026 $34,682 Table 2: Table Formulas List of Formulas = Calculated Fields Total Assets =SUM(Cash and Investments + Due from Other Govts + Accts Receivable + Accrued Int. + Inventory) Total Liabilities =SUM(Accts Pay. + Salaries Pay. + Dep on Sales + Due to Other Govt + Unearned Revenues) Nonspendable – Inventory = (Assets:Inventory) Unassigned = SUM(Total Assets – Total Liabilities – Nonspend. Inventory – Assigned Com. Absences.) Total Fund Balance = SUM(Nonspendable Inventory – Assigned Comp. Absences. – Unassigned) Total Liabilities and Fund Balance = SUM(Total Liabilities + Total Fund Balance) Total Intergovernmental = SUM(County + Federal + State Grant) Total Miscellaneous = SUM(Misc Interest Earnings + Misc. Others) Total Revenues = SUM(Total Intergovernmental + Charges for Services + Total Miscellaneous) Total District Operations = SUM(Personnel Services + Other Services and Charges + Supplies + Capital Outlay-depr.) Total Project Expenditures = SUM(District + County + Federal + State) Total Expenditures = SUM(Total District Operations + Total Project Expenditures) Excess of Revenues Over (Under) Expenditures = SUM(Total Revenues – Total Expenditures) Unrestricted Fund Balance = SUM(Fund Balance:Unassigned) Annual Cost of District Operations = SUM(Total District Operations) Monthly Cost of District Operations = SUM(Annual Cost of District Operations / 12) # of Months of Fund Balance = SUM(Unrestricted Fund Balance / Monthly Cost of District Operations)