Help me

Create a forecast for average demand

Using historic sales data create a monthly forecast using classical decomposition that is based on regression and seasonality. We are creating a monthly forecast not quarterly.

For help, review Module 4 Excel Exercise. Please note that the Module 4 Exercise is based on quarterly data. Kiana is creating a monthly forecast based on monthly data. You will create a seasonal Index for each month. Therefore, you will have seasonal indexes for Jan, Feb, March, etc. Remember the total of the months seasonal indexes for a year should add up to 12. You will still use a three-year average for your final seasonal index.

Home Insert Draw Page Layout Formulas Data Review View X cut Calibri (Body) 11 A- A- Wra Copy Paste B A Me! Format D34 fx D E F G B C 1 Donut Information 2 Based on the below data, create the profit model for Donuts to Go. 3 Assume that each customer will buy one donut and one cup of coffee 4 5 Time period $4.00 $3.50 $0.50 per donut Variable costs month $200.00 Foxed Costs 6 7 Revenue: Cup of Coffee 8 Revenue: Donut 9 Donut ingredients per donunt) 10 paper products: napkins, plates etc 11 Insurance 12 13 Maintenance & Repairs to equipment 14 Marketing & Promotion: Advertising 15 Coffee 16 Coffee cups 17 Payroll: Wages (Owner Manager) 18 Payroll: Wages (per Employees) 19 Donut and Coffee equipment rent 20 Professional Fees: Accounting 21 Professional Fees: Legal 22 Powdered and Liquid Beverages 23 Rent Previous research expense for Donuts 24 advancements 25 Supplies: Office 26 Utilities 27 28 29 30 Additional Data month month per cup per cup month month month month month $0.00 $50.00 $0.25 $0.10 $2,500.00 $1,000.00 $500.00 $50.00 $25.00 $0.00 $1,000.00 month month month $1,500.00 $25.00 $200.00 25% High demand, Day old % above Low Demand, % 31 Monthly Production Lost Sales revenue Average below average 32 4000 3 1.25 20% 33 Franchise Monthly production increase 34 Franchise Expense 6,000.00 40% 35 Franchise demand increase 24% 36 States of Natures probabilities for Expected Values 37 High demand 20.00% 38 Average demand 55.00% 39 Low demand 25.00% 40 41 42 12 StartHere Financial information SI and regression Current operations Start here_Individual project Home Insert Draw Page Layout Formulas Data Review View X Cut Calibri (Body) 11 A- A Wrap Text General 9 Copy Paste B I E + Merge & Center $ % ) Format 116 B c D H K Regression Output 1 Month Demand Yearly average Seasonal Index Average Si Deseasonalized Time period 1/1/17 3209 2/1/17 2778 3/1/12 2428 4/1/17 2996 6 5/1/17 2349 7 6/1/17 3767 8 7/1/17 3338 9 8/1/17 2604.5 10 9/1/17 2251.5 11 10/1/17 3246 12 11/1/17 3542 13 12/1/17 4331 14 1/1/18 2217 15 2/1/18 2683.5 16 3/1/18 2954.5 17 4/1/18 3235 18 5/1/18 2459 19 6/1/18 3794 20 7/1/18 4589 21 8/1/18 3087 22 9/1/18 3112 23 10/1/1 3785 24 11/1/18 4193 25 12/1/18 5129 26 1/1/19 2674 27 2/1/19 3112.5 28 3/1/19 2848.5 29 4/1/19 2465 30 5/1/19 3770 31 6/1/19 3714 32 7/1/19 3155 33 8/1/19 3268 34 9/1/19 3568 35 10/1/19 4475 36 11/1/19 5248.5 32 12/1/19 5591.5 38 Deseasonalized forecast 39 40 41 42 Seasonalized forecast 43 44 45 46 47 48 49 50 51 StartHere Financial information SI and regression Current operations Future operations Expected Home Insert Draw Page Layout Formulas Data Review View X cut Calibri (Body) 11 A- A- Wra Copy Paste B A Me! Format D34 fx D E F G B C 1 Donut Information 2 Based on the below data, create the profit model for Donuts to Go. 3 Assume that each customer will buy one donut and one cup of coffee 4 5 Time period $4.00 $3.50 $0.50 per donut Variable costs month $200.00 Foxed Costs 6 7 Revenue: Cup of Coffee 8 Revenue: Donut 9 Donut ingredients per donunt) 10 paper products: napkins, plates etc 11 Insurance 12 13 Maintenance & Repairs to equipment 14 Marketing & Promotion: Advertising 15 Coffee 16 Coffee cups 17 Payroll: Wages (Owner Manager) 18 Payroll: Wages (per Employees) 19 Donut and Coffee equipment rent 20 Professional Fees: Accounting 21 Professional Fees: Legal 22 Powdered and Liquid Beverages 23 Rent Previous research expense for Donuts 24 advancements 25 Supplies: Office 26 Utilities 27 28 29 30 Additional Data month month per cup per cup month month month month month $0.00 $50.00 $0.25 $0.10 $2,500.00 $1,000.00 $500.00 $50.00 $25.00 $0.00 $1,000.00 month month month $1,500.00 $25.00 $200.00 25% High demand, Day old % above Low Demand, % 31 Monthly Production Lost Sales revenue Average below average 32 4000 3 1.25 20% 33 Franchise Monthly production increase 34 Franchise Expense 6,000.00 40% 35 Franchise demand increase 24% 36 States of Natures probabilities for Expected Values 37 High demand 20.00% 38 Average demand 55.00% 39 Low demand 25.00% 40 41 42 12 StartHere Financial information SI and regression Current operations Start here_Individual project Home Insert Draw Page Layout Formulas Data Review View X Cut Calibri (Body) 11 A- A Wrap Text General 9 Copy Paste B I E + Merge & Center $ % ) Format 116 B c D H K Regression Output 1 Month Demand Yearly average Seasonal Index Average Si Deseasonalized Time period 1/1/17 3209 2/1/17 2778 3/1/12 2428 4/1/17 2996 6 5/1/17 2349 7 6/1/17 3767 8 7/1/17 3338 9 8/1/17 2604.5 10 9/1/17 2251.5 11 10/1/17 3246 12 11/1/17 3542 13 12/1/17 4331 14 1/1/18 2217 15 2/1/18 2683.5 16 3/1/18 2954.5 17 4/1/18 3235 18 5/1/18 2459 19 6/1/18 3794 20 7/1/18 4589 21 8/1/18 3087 22 9/1/18 3112 23 10/1/1 3785 24 11/1/18 4193 25 12/1/18 5129 26 1/1/19 2674 27 2/1/19 3112.5 28 3/1/19 2848.5 29 4/1/19 2465 30 5/1/19 3770 31 6/1/19 3714 32 7/1/19 3155 33 8/1/19 3268 34 9/1/19 3568 35 10/1/19 4475 36 11/1/19 5248.5 32 12/1/19 5591.5 38 Deseasonalized forecast 39 40 41 42 Seasonalized forecast 43 44 45 46 47 48 49 50 51 StartHere Financial information SI and regression Current operations Future operations Expected