I need the Excel formula for finding the Sales Forecast for this problem . The p
ID: 3270282 • Letter: I
Question
I need the Excel formula for finding the Sales Forecast for this problem.
The printscreen is the steps I have completed so far.
Assignment Tasks Event Profitability Home ert Page Layout Formas Data Connectior Show Detail Solver In the "Input Analysis" section of the spreadsheet model, calculate a sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference cell 113 (the attendance forecast) for your calculations. [7 points Properties Hide Detail 6 From From From From Othe Exsting Refresh Access Web Text Sources Connections! All Edit Links Get External Dato Connections Analysis 116 iea Model Inputs Input Analysis ou are Food Merchandise 70% 5% of Events Product Type Cost of Goods Sold (COGS Commission Fixed Costs Employees (% of Expected Profit) Salary (per employee) Programs 40% 20% basket 10% Statistic Attendance Programs Food Merchandise 5,000.00 $10,000.00 $20,000.00 0.20% 14965.76 72471.76 154242.26 393360.47 227919.37 $8,366.52 $12,980.73 $44,671.38 inside food, a to help 0.03% 0.05% $50.00 $100,000.00 Standard Deviation 3994.23 42687.13 66229.45 95% Confidence Interval Product Type Programs Food Merchandise selecte 0.550.62 0.54 Event Profitability Analysis Correlation with Attendance In:lp 12 Gross Profit Attendance 18000 values Product Type ProgramsFood Merchandise Sales Cost of Goods Sold (COGS) Gross Profit Product Type Programs Food Merchandise the cell Sales Forecast Upper Limit for Sales Lower Limit for Sales 16 18 Operating Expenses Product Type ProgramsFood Merchandise What-if Analysis 21 Attendance Forecast Salary Expenses Commissions Fixed Costs Total Operating Expenses 15000 21000 $5,000.00 $10,000.00 $20,000.00 $50,000.00 75,000.00 s!00,000.00 $125,000.00 2s 25 26 (4 n Event Profit PTI pT2 p T3· PT4 PTS Past Events PivotTable Questions . Bakery Ready Profit before Arena Fee E 8/22/2017Explanation / Answer
Excel Sales Forecast Accuracy Formula and Calculation
formula for forecast accuracy treats both of these situations as equally bad. we take the absolute value of (Forecast–Actual) and divide by the larger of the forecasts or actual.
Forecast accuracy calculation follows these steps.
1) First, calculate the absolute error on a product-by-product basis. Whether the forecast was high or low, the error is always a positive number. The ABS function returns the Absolute Value of a number
2) Then, calculate the divisor. This is what we call “Size of the opportunity to screw up".
3) Finally, calculate the error percentage by dividing error by divisor