Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Your Bama Burger Buggy is a local food truck company. They currently have multiple trucks serving meals in the local area. Bama Burger Buggy also

Your

Bama Burger Buggy is a local food truck company. They currently have multiple trucks serving meals in the local area. Bama Burger Buggy also has a central storage and kitchen facility that preps everything for the day. Inventory is held at the central storage facility and administrative duties are done there. Bama Burger prides themselves on fresh, homemade products. Everything they sell is handmade in their central kitchen facility. Project Description Bama Burger, like every business, must have reliable forecasts to make the best decisions possible. Accurate forecasts will allow Bama Burger to properly staff, carry the correct amount of inventory and to plan their cash flow requirements into the upcoming months. Bama Burger has historical data on each item sold. For the short term decisions that will be made the data is delivered in monthly sales per item. Deliverables Bama Burger has hire you as their consultant. You have been asked to analyze the historical data to find patterns and to use this insight to produce an accurate monthly forecast for October, November and December of 2022. You will only need to analyze historical data for hamburgers and for coffee. These two items should give you enough insight into the different sales patterns to make an informed decision about all the items going forward. Assignment Download the "BBB - Forecast file" from Blackboard to your computer. Rename the file "BamaID_Forecast Extra Credit" (example: nechilcutt_Forecast Extra Credit) before you submit your completed assignment. Please keep it as an Excel file (.xlsx). Other spreadsheet file types will not be accepted. Do not change the names on the tabs. "Monthly Sales Data" Sheets - You may manipulate the data as you see fit on this page. The historical data for all seven menu items have been listed here. Use this data as you make your deliverable forecast. The data you need for Hamburger Analysis and Coffee Analysis has already been loaded on those pages. "Analysis" Sheets - Each column holds information you may find helpful in your analysis. Actual data for each item is posted on the analysis sheet in Column B on each page. The following is a data dictionary for the listed information: "Actual" - Historical monthly sales data for Hamburgers and Coffee are posted here "4 Month MA" - You are asked to deliver a 4 month moving average for Hamburger and Coffee in columns C, Deviation of that forecast in Column D & an Absolute Deviation in Column E thru Sept 2022. Figure the MAD for this tool in E62. Use this information to analyze the accuracy of this forecasting tool.

"4 Month Weighted MA" - You are asked to deliver a 4 month weighted moving average for Hamburger and Coffee in columns G, Deviation of that forecast in Column H & an Absolute Deviation in Column I thru Sept 2022. Bama Burger has assigned weights of 0.10 to the oldest month, 0.15 to the second oldest, 0.25 to the next and 0.50 to the youngest month. Figure the MAD for this tool in I62. Use this information to analyze the accuracy of this forecasting tool. "Exponential Smoothing" - You are asked to deliver a historical forecast using Exponential Smoothing for Hamburger and Coffee in columns J, Deviation of that forecast in Column K & an Absolute Deviation in Column L thru Sept 2022. Bama Burger has historically used 0.30 as their Alpha and begins the forecast with a Naive Forecast to get things started. Figure the MAD for this tool in L62. Use this information to analyze the accuracy of this forecasting tool. The Bama Burger investors are Auburn grads and they will need pictures to understand your analysis. Bama Burger has asked for at least one meaningful graph comparing the historical sales data with one of your historical forecasts from the above list. A meaningful graph is self-explanatory with correct titles, headers, vertical and horizonal data points and in a proper format for the data being used. Simplicity and clarity are key to a good graph. "Deliverables" Sheet - 1. Type your name in Cell D2 as the Author. Type the section in I2. 2. Use the forecasting tool of your choice to make an October, November and December forecast of each item on the menu at Bama Burger Buggy. Enter your forecast in cells (C6:I8) 3. You may use any forecasting tool you choose (or any combination of tools). This will be graded competitively against all other entries from the class. The most accurate forecast will receive the most points. 4. In the text box (C10:I13) let me know which forecasting tool you chose to use. You must have information in all of the Analysis and Deliverables sheet places listed above. If you do not, no credit will be given.

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed
11:07 Safari .01 5GEO Forecast Bonus Fall 2022 2 AD Q . . . B C G H K M Units sold each month Date Burgerz Cheese Burger Chicken Salad Fries Soda Coffee 1/1/18 1,474 L,154 955 921 Use this page for any additional /1/18 3,030 1,962 1,195 1,620 929 766 383 2,516 1,715 work you want to do. 3/1/18 1,373 1,296 ,014 868 1,262 4/1/18 2,630 2,276 1,481 1,194 934 783 1,273 Think of it as Excel Scratch Paper. 2,464 /1/18 2,286 1,299 1,370 991 860 1,555 2,572 2,449 1,444 6/1/18 819 519 582 1,696 1,807 2,628 7/1/18 691 945 473 513 1,767 1,402 3,240 704 /1/18 869 24 651 1,560 1,739 9/1/18 3,996 977 1,168 878 736 1,129 2,314 10/1/18 2,085 1,314 1,292 1,020 896 998 13 2,720 2,399 1/1/18 1,514 1,373 L,026 860 908 2,822 1,728 2/1/18 1,340 1,508 1,015 826 884 2,705 L.576 1,493 /1/19 2,052 1,600 1,245 16 1,121 4,061 2,614 2,341 2/1/19 1,680 1,294 1,006 1,148 17 3/1/19 3,361 2,300 1,889 1,74 1,376 1,10 18 1,559 3,452 2,958 4/1/19 1,948 1,750 1,380 19 1,108 1,745 5/1/19 3,538 1,806 3,262 1,972 1.449 1,130 2,027 3.631 3,432 20 2.054 6/1/19 1,054 801 707 21 2,145 2,168 1,167 7/1/19 884 3,439 645 618 22 2,459 1,900 4,657 982 8/1/19 1,056 787 730 1,955 2,158 23 5,502 1,210 9/1/19 1,647 1,329 24 1,055 1,530 10/1/19 3,314 2,948 1,880 ,850 1,447 1,134 1,269 25 3,642 3,183 11/1/19 2,077 1,788 1.387 1,062 1,034 26 3.564 2,214 2/1/19 2.010 1,863 1,498 1,132 1.135 27 3,784 2,039 2,195 1/1/20 1,176 887 824 823 28 2,300 1,608 1,310 2/1/20 940 711 668 749 29 /1/20 1,951 1,363 1,000 1,073 730 651 30 1,012 2,115 1,712 /1/20 949 1,125 706 665 1,041 31 2,041 5/1/20 ,875 1,102 958 599 32 638 1,172 1,964 1,824 5/1/20 736 1,060 507 525 1,341 33 1,521 2,080 7/1/20 596 753 109 492 1,416 34 1,285 /1/20 593 2,444 578 445 472 1,129 35 1,342 2,745 9/1/20 966 621 712 651 980 36 1,950 10/1/20 1,733 1,078 1,007 733 37 678 839 2,100 11/1/20 1,816 1,149 1,020 760 683 788 38 2/1/20 2,005 1,298 1,123 1,099 825 771 786 2,308 39 1,370 1,226 1/1/21 1,599 1,214 933 930 3,179 2,146 2/1/21 1,759 1,404 1,121 883 41 970 2,866 3/1/21 1,986 1,586 1,587 1.249 1,004 1,380 42 3,147 2,639 4/1/21 1,751 1,461 1,140 929 1,468 43 2,925 2,787 1,582 5/1/21 1,366 1,058 881 44 1,591 2,789 2,643 6/1/21 1,498 977 703 669 1,919 2,052 45 7/1/21 3,103 1,109 730 515 526 46 1,975 1,640 3,780 808 8/1/21 895 532 618 1,595 47 1,807 4,445 979 9/1/21 1,412 1.086 894 1,347 48 2,875 2.569 1,568 10/1/21 1,167 970 49 994 2,921 11/1/21 2,566 1,554 1,628 1,223 980 957 50 3,160 12/1/21 1,950 1,754 1,675 1,272 1,040 991 51 3.418 1,822 1,849 4/1/22 2,163 1,741 1,511 52 1,327 3,797 2,788 2,951 2/1/22 1,934 1,562 53 1,267 1,489 3,312 2,591 2,527 3/1/22 2,096 1,730 54 1,389 2,162 3,625 3,525 /1/22 1,899 2,740 ,513 1,273 2,253 55 3,386 3,612 5/1/22 2,520 1,975 1,577 1,330 2,696 56 3,531 6/1/22 1,257 3,716 2,607 976 57 877 3,227 2,368 4,209 7/1/22 927 1,638 691 688 58 3,137 1,704 4,987 3/1/22 1,170 1,183 902 856 59 2,753 2,204 9/1/22 6,503 1,944 1,518 1.574 1,341 2,095 60 3,508 3,463 10/1/22 2,632 61 11/1/22 62 12/1/22 63 64 65 66 67 68 69 70 71 72 73 74 fx Enter text or formula here Monthly Sales Data... Burger Analy +2:54 Today 12:32 AM Alpha 4 Month Weighted MA Exp. Smoothing Actual 4 Month MA Deviation Absoluted Deviation Forecast Deviation Absoluted Deviation Deliver one m Date Coffee Forecast Deviation Absoluted Deviation Weights Forecast Jan-18 1620 0.1 0.15 Feb-18 1373 Mar-18 1481 0.25 0.50 Apr-18 1370 May-18 1444 Jun-18 945 Jul-18 704 Aug-18 977 Sep-18 1292 Oct. 18 1514 Nov-18 1508 Dec-18 1493 Jan- 19 2341 Feb-19 1889 Mar-19 1948 Apr-19 1972 May-19 2054 Jun-19 1167 Jul-19 982 Aug-19 1210 Sep-19 1880 Oct-19 2077 Nov-19 2010 Dec-19 2195 Jan-20 1310 Feb-20 1073 Mar-20 1125 Apr-20 1102 May-2 1060 Jun-20 753 Jul-20 578 Aug-20 621 Sep-20 1078 Oct-20 1149 Nov-20 1123 Dec-20 1226 Jan-21 1759 Feb-21 1587 Mar-21 1751 Apr-21 1582 May-21 1498 Jun-21 1109 Jul-21 808 Aug-21 979 Sep-21 1568 Oct-21 1628 Nov-21 1754 Dec-21 1849 Jan-22 2951 Feb-22 2527 Mar-22 2740 Apr-22 2520 May-22 2607 Jun-22 1638 Jul-22 1170 Minimum Aug-22 1518 Sep-22 2632 MAD MAD MAD Delete Recover2:54 Today 12:32 AM alpha Actual 4 Month MA 4 Month Weighted MA Exp. Smoothing te Hamburger n Absoluted Deviat Weights Forecast Deviation Hamburger Deviation Absoluted Deviation Deliver one mean 0.1 below Feb-18 1195 Mar-18 1296 0.25 Apr-18 1194 0.50 May-18 1299 Jun-18 819 Jul-18 691 Aug-18 869 Sep-18 1168 Oct-18 1314 Nov-18 1373 Dec-18 1340 Jan-19 2052 Feb-19 1680 Mar-19 1747 Apr-19 1750 May-19 1806 Jun-19 1054 Jul-19 884 1056 Sep-19 1647 Oct- 19 1850 1788 Dec-19 1863 1176 Feb-20 940 Mar-20 1000 pr-20 949 tay-20 un-20 Jul-20 WUR -20 Oct.20 1007 Nov-20 1020 Jee-20 1099 an- 21 1599 1404 Mar-21 1586 Apr- 21 1461 May-21 1366 Jun-21 977 Jul-21 730 Aug-21 895 1412 oct-21 1472 Nov-21 1554 Dec-21 1675 Jan-22 2163 Feb.22 1934 Mar-22 Apr-22 2096 1899 May-22 1975 Jun-22 1257 Jul-22 927 Aug.22 1183 Sep-22 1944 MAD Delete Recover2:55 Today K 1:34 AM C D E G H A B Name: Type your name here Section: Enter your sectic My forecast for Cheese Burger Soda Coffee Burgerz Chicken Salad Fries Oct-22 Nov-22 Dec-22 How did you arrive at your forecast (or what tool did you use)? 11 12 13 14 16 20 62% 33 34 35 36 37 38 39 40 41 42 43 44 My forecast Delete Recover

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamentals Of Project Management

Authors: Joseph Heagney

6th Edition

140023526X, 978-1400235261

More Books

Students also viewed these General Management questions

Question

4. Will technology eliminate the need for HR managers?

Answered: 1 week ago