Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Using Forecasting and risk analysis I need detailed steps showing calculations using forecasting and simulation. Sara sells two types of cakes from home Red Velvet
Using Forecasting and risk analysis
I need detailed steps showing calculations using forecasting and simulation.
Sara sells two types of cakes from home Red Velvet and Lotus. The table below shows the historical data of the last two years, it includes Cost per unit, Price per unit and Quantity Sold of Red Velvet Cake: Year Month Cost Year Month Price January February March April May June July August September October November December Price $ 15.00 $ 12.75 $ 7.50 $ 15.00 $ 12.75 $ 7.50 $ 15.00 $ 11.25 $ 7.50 $ 15.00 $ 12.75 $ 7.50 $ 6.10 $ 5.84 $ 5.72 $ 5.83 $ 5.02 $ 6.20 $ 6.15 $ 5.39 $ 6.50 $ 5.43 $ 5.99 $ 5.13 Quantity Sold 50 60 100 60 57 120 65 70 140 85 98 2019 Cost $ 5.93 $ 5.00 $ 5.19 $ 6.02 $ 5.11 $ 5.41 $ 5.19 $ 6.25 $ 6.03 $ 6.02 ? January February March April May June July August September October November Quantity Sold 55 70 110 80 82 140 85 90 140 110 ? 2020 $ 15.00 $ 12.75 $ 7.50 $ 15.00 $ 12.75 $ 7.50 $ 15.00 $ 11.25 $ 7.50 $ 15.00 $ 12.75 150 There is a relationship between Quantity Sold of Red Velvet Cake and the other variables (Months, Cost per unit and Price). Sara should use the historical data to calculate the Profit of Red Velvet Cake for this month. Therefore, she needs to forecast the Quantity Sold of November. She decided to set price per unit as $12.75 and she assumes that cost per unit of November could be any value between minimum and maximum cost appear in the last 2 years. She decided to produce 115 Red Velvet Cake this month. She knows that the Total Fixed cost of this month is either $150, $200, or $250 with probabilities 20%, 30%, 50%, respectively. The table below shows the historical data of the last year, it includes Cost per unit, Price per unit and Quantity Sold of Lotus Cake: Year Month Cost Price Quantity Sold January $ 3.50 $ 12.50 100 February $ 3.50 $ 12.50 115 March $ 3.50 $ 12.50 110 April $ 3.50 $ 12.50 120 May $ 3.50 $ 12.50 115 June $ 3.50 $ 12.50 115 July $ 3.50 $ 12.50 120 August $ 3.50 $ 12.50 125 September $ 3.50 $ 12.50 130 October $ 3.50 $ 12.50 125 November $ 3.50 $ 12.50 ? 2020 There is no relationship between Quantity Sold of Lotus and the other variables (Months, Cost per unit and Price). Sara should use the historical data to calculate the Profit of Lotus for this month. Therefore, she needs to forecast the Quantity Sold of November. Knowing that alpha equal 0.7 and beta equal 0.4. Sara does not have the 1st period forecast and she does not want to assume it in order to start her forecasting, she prefers to do some calculation. Therefore, she decided to use simple linear regression or quadratic regression to find out the value of the 1* period forecast. She decided to set price per unit and cost as previous months. She decided to produce 130 Lotus Cake. She knows that the Total Fixed Cost of this month is could be any value clustered around mean of $200 and standard deviation of $100. Sara wants to know which type is generating less Profit and should be marketed more or replaced with a new one. Question1: You are required to help Sara to find the November Profit of Red Velvet Cake: 1) Perform forecasting for the two years to find November Quantity Sold of Red Velvet Cake. 2) Prepare a simulation of size 50 to determine the November Profit of Red Velvet Cake, showing Total Cost and Revenue as well. Sara sells two types of cakes from home Red Velvet and Lotus. The table below shows the historical data of the last two years, it includes Cost per unit, Price per unit and Quantity Sold of Red Velvet Cake: Year Month Cost Year Month Price January February March April May June July August September October November December Price $ 15.00 $ 12.75 $ 7.50 $ 15.00 $ 12.75 $ 7.50 $ 15.00 $ 11.25 $ 7.50 $ 15.00 $ 12.75 $ 7.50 $ 6.10 $ 5.84 $ 5.72 $ 5.83 $ 5.02 $ 6.20 $ 6.15 $ 5.39 $ 6.50 $ 5.43 $ 5.99 $ 5.13 Quantity Sold 50 60 100 60 57 120 65 70 140 85 98 2019 Cost $ 5.93 $ 5.00 $ 5.19 $ 6.02 $ 5.11 $ 5.41 $ 5.19 $ 6.25 $ 6.03 $ 6.02 ? January February March April May June July August September October November Quantity Sold 55 70 110 80 82 140 85 90 140 110 ? 2020 $ 15.00 $ 12.75 $ 7.50 $ 15.00 $ 12.75 $ 7.50 $ 15.00 $ 11.25 $ 7.50 $ 15.00 $ 12.75 150 There is a relationship between Quantity Sold of Red Velvet Cake and the other variables (Months, Cost per unit and Price). Sara should use the historical data to calculate the Profit of Red Velvet Cake for this month. Therefore, she needs to forecast the Quantity Sold of November. She decided to set price per unit as $12.75 and she assumes that cost per unit of November could be any value between minimum and maximum cost appear in the last 2 years. She decided to produce 115 Red Velvet Cake this month. She knows that the Total Fixed cost of this month is either $150, $200, or $250 with probabilities 20%, 30%, 50%, respectively. The table below shows the historical data of the last year, it includes Cost per unit, Price per unit and Quantity Sold of Lotus Cake: Year Month Cost Price Quantity Sold January $ 3.50 $ 12.50 100 February $ 3.50 $ 12.50 115 March $ 3.50 $ 12.50 110 April $ 3.50 $ 12.50 120 May $ 3.50 $ 12.50 115 June $ 3.50 $ 12.50 115 July $ 3.50 $ 12.50 120 August $ 3.50 $ 12.50 125 September $ 3.50 $ 12.50 130 October $ 3.50 $ 12.50 125 November $ 3.50 $ 12.50 ? 2020 There is no relationship between Quantity Sold of Lotus and the other variables (Months, Cost per unit and Price). Sara should use the historical data to calculate the Profit of Lotus for this month. Therefore, she needs to forecast the Quantity Sold of November. Knowing that alpha equal 0.7 and beta equal 0.4. Sara does not have the 1st period forecast and she does not want to assume it in order to start her forecasting, she prefers to do some calculation. Therefore, she decided to use simple linear regression or quadratic regression to find out the value of the 1* period forecast. She decided to set price per unit and cost as previous months. She decided to produce 130 Lotus Cake. She knows that the Total Fixed Cost of this month is could be any value clustered around mean of $200 and standard deviation of $100. Sara wants to know which type is generating less Profit and should be marketed more or replaced with a new one. Question1: You are required to help Sara to find the November Profit of Red Velvet Cake: 1) Perform forecasting for the two years to find November Quantity Sold of Red Velvet Cake. 2) Prepare a simulation of size 50 to determine the November Profit of Red Velvet Cake, showing Total Cost and Revenue as well Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started