Charting and Importing data

1. Open your Budget Template Excel document.

Copy data from 2 columns (and the dates) that have a lot of entries in the spreadsheet (e.g. “Total Food” and “Eating Out”) below the table.

2. Create a Line Graph with this data (see the chart sample below)

3. Add a Title to the chart

4. Put a labelled legend at the bottom of the chart

5. Have data values for each line in the chart

6. Write a paragraph that gives an analysis of the chart (does it correlate?)

7. Open your PCDB, insure there is data for many different dates (make up data if there is not enough)

8. Create a Query for each store and one product that shows it’s price over time (Date Price, Store, Product Name) {3 * 20 points) For example, the three queries may be “Harry Potter” for “Amazon”, “Harry Potter” for “Target” and “Harry Potter” for “Walmart”

9. Import the three data tables to a new Excel workbook (each on a separate worksheet) (3 * 5 points)

10. Create a new worksheet called “Price Trend” that has columns for the date, and the price of the item at each store. Use relative references to populate the columns with data (i.e. you should be able to change the value on one of the worksheets and the value is updated on this worksheet.). If one or more stores is missing prices for a date, add the date and a price to that worksheet.

11. Create a line chart showing how the prices have changed at each store. If there are lines that are flat, change some price values on the appropriate worksheet.

12. Create queries for the other products

13. Create workbooks with worksheets for each of the other two products

14. Create line charts showing the price trends of the products.

15. Good formatting on each chart, axes, etc. (see the chart sample below)

16. For each line chart, write a paragraph in a textbox that analyzes the price trends.

Answer the following questions:
What does the information in the chart mean?
Which store changes its prices more often?
Is there is store that always has the lowest price?
What other interesting comments can you make about the trends? You need to show a thoughtful, complete analysis and effort.