Dennis wright
serjoo331
kelloggs_graph.xlsxKellogg Example
| Yr & Qtr | Kellogg's Sales | Vs Prev Qtr (Seasonality) | Vs YAG (Trend) | ||||
| 2013 1 | $3,861 | ||||||
| 2013 2 | $3,714 | -4% | |||||
| 2013 3 | $3,716 | 0% | |||||
| 2013 4 | $3,501 | -6% | |||||
| 2014 1 | $3,742 | 7% | -3% | ||||
| 2014 2 | $3,685 | -2% | |||||
| 2014 3 | $3,639 | -1% | |||||
| 2014 4 | $3,514 | -3% | |||||
| 2015 1 | $3,556 | 1% | -5% | ||||
| 2015 2 | $3,498 | -2% | |||||
| 2015 3 | $3,329 | -5% | |||||
| 2015 4 | $3,142 | -6% | |||||
| 2016 1 | $3,395 | 8% | -5% | ||||
| 2016 2 | $3,268 | -4% | |||||
| 2016 3 | $3,254 | -0% | |||||
| 2016 4 | $3,097 | -5% | |||||
| 2017 1 | +8;+1;+7 | -5% | |||||
| +6 | |||||||
| Avg | $3,254 | $3,283 | $3,225 | ||||
| Look at OVERALL TREND From Same Qtr Year to Year or Year Ago (YAG) | |||||||
| Seasonality = Which Qtrs always go up OR Always Drop, does our Forecast Qtr show an Up or Down vs prior Qtr? | |||||||
| Look up history via: | http://www.nasdaq.com/symbol/k/revenue-eps | ||||||
| insert Stock Symbol where K is Kellogg's (MCK, SONC, LULU) | |||||||
| Prof S says | Actual | Fe | |||||
| 2015 4 | $3,150 | $3,142 | 0.25% | ||||
| 2017-1 | $3,254 | ||||||
Fcst Homework Explained
| I | Purpose | The purpose of this assignment is to give each of us the "thrill" (anxiety??) of making | |||||||
| a Sales Forecast for one of the 3 Co's below. | |||||||||
| You need to Forecast Net Sales $ (ie "Revenue") for a Fiscal Quarter. This will be the Quarter | |||||||||
| for which these Co's will announce Sales and Earnings to Wall Street the week of Mar 27: | |||||||||
| Company | Stk Symbol | Qtr Ends | Reporting Dt | ||||||
| McCormick | MKC | Feb 28, 2017 | Mar 28 | Might report Mon Night!! | |||||
| Sonic | SONC | Feb 28, 2017 | Mar 28 | ||||||
| Lululemon | LULU | Jan 31, 2017 | Mar 29 | ||||||
| II | Why | Everything in Business starts with "Sales" eg, the Income Statement starts with Sales, ie Revenue | |||||||
| If we don't think the Product will Sell we shouldn't Market it, Make it, Move it. | |||||||||
| Most Log Orgs have their own Forecasting Dept b/c Marketing and Sales stink at it! | |||||||||
| If you do a great job on the drill then perhaps you should be a "Demand Planner". If you stink at | |||||||||
| it then change your Major to Marketing. | |||||||||
| III | POV | My Point of View--it's impossible to make a "perfect" forecast. Forecasts are always wrong | |||||||
| but they'll be less wrong if we use HISTORY to make forecasts (Log) vs emotion (Mktg/Sales) | |||||||||
| IV | Drill | Our Forecast Drill involves making a Sales $ (ie Revenue) Forecast for the most recently | |||||||
| completed Fiscal Quarter for ONE of the 3 Companies. | |||||||||
| I realize that MOST OF US have had no formal training on Forecasting | |||||||||
| but I've done an Analysis of Kellogg's to show you how I approach Forecasting. | |||||||||
| I think you'll find there's a little bit of Anxiety with this drill b/c you just don't know | |||||||||
| if your number is going to be "good" or "bad". This I think will make you appreciate | |||||||||
| those that have to make forecasts as part of their job (Sales, Marketing and Log) | |||||||||
| and those of us who have to Adjust Production (Log and Plants) b/c the Forecast | |||||||||
| was waaaaay off. | |||||||||
| V | Reco | You can use any Forecasting Method that you want--including using a number that | |||||||
| you dreampt about recently--it's up to you. | |||||||||
| I'm doing this Drill too and here's my Reco (Recommended Approach) | |||||||||
| 1) | Do % Analysis for SEASONALITY AND TREND per the Kellogg's Drill | % Change: | |||||||
| 2) | GRAPH the Last 12 Quarters of Revenue (Sales) History | Most Recent $/Prev $ -1 | |||||||
| 3) | Read relevant articles that might relate to the Co's Sales Trends | ||||||||
| Look up history via: | http://www.nasdaq.com/symbol/mkc/revenue-eps | For McCormick (mkc) | |||||||
| insert Stock Symbol for the Company your pick where the mkc is | |||||||||
| VI | Sum | Summary of all my verbiage is YOU'LL need to look at the DATA to see if there | |||||||
| are any patterns that can help you in making YOUR FORECAST. This doesn't need | |||||||||
| to be sophisticated--simple % relationships will suffice. I'm ok if you round to | |||||||||
| nearest Million eg a forecast of $3,594,250,135 = $3,594. | |||||||||
| VII | Grading | Fcst $/Actual $-1 = % Forecast Error = Fe % | |||||||
| Grading Scale for Fcst Homework | |||||||||
| Example: Student | Fe % | Pts | Other | ||||||
| Forecast = | $3,240 | +/- 0% to 1% | 6 | Also, students w/less Fe than | |||||
| Actual = | $3,245 | +/- 1% to 3% | 5 | Prof will get +1 pt | |||||
| Fe = | -0.2% | +/- 3% to 6% | 4 | ||||||
| = | 6 Pts | +/- 6% to 9% | 3 | ||||||
| +/- 9% to 12% | 2 | ||||||||
| Example: Prof | +/- 12% to 15% | 1 | |||||||
| Forecast = | $3,254 | > +/- 15% | 0 | = Change Major to Marketing | |||||
| Actual = | $3,245 | b/c they can't forecast at all!! | |||||||
| Fe = | 0.3% | ||||||||
| Student = -.5% = +1 Pt | |||||||||
| Total Student = 7 pts | |||||||||
Data only with Prof's Fcst Calc
| Yr & Qtr | Kellogg's Sales | % vs Prior Qtr SEASONALITY | % vs YAG TREND | |
| 2013 1 | $3,861 | |||
| 2013 2 | $3,714 | -4% | ||
| 2013 3 | $3,716 | 0% | ||
| 2013 4 | $3,501 | -6% | ||
| 2014 1 | $3,742 | 7% | -3% | |
| 2014 2 | $3,685 | -2% | ||
| 2014 3 | $3,639 | -1% | ||
| 2014 4 | $3,514 | -3% | ||
| 2015 1 | $3,556 | 1% | -5% | |
| 2015 2 | $3,498 | -2% | ||
| 2015 3 | $3,329 | -5% | ||
| 2015 4 | $3,142 | -6% | ||
| 2016 1 | $3,395 | 8% | -5% | |
| 2016 2 | $3,268 | -4% | ||
| 2016 3 | $3,254 | -0% | ||
| 2016 4 | $3,097 | -5% | ||
| 2017 1 | ?? | 7%, 1%, 8% | -5% | |
| +6% | ||||
| 2017 1 | $3,254 | $3,283 | $3,225 | |
| Look at OVERALL TREND From Same Qtr Year to Year or Year Ago (YAG) | ||||
| Seasonality = Which Qtrs always go up OR Always Drop, does our Forecast Qtr show an Up or Down vs prior Qtr? | ||||
| Look up history via: | http://www.nasdaq.com/symbol/k/revenue-eps | |||
| insert Stock Symbol where K is Kellogg's | ||||
| Prof S Forecast | Forecast | Actual | Fe | |
| for Kellogg's is: | $3,254 | |||
| But I'm probably wrong thinking they'll be +6% above last Qtr and -5% vs a Year Ago | ||||
| That's a wide spread so Students can pick anywhere inside OR OUTSIDE of that Range | ||||
| for their Kellogg's Forecast |
Graph Only
| PART I | To Do a Graph in Excel: |
| 1) | Drag Cursor over the data you want to graph = First 2 Columns of Kellogg's Data via: |
| Year and Qtr; Kellogg's Sales | |
| and Right Click "Copy" Command | |
| 2) | Click Insert in Top Ribbon and go to Chart Section & click on Line Graphs (to the right of "Column") |
| 3) | Click on the first "2 D" line graph |
| 4) | Return to the Original Worksheet where you want to display the Graph |
| 5) | Move cursor to an open area of the worksheet where you want to show the graph & click |
| 6) | Right Click and Highlight "Paste" and Click on Paste |
| 7) | The Graph will populate in the area (we hope!) |
| PART II | To change the Y Axis Values (Excel shows too many numbers so it's OK to change the Scale) |
| 1) | Drag cursor over the Y Axis Data & Right Click |
| 2) | A dropdown menu list will appear & at the bottom it will indicate "Format Axis" so left click on that |
| btw--if the drop down does NOT list "Format Axis" at the bottom--the repeat the proceedure | |
| 3) | A new drop down menu list will appear & you can use the Axis Options to change the Values in the Graph |
| 4) | For the Graph Above--I made the Minimum $4000 and the Maximum $5000 since that covered the Sales Data Range |
| I also made the Major Unit $100 and Minor Unit $20 which were automatically set by Excel | |
| Part III | Inserting a Trend Line |
| Click on Excel HELP ? And type in the following: | |
| Add, change, or remove a trendline in a chart | |
| Excel will give detailed instructions on how to add a trendline to the Chart--better than me |
Trendline
| Inserting a Trend Line | |
| Click on Excel HELP ? And type in the following: | |
| Add, change, or remove a trendline in a chart | |
| Excel will give detailed instructions on how to add a trendline to the Chart--better than me |
