forecast report and excel

profileC97
ThreeJays_ExcelfileforStudents.xls
Home>Business & Finance homework help>Management homework help>forecast report and excel

Ex. 3 - Prod Cost & Price Data

Exhibit 3: Product Cost and Price Data Summary (dollars per case)
Products Strawberry Jam Raspberry Jelly Peach Jam Bluberry Jam Apple/Mint Jelly
Wholesale/Case (12) 32.45 35.40 31.26 32.76 29.75
Materials - Food Cost 15.92 18.10 14.44 16.59 13.90
Materials - Packaging (Jars) 6.60 6.60 6.60 6.60 6.60
Materials - Packaging (Carton) 0.53 0.53 0.53 0.53 0.53
Labor Cost 1.29 1.29 1.29 1.29 1.29
Variable Overhead 1.45 1.45 1.45 1.45 1.45
Total Variable Costs 25.79 27.97 24.31 26.46 23.77
Contribution to Overhead & Profit 6.66 7.43 6.95 6.30 5.98
Fixed Overhead Costs 2.55 2.55 2.55 2.55 2.55
Full Costs 28.34 30.52 26.86 29.01 26.32
Profit 4.11 4.88 4.40 3.75 3.43

Ex. 4 - EOQ & ROP Calc

Exhibit 4: EOQ and ROP Calculation Using Existing Method
12 ounce Jar Total Set Up Cost (S)* Annual Demand (D)** % Carrying Cost (i) Unit Cost ( C )*** EOQ (Cases) ROP (Cases)
Strawberry Jam 63.70 2,993 9% 28.34 387 173
Raspberry Jelly 63.70 2,335 9% 30.52 329 135
Peach Jam 63.70 1,492 9% 26.86 280 86
Blueberry Jam 63.70 886 9% 29.01 208 51
Apple/Mint Jelly 63.70 625 9% 26.32 183 36
*= 5.75+30.25+4.70+23.50 (from Exhibit 3)
** 2010 Annual Demand
***Full cost from Exhibit 4

Ex. 5 - Monthly Sales Data

Exhibit 5: Monthly Sales Data
Label Type Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec Year Total
3Js Strawberry Jam
2012 345 301 325 299 344 296 329 334 349 325 289 333 3,869
2013 566 671 384 631 616 2,868
Marran Markets Raspberry Jelly
2012 229 270 236 279 273 255 236 232 235 276 244 241 3,006
2013 744 737 425 379 571 2,856
Kerry's Marts Peach Jam
2012 156 176 174 144 160 178 155 159 178 166 176 148 1,970
2013 167 146 78 84 117 592
Dom's Food Stores Blueberry Jam
2012 92 109 98 99 102 111 103 99 94 104 107 93 1,211
2013 100 99 80 139 108 526
AAA Grocers Apple/Mint Jelly
2012 66 77 79 69 65 66 68 67 62 74 71 68 832
2013 73 63 110 146 88 480