MS Excel 2010 trend template

Posted on 2012-08-14
Last Modified: 2013-08-04
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
Question by:bloodywicked
    LVL 8

    Assisted Solution

    by:Elton Pascua
    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.
    LVL 92

    Accepted Solution

    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.


    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

    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):

    Day of week
    Variations in marketing/advertising
    Changes in product mix
    Changes in operating hours/store environment
    Changes in the neighborhood around the store

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now