The financial reporting to date has been done using absorption costing.That is to say that the manufacturing costs included direct materials, direct labor, variable manufacturing overhead and fixed manufacturing overhead.
kranglo
case_3_performance_drinks_final.xlsxMonthly Profit Report Trad'l
| PERFORMANCE DRINKS - MONTHLY PROFIT REPORT | |||||
| Basic | Hydration | Intensity | Post Workout | Total | |
| REVENUE | |||||
| Sales | $ 125,000 | $ 120,000 | $ 74,250 | $ 93,000 | $ 412,250 |
| COSTS | |||||
| Direct Materials | $ 40,000 | $ 50,000 | $ 31,000 | $ 33,000 | $ 154,000 |
| Direct Labor | $ 25,000 | $ 20,000 | $ 10,000 | $ 18,000 | $ 73,000 |
| Fringe Benefits on Direct Labor | $ 11,250.00 | $ 9,000.00 | $ 4,500.00 | $ 8,100.00 | $ 32,850.00 |
| Manufacturing Overhead | $ 43,750.00 | $ 35,000.00 | $ 17,500.00 | $ 31,500.00 | $ 127,750.00 |
| TOTAL COST | $ 120,000.00 | $ 114,000.00 | $ 63,000.00 | $ 90,600.00 | $ 387,600.00 |
| GROSS MARGIN | $ 5,000.00 | $ 6,000.00 | $ 11,250.00 | $ 2,400.00 | $ 24,650.00 |
| GROSS MARGIN RATIO | 4.00% | 5.00% | 15.15% | 2.58% | 5.98% |
| OPERATING EXPENSES | |||||
| SELLING | |||||
| Sales Salaries | $ 4,500.00 | ||||
| Advertising | $ 5,000.00 | ||||
| Meal, Travel & Entertainment | $ 1,500.00 | ||||
| TOTAL SELLING EXPENSE | $ 11,000.00 | ||||
| GENERAL & ADMINISTRATIVE EXP. | |||||
| Salaries General Admin | $ 7,000.00 | ||||
| General Insurance | $ 3,000.00 | ||||
| Depreciation - Office Equipment | $ 1,500.00 | ||||
| Utilities - Admin | $ 1,000.00 | ||||
| TOTAL GENERAL & ADMIN EXP. | $ 12,500.00 | ||||
| TOTAL OPERATING EXPENSE | $ 23,500.00 | ||||
| OPERATING INCOME | $ 1,150.00 | ||||
| OPERATING INCOME MARGIN RATIO | 0.28% | ||||
| Monthly Volume: | 100,000 | 80,000 | 45,000 | 60,000 | 285,000 |
| Unit Price: | $ 1.25 | $ 1.50 | $ 1.65 | $ 1.55 | $ 1.45 |
| Unit Cost: | $ 1.200 | $ 1.425 | $ 1.400 | $ 1.510 | $ 1.360 |
Mfg OHD
| PERFORMANCE DRINKS - MONTHLY MFG OHD COST REPORT | |
| Monthly Charge | |
| Indirect Labor | $ 55,000.00 |
| Fringe Benefits on Indirect Labor | $ 24,750.00 |
| Utilities | $ 5,000.00 |
| Processing Equipment - Depreciation | $ 10,000.00 |
| Preventative Maintenance | $ 10,000.00 |
| Information Technology | $ 23,000.00 |
| Total | $ 127,750.00 |
| e |
OHD BY MONTH
| YEAR | MONTH | MFG OHD | NON MFG OHD | TOTAL OHD | UNITS SOLD | MACH HOUR USED | ||
| 2011 | January | $ 101,147.00 | $ 20,200.00 | $ 121,347.00 | 235,000 | 14,000 | ||
| 2011 | February | $ 115,035.00 | $ 20,100.00 | $ 135,135.00 | 238,000 | 15,000 | ||
| 2011 | March | $ 104,787.00 | $ 19,600.00 | $ 124,387.00 | 240,000 | 14,400 | ||
| 2011 | April | $ 118,541.00 | $ 19,500.00 | $ 138,041.00 | 265,000 | 15,680 | ||
| 2011 | May | $ 120,031.00 | $ 20,000.00 | $ 140,031.00 | 266,000 | 17,000 | ||
| 2011 | June | $ 125,487.00 | $ 20,400.00 | $ 145,887.00 | 274,500 | 19,500 | ||
| 2011 | July | $ 124,555.00 | $ 20,500.00 | $ 145,055.00 | 275,000 | 20,000 | ||
| 2011 | August | $ 130,412.00 | $ 20,350.00 | $ 150,762.00 | 285,000 | 20,100 | ||
| 2011 | September | $ 145,030.00 | $ 20,050.00 | $ 165,080.00 | 287,000 | 24,000 | ||
| 2011 | October | $ 128,115.00 | $ 19,950.00 | $ 148,065.00 | 278,000 | 19,801 | ||
| 2011 | November | $ 127,150.00 | $ 20,600.00 | $ 147,750.00 | 275,000 | 19,999 | ||
| 2011 | December | $ 126,225.00 | $ 20,620.00 | $ 146,845.00 | 271,250 | 19,850 | ||
| 2012 | January | $ 99,450.00 | $ 20,500.00 | $ 119,950.00 | 232,000 | 15,000 | ||
| 2012 | February | $ 111,999.00 | $ 20,280.00 | $ 132,279.00 | 234,000 | 15,500 | ||
| 2012 | March | $ 112,568.00 | $ 20,560.00 | $ 133,128.00 | 233,000 | 15,500 | ||
| 2012 | April | $ 108,787.00 | $ 20,560.00 | $ 129,347.00 | 231,000 | 14,850 | ||
| 2012 | May | $ 118,050.00 | $ 20,158.00 | $ 138,208.00 | 266,000 | 16,500 | ||
| 2012 | June | $ 124,532.00 | $ 20,176.00 | $ 144,708.00 | 269,000 | 19,500 | ||
| 2012 | July | $ 131,254.00 | $ 20,192.00 | $ 151,446.00 | 284,000 | 19,999 | ||
| 2012 | August | $ 130,555.00 | $ 20,490.00 | $ 151,045.00 | 286,000 | 20,000 | ||
| 2012 | September | $ 149,001.00 | $ 20,532.00 | $ 169,533.00 | 290,000 | 25,000 | ||
| 2012 | October | $ 125,633.00 | $ 20,574.00 | $ 146,207.00 | 274,500 | 19,850 | ||
| 2012 | November | $ 124,131.00 | $ 20,490.00 | $ 144,621.00 | 273,250 | 19,787 | ||
| 2012 | December | $ 124,665.00 | $ 20,562.00 | $ 145,227.00 | 271,450 | 19,899 | ||
| $ 142,253.50 | $ 263,914.58 | $ 18,363.13 | ||||||
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.9642508936 | |||||||
| R Square | 0.9297797857 | |||||||
| Adjusted R Square | 0.9230921463 | |||||||
| Standard Error | 3339.1230904178 | |||||||
| Observations | 24 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 2 | 3100288446.72782 | 1550144223.36391 | 139.0295921226 | 0 | |||
| Residual | 21 | 234144603.272182 | 11149743.012961 | |||||
| Total | 23 | 3334433050 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | 50690.3930928926 | 12216.8308287367 | 4.1492260803 | 0.0004547719 | 25284.102656153 | 76096.6835296322 | 25284.102656153 | 76096.6835296322 |
| X Variable 1 | 0.1314015083 | 0.0751159523 | 1.7493156142 | 0.0948383999 | -0.0248106656 | 0.2876136821 | -0.0248106656 | 0.2876136821 |
| X Variable 2 | 3.0977479378 | 0.5151279873 | 6.0135500574 | 0.000005732 | 2.0264806475 | 4.1690152281 | 2.0264806475 | 4.1690152281 |
