• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

MS Excel 2010 trend template

Hello everyone,

I'll try to be as descriptive as possible, but please feel free to ask any questions if there's anything that is unclear.

I have a small business with 20 month worth of daily sales data.  I hoping to find an excel sheet template, where I can plug in my daily sales, and get trend and/or forcasting graphs based on the entered data.

I'm also hoping to the excel sheet to be dynamic or flexabile enough for me, for example, find out what's our average sales for Mondays, Tuesday, ext.

Please let me know if you have any questions, and thank you so much in advance
0
bloodywicked
Asked:
bloodywicked
2 Solutions
 
Elton PascuaCommented:
If you're looking for flexibility, I would say, go for Pivot Tables. I analyze customer CSAT data and sometimes I have to look into individual question scores, customer regions, type of product, etc and I found Pivot Tables to be very flexible at this. You can also create a pivot chart to visually analyze data.
0
 
Patrick MatthewsCommented:
In the attached document, I created some random data crafted to show some cyclical effects (such as busy and slow months, and busy and slow weekdays), as well as a gentle upward trend in sales over time.

Q-27828676.xlsm

From that source data, I created a PivotTable and PivotChart.  (Once the PT was created, I grouped the date field to show days, months, and years.)

This approach, as techfanatic notes, will allow you to pursue the data at any desired level of aggregation.  (If you want to analyze weekdays, just modify the PT definition to include that field.)

I also included code to automatically refresh your PivotTable and PivotChart, as explained in my article http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html

Note that forecasting is a very broad and very complicated topic, and can mean different things t different people.  You will have to account for many different variables, such as (but not limited to):

Seasonality
Day of week
Holidays
Variations in marketing/advertising
Changes in product mix
Changes in operating hours/store environment
Changes in the neighborhood around the store
etc.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now