Do My Homework / Homework Help Answers / Microsoft Excel Homework Help / Using Historic Sales Data, create a monthly forecast using monthly decomposition

# Using Historic Sales Data, create a monthly forecast using monthly decomposition

Need help with this question or any other Microsoft Excel assignment help task?

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. This data set is your historic sales. Please set up your excel spreadsheet to 4 decimal points
Data 4 Month Demand 1/1/19 2395 2/1/19 2584 3/1/19 2681 4/1/19 3002 5/1/19 3473 6/1/19 3358 7/1/19 1201 8/1/19 2549 9/1/19 3012 10/1/19 3561 11/1/19 3432 12/1/19 4515 1/1/20 2434 2/1/20 2627 3/1/20 2710 4/1/20 2986 5/1/20 3602 6/1/20 3487 7/1/20 2626 8/1/20 3045 9/1/20 3044 10/1/20 3794 11/1/20 4275 12/1/20 5001 1/1/21 2476 2/1/21 3039 3/1/21 3050 4/1/21 3431 5/1/21 3712 6/1/21 3915 7/1/21 2983 8/1/21 3305 9/1/21 3325 10/1/21 4508 11/1/21 4691 12/1/21 6504
5.ID Do Not Alter or Delete this Worksheet or you submission cannot be graded! DirVer Action Name Panther ID Date/Time S01 Start S01 Ahadu Solomon 202020202 20-Sep-2021 05:31 Microsoft Office User Starter Sheet None 0 Jan-01 00:00 Welcome to Microsoft Excel version 16.53 build 912 running on Macintosh (Intel) Version 11.2.3 (Build 20D91)! Financial information Donut Information Spring 2021 Based on the below data, create the profit model for Donuts to Go. Ahadu Solomon Assume that each customer will buy one donut and one cup of coffee Enter totals Time period Fixed Costs Revenue: Cup of Coffee \$2.99 Varible Costs Revenue: Donut \$2.50 Coffee Donut ingredients per donunt) per donut \$0.60 Donut paper products: napkins, plates etc Insurance month \$300.00 Maintenance & Repairs to equipment month \$0.00 Marketing & Promotion: Advertising month \$100.00 Coffee per cup \$0.35 Coffee cups per cup \$0.15 Payroll: Wages (Owner/ Manager) month \$2,400.00 Payroll: Wages (per Employees) month \$1,200.00 Donut and Coffee equipment rent month \$500.00 Professional Fees: Accounting month \$50.00 Professional Fees: Legal month \$25.00 Powdered and Liquid Beverages \$0.00 Rent month \$1,000.00 Previous research expense for Donuts advancements \$1,500.00 Supplies: Office month \$25.00 Utilities month \$200.00 Additional Data Operations Monthly Production 4000 Lost Sales 3 Day old revenue 1.25 High demand, % above Average 22% Low Demand, % below average 26% Franchise Operations Monthly Fixed Expense increase 4,350.00 Monthly Production Increase 32% Monthly demand increase 22% States of Natures probabilities Low 20.00% Average demand 55.00% High 25.00% Total SI and regression Pt 1 Month Demand Yearly average Seasonal Index Average SI Deseasonalized Time period Regression Output 1/1/19 CLICK CELL J2 as output cell for regression 2/1/19 3/1/19 4/1/19 5/1/19 6/1/19 7/1/19 8/1/19 9/1/19 10/1/19 11/1/19 12/1/19 1/1/20 2/1/20 3/1/20 4/1/20 5/1/20 6/1/20 7/1/20 8/1/20 9/1/20 10/1/20 11/1/20 12/1/20 1/1/21 2/1/21 3/1/21 4/1/21 5/1/21 6/1/21 7/1/21 8/1/21 9/1/21 10/1/21 11/1/21 Use the average seasonal Index in the column for 2019 12/1/21 Deseasonalized forecast Seasonalized forecast 1/1/22 2/1/22 3/1/22 4/1/22 5/1/22 6/1/22 7/1/22 8/1/22 9/1/22 10/1/22 11/1/22 12/1/22 Highest yearly average Highest Average SI Total 3 year deseasonalized demand Put yearly average in cell I50 When you move your forecast to the profit models, you must use an equation, not just copy the values Total 3 year demand Average 3 year deseasonalized demand Average 3 year demand Ahadu Solomon Spring 2021 Current operations Pt2 & Pt3 CURRENT OPERATIONS Reminder: Format Cells to show 2 decimal places Monthly Production Lost Sales Day old revenue test Part 2 Summer 2020 AVERAGE DEMAND Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total Demand satisfied demand Extra donuts(over) Unsatisfied customers (short) Revenue coffee donut Revenue from day old sales Total Revenue Expenses Fixed Expenses Total Fixed Expense Variable Expenses Coffee Variable expense Donut Variable expense Total Variable Expenses Expenses: due to lost sales Total Expenses Profit Donuts and Coffee Part 3 You should be able to copy from average to high and low and then just make some modifications HIGH DEMAND Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total Demand satisfied demand Extra donuts(over) Unsatisfied customers (short) Revenue coffee donut Revenue from day old sales Total Revenue Expenses Fixed Expenses Total Fixed Expense Variable Expenses Coffee Variable expense Donut Variable expense Total Variable Expenses Expenses: due to lost sales Total Expenses Profit Donuts and Coffee Part 3 LOW DEMAND Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total Demand satisfied demand Extra donuts(over) Unsatisfied customers (short) Revenue coffee donut Revenue from day old sales Total Revenue Expenses Fixed Expenses Total Fixed Expense Variable Expenses Coffee Variable expense Donut Variable expense Total Variable Expenses Expenses: due to lost sales Total Expenses Profit Donuts and Coffee Ahadu Solomon Spring 2021 Franchise operations Pt2 & Pt3 FRANCHISE Reminder: Format Cells to show 2 decimal places Monthly Production Lost Sales Day old revenue Part 2 AVERAGE DEMAND Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total Demand satisfied demand Extra donuts(over) Unsatisfied customers (short) Revenue coffee donut Revenue from day old sales Total Revenue Expenses Fixed Expenses Total Fixed Expense Variable Expenses Coffee Variable expense Donut Variable expense Total Variable Expenses Expenses: due to lost sales Total Expenses Profit Donuts and Coffee Part 3 You should be able to copy from average to high and low and then just make some modifications HIGH DEMAND Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total Demand satisfied demand Extra donuts(over) Unsatisfied customers (short) Revenue coffee donut Revenue from day old sales Total Revenue Expenses Fixed Expenses Total Fixed Expense Variable Expenses Coffee Variable expense Donut Variable expense Total Variable Expenses Expenses: due to lost sales Total Expenses Profit Donuts and Coffee Part 3 LOW DEMAND Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total Demand satisfied demand Extra donuts(over) Unsatisfied customers (short) Revenue coffee donut Revenue from day old sales Total Revenue Expenses Fixed Expenses Total Fixed Expense Variable Expenses Coffee Variable expense Donut Variable expense Total Variable Expenses Expenses: due to lost sales Total Expenses Profit Donuts and Coffee Expected Values Pt3 Remember that we use profits to fill in payoff tables like the ones we forecasted in the previous two sheets. It would help if you filled in the table in C5:E6 and referenced those values to the other tables below. Use the probabilities found in Finanicl Information B42:44 for the regret tables PAYOFF TABLE Reminder: Format Cells to show 2 decimal places State of Nature Summary of Results Methods Fill in the space below for each method solved under each Decision Alternative Decision Alternatives Low Average High Decision Alternatives Maximin MaxiMax Laplace MinMax regret EVUII EOL CURRENT OPERATIONS CURRENT OPERATIONS FRANCHISE FRANCHISE Decision Alternatives DM UNDER IGNORANCE Kiana should choose to: Maximin FRANCHISE State of Nature Decision Alternatives Low Average High Because: CURRENT OPERATIONS Franchise Operations is proving to be the best decision alternative with most methods.For example, if we evaluate the EOL of the two, Franchise Operations has the lowest EOL. This is the best decision as we always want the lowest "loss" or EOL! FRANCHISE Maximax State of Nature Decision Alternatives Low Average High CURRENT OPERATIONS FRANCHISE Laplace State of Nature Decision Alternatives Low Average High CURRENT OPERATIONS FRANCHISE Minimax Regret Regret table State of Nature Decision Alternatives Low Average High CURRENT OPERATIONS FRANCHISE DM UNDER RISK EVUII State of Nature Decision Alternatives Low Average High Probability CURRENT OPERATIONS FRANCHISE EVUPI State of Nature Decision Alternatives Low Average High Probability Payoff EVPI EOL Regret table State of Nature Decision Alternatives Low Average High Probability CURRENT OPERATIONS FRANCHISE
There are no answers to this question.