Assignment:
A) Excel Test: LOOK BELOW FOR HINTS AND EXAMPLES !! The SSS Grad Council has decided to use a spreadsheet to determine how much it must charge each member attending its annual Grad Dance so that it will not lose money. Create the following spreadsheet, and save it as excel6a.
Below are listed the costs of each item based upon 50 members attending:
Expenses 50 students 100 students 150 Students 200 Students Band $1,500.00 Decorations $185.00 Print tickets $73.15 Electricity $50.00 Advertising $182.00 Clean up $78.00 Appetizers $56.00 Entrees $111.00 Dessert $34.50 Beverages $150.00 Total Cost Cost Per Student 1. Fill in the information in the blank columns - use the following instructions:
- The Cost of the Band through to Clean-up are the SAME no matter how many students attend. (hint: use a formula with cell references in the 100 students column and copy it to the other columns)
- The Cost for the Food will change, depending on how many people attend.
- The cost for 100 people will be equal to 2 * Cost for 50 students.
- The cost for 150 people will be 3 * Cost for 50 students
- The cost for 200 people will be 4 * Cost for 50 students
- Make sure you use formulas with cell references.- In the Total Cost row: use the Sum function to add up the expenses for each column
- In the Cost Per Student row: use a formula to figure out the cost per student (hint: Cost Per Student = Total Cost/ Number of students).
2. Create the following charts:
- A Pie chart that shows how much was spent on each item for when 50 students attend
- highlight the expenses and the amount spent one each item
- choose Insert/Chart
- Choose Pie chart
- Add the Title "Grad: 50 Students"
- Add data labels so that the Names of each expense will appear next to their values
- A Line chart that compares the Total cost for when 50, 100, 150 or 200 students attend
- highlight the row with the titles (50 students, etc.)
- Press and hold control, then highlight the amounts in the Total Row
- Create a Line chart
- Add the Title "Total Expenses"
- Add Data Labels
- A Bar chart that compares how much was spent on each item for when 50, 100, 150 or 200 students attend
- make sure you highlight the expenses, and then all the values in each column
- choose a bar chart
- add the Title Grad Expenses- Save the File as Grads.xls on your U: drive
A chart is a graph of the data in a spreadsheet. Studies show that people only remember 10 % of what they read, and 20 % of what they hear, but 30 % of what they see. For this reason, charts are often used to present the information from a spreadsheet. Charts not only show the information graphically, but they also show the relationships between the data more clearly. When a chart is created, it is linked to the information in the worksheet so that changes in the worksheet will automatically occur in the chart. However, a good chart needs to be carefully planned in order to be useful.
Chart components
A chart has titles, labels, a legend, and at least one data series.
Types of Charts
Before creating a chart, you need to plan what type of chart to make, based on the type of information you have. You should ask yourself the following questions:
The 3 most commonly used chart types are the Pie Chart, Bar Chart and Line Chart
Pie Chart : A pie chart can include only ONE type of data, with each slice representing a value from the series. The size of each slice varies with its percentage of the total. Pie charts are best for charting data that is a percentage of a whole. Ex. How much you spent of your budget on each item.
Bar Chart
A bar chart can include several series of data, with each bar representing a value. The height of the bar is proportional to the value it represents. Bar charts are therefore useful for comparing the differences between values. You can create a bar chart with horizontal or vertical bars. A vertical bar chart is called a Column chart. A bar chart can also be made with different shapes, for example, cylinders, cones or pyramids.
| Bar chart |
Line Chart
A line chart can include several different series of data with each line representing a series. The values in a series are represented by a point on the line. Line charts are useful for displaying the differences of data over time.
Making a Chart: