Toomers 635
Askabul
ToomerEnergyExcelFile.xlsxT0-SalesForecast
| Toomer's Energy Drink | ||||||||
| 2016 Sales Projections | ||||||||
| Flavor | Bottle Size | 2016 Forecast | Total Sales | List Price | Sales € | |||
| (in Units) | 250 ml | 9,200,000 | 1.99 € | 18,308,000 € | ||||
| Strawberry | 250 ml | 3,000,000 | 500 ml | 7,500,000 | 3.49 € | 26,175,000 € | ||
| 500 ml | 2,450,000 | Total Sales | 16,700,000 | 44,483,000 € | ||||
| Lemon | 250 ml | 3,400,000 | ||||||
| 500 ml | 2,750,000 | |||||||
| Grape | 250 ml | 2,800,000 | ||||||
| 500 ml | 2,300,000 | |||||||
| Total | 16,700,000 |
T1-Production Cost Forecast
| Toomer's Energy Drink | ||||||
| 2016 Standard Costs | ||||||
| Forecasted Production Fixed Costs | ||||||
| Total: | 9,400,000 € | |||||
| Allocated to each product group based on ml: | ||||||
| Bottle Size | Original Forcast | Total ml | % of ml | FC allocation | FC per unit | |
| 250 ml | 9,200,000 | 2,300,000,000 | 38.02% | 3,573,554 € | 0.388 € | |
| 500 ml | 7,500,000 | 3,750,000,000 | 61.98% | 5,826,446 € | 0.777 € | |
| Total | 6,050,000,000 | 9,400,000 € | ||||
| Forecasted Production Variable Costs | ||||||
| Direct labor + Direct materials + Variable Overhead Costs | ||||||
| Flavor | Bottle Size | Variable Costs per Unit | ||||
| Strawberry | 250 ml | 0.352 € | ||||
| 500 ml | 0.687 € | |||||
| Lemon | 250 ml | 0.377 € | ||||
| 500 ml | 0.723 € | |||||
| Grape | 250 ml | 0.327 € | ||||
| 500 ml | 0.636 € |
T2- Baseline Performance Goals
| Toomer's Energy Drinks | |||
| Budgeted Income Statement (Baseline Goals) | |||
| 2016 | |||
| Projection for year ended December 31 | |||
| Revenue | Unit Rev | Units | |
| 250-Strawberry | 1.990 € | 3,000,000 | 5,970,000 € |
| 250-Lemon | 1.990 € | 3,400,000 | 6,766,000 € |
| 250-Grape | 1.990 € | 2,800,000 | 5,572,000 € |
| Total 250ml | 9,200,000 | 18,308,000 € | |
| 500-Strawberry | 3.490 € | 2,450,000 | 8,550,500 € |
| 500-Lemon | 3.490 € | 2,750,000 | 9,597,500 € |
| 500-Grape | 3.490 € | 2,300,000 | 8,027,000 € |
| Total 500ml | 7,500,000 | 26,175,000 € | |
| Total Gross Revenue | 44,483,000 € | ||
| Less: Bad Debt Expenses Percentage of Sales | 2.50% | 1,112,075 € | |
| Net Revenue | 43,370,925 € | ||
| Cost of Goods Sold | Unit Cost | Units | |
| Beginning Finished Goods Inventory | |||
| 250-Strawberry | 0.740 € | 52,500 | 38,850 € |
| 250-Lemon | 0.765 € | 53,258 | 40,742 € |
| 250-Grape | 0.715 € | 45,250 | 32,354 € |
| Total 250ml | 151,008 | 111,946 € | |
| 500-Strawberry | 1.464 € | 33,125 | 48,495 € |
| 500-Lemon | 1.500 € | 34,250 | 51,375 € |
| 500-Grape | 1.413 € | 31,582 | 44,625 € |
| Total 500ml | 98,957 | 144,495 € | |
| Total Beginning Finished Goods Inventory | 249,965 | 256,441 € | |
| Plus, Cost of Goods Manufactured | |||
| 250-Strawberry | 0.740 € | 3,000,000 | 2,220,000 € |
| 250-Lemon | 0.765 € | 3,400,000 | 2,601,000 € |
| 250-Grape | 0.715 € | 2,800,000 | 2,002,000 € |
| Total 250ml | 9,200,000 | 6,823,000 € | |
| 500-Strawberry | 1.464 € | 2,450,000 | 3,586,800 € |
| 500-Lemon | 1.500 € | 2,750,000 | 4,125,000 € |
| 500-Grape | 1.413 € | 2,300,000 | 3,249,900 € |
| Total 500ml | 7,500,000 | 10,961,700 € | |
| Total Cost of Goods Manufactured | 16,700,000 | 17,784,700 € | |
| Less: Ending Finished Goods Inventory | |||
| 250-Strawberry | 0.740 € | 52,500 | 38,850 € |
| 250-Lemon | 0.765 € | 53,258 | 40,742 € |
| 250-Grape | 0.715 € | 45,250 | 32,354 € |
| Total 250ml | 151,008 | 111,946 € | |
| 500-Strawberry | 1.464 € | 33,125 | 48,495 € |
| 500-Lemon | 1.500 € | 34,250 | 51,375 € |
| 500-Grape | 1.413 € | 31,582 | 44,625 € |
| Total 500ml | 98,957 | 144,495 € | |
| Cost of Goods Sold | |||
| 250-Strawberry | 0.740 € | 3,000,000 | 2,220,000 € |
| 250-Lemon | 0.765 € | 3,400,000 | 2,601,000 € |
| 250-Grape | 0.715 € | 2,800,000 | 2,002,000 € |
| Total 250ml | 9,200,000 | 6,823,000 € | |
| 500-Strawberry | 1.464 € | 2,450,000 | 3,586,800 € |
| 500-Lemon | 1.500 € | 2,750,000 | 4,125,000 € |
| 500-Grape | 1.413 € | 2,300,000 | 3,249,900 € |
| Total 500ml | 7,500,000 | 10,961,700 € | |
| Total Cost of Goods Sold | 16,700,000 | 17,784,700 € | |
| Gross Profit (includes bad debt expense) | |||
| 250-Strawberry | 1.200 € | 3,000,000 | 3,600,750 € |
| 250-Lemon | 1.175 € | 3,400,000 | 3,995,850 € |
| 250-Grape | 1.225 € | 2,800,000 | 3,430,700 € |
| Total 250ml | 9,200,000 | 11,027,300 € | |
| 500-Strawberry | 1.939 € | 2,450,000 | 4,749,938 € |
| 500-Lemon | 1.903 € | 2,750,000 | 5,232,563 € |
| 500-Grape | 1.990 € | 2,300,000 | 4,576,425 € |
| Total 500ml | 7,500,000 | 14,558,925 € | |
| Total Gross Profit | 16,700,000 | 25,586,225 € | |
| Less, Selling, General and Administrative Expenses | |||
| Commissions | 2.0% | of net sales | 867,419 € |
| Advertising | 5,250,000 € | ||
| Salaries and wages | 5,625,047 € | ||
| Depreciation and amortization | 2,420,000 € | ||
| Maintenance and repairs | 520,830 € | ||
| Freight and logistics | 2.777% | of net sales | 1,204,500 € |
| Office expenses | 245,830 € | ||
| Office Rent | 1,000,000 € | ||
| Research and development | 800,000 € | ||
| Other expenses | 150,000 € | ||
| Total SG&A | 18,083,626 € | ||
| Operating Income | 7,502,600 € | ||
| Baseline Bonus reached @ 7.5M € | |||
T3-Achievement of Stretch Goals
| Toomer's Energy Drinks | |||||||||||
| Budgeted Income Statement (Actual: October 31, 2016) | |||||||||||
| 2016 | |||||||||||
| Actual Results as of October 31, | Budget for November & December | Projection for year ended December 31 | |||||||||
| Revenue | Unit Rev | Units | Unit Rev | Units | Unit Rev | Units | |||||
| 250-Strawberry | 1.990 € | 2,995,194 | 5,960,436 € | 1.990 € | 300,000 | 597,000 € | 1.990 € | 3,295,194 | 6,557,436 € | ||
| 250-Lemon | 1.990 € | 3,195,983 | 6,360,006 € | 1.990 € | 340,000 | 676,600 € | 1.990 € | 3,535,983 | 7,036,606 € | ||
| 250-Grape | 1.990 € | 2,673,241 | 5,319,750 € | 1.990 € | 280,000 | 557,200 € | 1.990 € | 2,953,241 | 5,876,950 € | ||
| Total 250ml | 8,864,418 | 17,640,192 € | 920,000 | 1,830,800 € | 9,784,418 | 19,470,992 € | |||||
| 500-Strawberry | 3.490 € | 2,325,643 | 8,116,494 € | 3.490 € | 245,000 | 855,050 € | 3.490 € | 2,570,643 | 8,971,544 € | ||
| 500-Lemon | 3.490 € | 2,356,456 | 8,224,031 € | 3.490 € | 275,000 | 959,750 € | 3.490 € | 2,631,456 | 9,183,781 € | ||
| 500-Grape | 3.490 € | 2,337,362 | 8,157,393 € | 3.490 € | 230,000 | 802,700 € | 3.490 € | 2,567,362 | 8,960,093 € | ||
| Total 500ml | 7,019,461 | 24,497,919 € | 750,000 | 2,617,500 € | 7,769,461 | 27,115,419 € | |||||
| Total Gross Revenue | 15,883,879 | 42,138,111 € | 1,670,000 | 4,448,300 € | 17,553,879 | 46,586,411 € | |||||
| Less, Bad Debt Expenses | 2.50% | 1,053,453 € | 2.50% | 111,208 € | 2.50% | 1,164,660 € | |||||
| Net Revenue | 41,084,658 € | 4,337,093 € | 45,421,750 € | ||||||||
| Cost of Goods Sold | Unit Cost | Units | Unit Cost | Units | Unit Cost | Units | |||||
| Beginning Finished Goods Inventory | |||||||||||
| 250-Strawberry | 0.740 € | 52,500 | 38,850 € | 0.740 € | 52,500 | 38,850 € | 0.740 € | 52,500 | 38,850 € | ||
| 250-Lemon | 0.765 € | 53,258 | 40,742 € | 0.765 € | 53,258 | 40,742 € | 0.765 € | 53,258 | 40,742 € | ||
| 250-Grape | 0.715 € | 45,250 | 32,354 € | 0.715 € | 45,250 | 32,354 € | 0.715 € | 45,250 | 32,354 € | ||
| Total 250ml | 151,008 | 111,946 € | 151,008 | 111,946 € | 151,008 | 111,946 € | |||||
| 500-Strawberry | 1.464 € | 33,125 | 48,495 € | 1.464 € | 33,125 | 48,495 € | 1.464 € | 33,125 | 48,495 € | ||
| 500-Lemon | 1.500 € | 34,250 | 51,375 € | 1.500 € | 34,250 | 51,375 € | 1.500 € | 34,250 | 51,375 € | ||
| 500-Grape | 1.413 € | 31,582 | 44,625 € | 1.413 € | 31,582 | 44,625 € | 1.413 € | 31,582 | 44,625 € | ||
| Total 500ml | 98,957 | 144,495 € | 98,957 | 144,495 € | 98,957 | 144,495 € | |||||
| Total Beginning Finished Goods Inventory | 249,965 | 256,441 € | 249,965 | 256,441 € | 249,965 | 256,441 € | |||||
| Plus, Cost of Goods Manufactured | |||||||||||
| 250-Strawberry | 0.740 € | 2,995,194 | 2,216,444 € | 0.740 € | 300,000 | 222,000 € | 0.740 € | 3,295,194 | 2,438,444 € | ||
| 250-Lemon | 0.765 € | 3,195,983 | 2,444,927 € | 0.765 € | 340,000 | 260,100 € | 0.765 € | 3,535,983 | 2,705,027 € | ||
| 250-Grape | 0.715 € | 2,673,241 | 1,911,367 € | 0.715 € | 280,000 | 200,200 € | 0.715 € | 2,953,241 | 2,111,567 € | ||
| Total 250ml | 8,864,418 | 6,572,738 € | 920,000 | 682,300 € | 9,784,418 | 7,255,038 € | |||||
| 500-Strawberry | 1.464 € | 2,325,643 | 3,404,741 € | 1.464 € | 245,000 | 358,680 € | 1.464 € | 2,570,643 | 3,763,421 € | ||
| 500-Lemon | 1.500 € | 2,356,456 | 3,534,684 € | 1.500 € | 275,000 | 412,500 € | 1.500 € | 2,631,456 | 3,947,184 € | ||
| 500-Grape | 1.413 € | 2,337,362 | 3,302,693 € | 1.413 € | 230,000 | 324,990 € | 1.413 € | 2,567,362 | 3,627,683 € | ||
| Total 500ml | 7,019,461 | 10,242,118 € | 750,000 | 1,096,170 € | 7,769,461 | 11,338,288 € | |||||
| Total Cost of Goods Manufactured | 15,883,879 | 16,814,856 € | 1,670,000 | 1,778,470 € | 17,553,879 | 18,593,326 € | |||||
| Less: Ending Finished Goods Inventory | |||||||||||
| 250-Strawberry | 0.740 € | 52,500 | 38,850 € | 0.740 € | 52,500 | 38,850 € | 0.740 € | 52,500 | 38,850 € | ||
| 250-Lemon | 0.765 € | 53,258 | 40,742 € | 0.765 € | 53,258 | 40,742 € | 0.765 € | 53,258 | 40,742 € | ||
| 250-Grape | 0.715 € | 45,250 | 32,354 € | 0.715 € | 45,250 | 32,354 € | 0.715 € | 45,250 | 32,354 € | ||
| Total 250ml | 151,008 | 111,946 € | 151,008 | 111,946 € | 151,008 | 111,946 € | |||||
| 500-Strawberry | 1.464 € | 33,125 | 48,495 € | 1.464 € | 33,125 | 48,495 € | 1.464 € | 33,125 | 48,495 € | ||
| 500-Lemon | 1.500 € | 34,250 | 51,375 € | 1.500 € | 34,250 | 51,375 € | 1.500 € | 34,250 | 51,375 € | ||
| 500-Grape | 1.413 € | 31,582 | 44,625 € | 1.413 € | 31,582 | 44,625 € | 1.413 € | 31,582 | 44,625 € | ||
| Total 500ml | 98,957 | 144,495 € | 98,957 | 144,495 € | 98,957 | 144,495 € | |||||
| Total Ending Finished Goods Inventory | 249,965 | 256,441 € | 249,965 | 256,441 € | 249,965 | 256,441 € | |||||
| Cost of Goods Sold | |||||||||||
| 250-Strawberry | 0.740 € | 2,995,194 | 2,216,444 € | 0.740 € | 300,000 | 222,000 € | 0.740 € | 3,295,194 | 2,438,444 € | ||
| 250-Lemon | 0.765 € | 3,195,983 | 2,444,927 € | 0.765 € | 340,000 | 260,100 € | 0.765 € | 3,535,983 | 2,705,027 € | ||
| 250-Grape | 0.715 € | 2,673,241 | 1,911,367 € | 0.715 € | 280,000 | 200,200 € | 0.715 € | 2,953,241 | 2,111,567 € | ||
| Total 250ml | 8,864,418 | 6,572,738 € | 920,000 | 682,300 € | 9,784,418 | 7,255,038 € | |||||
| 500-Strawberry | 1.464 € | 2,325,643 | 3,404,741 € | 1.464 € | 245,000 | 358,680 € | 1.464 € | 2,570,643 | 3,763,421 € | ||
| 500-Lemon | 1.500 € | 2,356,456 | 3,534,684 € | 1.500 € | 275,000 | 412,500 € | 1.500 € | 2,631,456 | 3,947,184 € | ||
| 500-Grape | 1.413 € | 2,337,362 | 3,302,693 € | 1.413 € | 230,000 | 324,990 € | 1.413 € | 2,567,362 | 3,627,683 € | ||
| Total 500ml | 7,019,461 | 10,242,118 € | 750,000 | 1,096,170 € | 7,769,461 | 11,338,288 € | |||||
| Cost of Goods Sold before standard cost variance | 15,883,879 | 16,814,856 € | 1,670,000 | 1,778,470 € | 17,553,879 | 18,593,326 € | |||||
| Fixed overhead variance (overappplied) underapplied | - 436,125 € | 0 | - 436,125 € | ||||||||
| Total Cost of Goods Sold | 15,883,879 | 16,378,730 € | 1,670,000 | 1,778,470 € | 17,553,879 | 18,157,200 € | |||||
| Gross Profit (includes bad debt expense and fixed overhead variance) | |||||||||||
| 250-Strawberry | 1.200 € | 2,995,194 | 3,594,982 € | 1.200 € | 300,000 | 360,075 € | 1.200 € | 3,295,194 | 3,955,057 € | ||
| 250-Lemon | 1.175 € | 3,195,983 | 3,756,079 € | 1.175 € | 340,000 | 399,585 € | 1.175 € | 3,535,983 | 4,155,664 € | ||
| 250-Grape | 1.225 € | 2,673,241 | 3,275,389 € | 1.225 € | 280,000 | 343,070 € | 1.225 € | 2,953,241 | 3,618,459 € | ||
| Total 250ml | 8,864,418 | 10,626,449 € | 920,000 | 1,102,730 € | 9,784,418 | 11,729,179 € | |||||
| 500-Strawberry | 1.939 € | 2,325,643 | 4,508,840 € | 1.939 € | 245,000 | 474,994 € | 1.939 € | 2,570,643 | 4,983,834 € | ||
| 500-Lemon | 1.903 € | 2,356,456 | 4,483,747 € | 1.903 € | 275,000 | 523,256 € | 1.903 € | 2,631,456 | 5,007,003 € | ||
| 500-Grape | 1.990 € | 2,337,362 | 4,650,766 € | 1.990 € | 230,000 | 457,643 € | 1.990 € | 2,567,362 | 5,108,409 € | ||
| Total 500ml | 7,019,461 | 13,643,353 € | 750,000 | 1,455,893 € | 7,769,461 | 15,099,246 € | |||||
| Fixed overhead variance overappplied (underapplied) | 436,125 € | - 0 € | 436,125 € | ||||||||
| Total Gross Profit | 15,883,879 | 24,705,928 € | 1,670,000 | 2,558,623 € | 17,553,879 | 27,264,550 € | |||||
| Less, Selling, General and Administrative Expenses | |||||||||||
| Commissions | 2.0% | of net sales | 821,693 € | 2.0% | of net sales | 86,742 € | 2.0% | of net sales | 908,435 € | ||
| Advertising | 4,853,000 € | 375,000 € | 5,228,000 € | ||||||||
| Salaries and wages | 4,642,539 € | 887,508 € | 5,530,047 € | ||||||||
| Depreciation and amortization | 1,916,667 € | 403,333 € | 2,320,000 € | ||||||||
| Maintenance and repairs | 404,025 € | 86,805 € | 490,830 € | ||||||||
| Freight and logistics | 2.777% | of net sales | 1,141,006 € | 2.777% | of net sales | 120,450 € | 2.777% | of net sales | 1,261,456 € | ||
| Office expenses | 179,858 € | 40,972 € | 220,830 € | ||||||||
| Office rent | 833,333 € | 166,667 € | 1,000,000 € | ||||||||
| Research and development | 541,667 € | 133,333 € | 675,000 € | ||||||||
| Other expenses | 100,000 € | 25,000 € | 125,000 € | ||||||||
| Total SG&A | 15,433,788 € | 2,325,810 € | 17,759,598 € | ||||||||
| Operating Income | 9,272,140 € | 232,813 € | 9,504,952 € | ||||||||
| 10,000,000 € Stretch for double bonus | |||||||||||
TN2-Requirement 5
| Toomer's Energy Drinks | Identifies cells that can be manipulated to see how changes affect operating income | ||||||||||||
| Budgeted Income Statement (Actual: October 31, 2016) | |||||||||||||
| 2016 | |||||||||||||
| Actual Results as of October 31, | Budget for November & December | Projection for year ended December 31 | |||||||||||
| Revenue | Unit Rev | Units | Unit Rev | Units | Unit Rev | Units | |||||||
| 250-Strawberry | 1.990 € | 2,995,194 | 5,960,436 € | 1.990 € | 300,000 | 597,000 € | 1.990 € | 3,295,194 | 6,557,436 € | Increase sales Nov/Dec by: | 0.0% | ||
| 250-Lemon | 1.990 € | 3,195,983 | 6,360,006 € | 1.990 € | 340,000 | 676,600 € | 1.990 € | 3,535,983 | 7,036,606 € | ||||
| 250-Grape | 1.990 € | 2,673,241 | 5,319,750 € | 1.990 € | 280,000 | 557,200 € | 1.990 € | 2,953,241 | 5,876,950 € | ||||
| Total 250ml | 8,864,418 | 17,640,192 € | 920,000 | 1,830,800 € | 9,784,418 | 19,470,992 € | |||||||
| 500-Strawberry | 3.490 € | 2,325,643 | 8,116,494 € | 3.490 € | 245,000 | 855,050 € | 3.490 € | 2,570,643 | 8,971,544 € | ||||
| 500-Lemon | 3.490 € | 2,356,456 | 8,224,031 € | 3.490 € | 275,000 | 959,750 € | 3.490 € | 2,631,456 | 9,183,781 € | ||||
| 500-Grape | 3.490 € | 2,337,362 | 8,157,393 € | 3.490 € | 230,000 | 802,700 € | 3.490 € | 2,567,362 | 8,960,093 € | ||||
| Total 500ml | 7,019,461 | 24,497,919 € | 750,000 | 2,617,500 € | 7,769,461 | 27,115,419 € | |||||||
| Total Gross Revenue | 15,883,879 | 42,138,111 € | 1,670,000 | 4,448,300 € | 17,553,879 | 46,586,411 € | |||||||
| Less, Bad Debt Expenses | 2.50% | 1,053,453 € | 2.50% | 111,208 € | 2.50% | 1,164,660 € | |||||||
| Net Revenue | 41,084,658 € | 4,337,093 € | 45,421,750 € | ||||||||||
| Cost of Goods Sold | Unit Cost | Units | Unit Cost | Units | Unit Cost | Units | |||||||
| Beginning Finished Goods Inventory | |||||||||||||
| 250-Strawberry | 0.740 € | 52,500 | 38,850 € | 0.740 € | 52,500 | 38,850 € | 0.740 € | 52,500 | 38,850 € | ||||
| 250-Lemon | 0.765 € | 53,258 | 40,742 € | 0.765 € | 53,258 | 40,742 € | 0.765 € | 53,258 | 40,742 € | ||||
| 250-Grape | 0.715 € | 45,250 | 32,354 € | 0.715 € | 45,250 | 32,354 € | 0.715 € | 45,250 | 32,354 € | ||||
| Total 250ml | 151,008 | 111,946 € | 151,008 | 111,946 € | 151,008 | 111,946 € | |||||||
| 500-Strawberry | 1.464 € | 33,125 | 48,495 € | 1.464 € | 33,125 | 48,495 € | 1.464 € | 33,125 | 48,495 € | ||||
| 500-Lemon | 1.500 € | 34,250 | 51,375 € | 1.500 € | 34,250 | 51,375 € | 1.500 € | 34,250 | 51,375 € | ||||
| 500-Grape | 1.413 € | 31,582 | 44,625 € | 1.413 € | 31,582 | 44,625 € | 1.413 € | 31,582 | 44,625 € | ||||
| Total 500ml | 98,957 | 144,495 € | 98,957 | 144,495 € | 98,957 | 144,495 € | |||||||
| Total Beginning Finished Goods Inventory | 249,965 | 256,441 € | 249,965 | 256,441 € | 249,965 | 256,441 € | |||||||
| Plus, Cost of Goods Manufactured | |||||||||||||
| 250-Strawberry | 0.740 € | 2,995,194 | 2,216,444 € | 0.740 € | 300,000 | 222,000 € | 0.740 € | 3,295,194 | 2,438,444 € | Increase production by: | 0.0% | ||
| 250-Lemon | 0.765 € | 3,195,983 | 2,444,927 € | 0.765 € | 340,000 | 260,100 € | 0.765 € | 3,535,983 | 2,705,027 € | Note: cell N35 will increase units produced in excess of sales volume. | |||
| 250-Grape | 0.715 € | 2,673,241 | 1,911,367 € | 0.715 € | 280,000 | 200,200 € | 0.715 € | 2,953,241 | 2,111,567 € | ||||
| Total 250ml | 8,864,418 | 6,572,738 € | 920,000 | 682,300 € | 9,784,418 | 7,255,038 € | |||||||
| 500-Strawberry | 1.464 € | 2,325,643 | 3,404,741 € | 1.464 € | 245,000 | 358,680 € | 1.464 € | 2,570,643 | 3,763,421 € | ||||
| 500-Lemon | 1.500 € | 2,356,456 | 3,534,684 € | 1.500 € | 275,000 | 412,500 € | 1.500 € | 2,631,456 | 3,947,184 € | ||||
| 500-Grape | 1.413 € | 2,337,362 | 3,302,693 € | 1.413 € | 230,000 | 324,990 € | 1.413 € | 2,567,362 | 3,627,683 € | ||||
| Total 500ml | 7,019,461 | 10,242,118 € | 750,000 | 1,096,170 € | 7,769,461 | 11,338,288 € | |||||||
| Total Cost of Goods Manufactured | 15,883,879 | 16,814,856 € | 1,670,000 | 1,778,470 € | 17,553,879 | 18,593,326 € | |||||||
| Less, Ending Finished Goods Inventory | |||||||||||||
| 250-Strawberry | 0.740 € | 52,500 | 38,850 € | 0.740 € | 52,500 | 38,850 € | 0.740 € | 52,500 | 38,850 € | ||||
| 250-Lemon | 0.765 € | 53,258 | 40,742 € | 0.765 € | 53,258 | 40,742 € | 0.765 € | 53,258 | 40,742 € | ||||
| 250-Grape | 0.715 € | 45,250 | 32,354 € | 0.715 € | 45,250 | 32,354 € | 0.715 € | 45,250 | 32,354 € | ||||
| Total 250ml | 151,008 | 111,946 € | 151,008 | 111,946 € | 151,008 | 111,946 € | |||||||
| 500-Strawberry | 1.464 € | 33,125 | 48,495 € | 1.464 € | 33,125 | 48,495 € | 1.464 € | 33,125 | 48,495 € | ||||
| 500-Lemon | 1.500 € | 34,250 | 51,375 € | 1.500 € | 34,250 | 51,375 € | 1.500 € | 34,250 | 51,375 € | ||||
| 500-Grape | 1.413 € | 31,582 | 44,625 € | 1.413 € | 31,582 | 44,625 € | 1.413 € | 31,582 | 44,625 € | ||||
| Total 500ml | 98,957 | 144,495 € | 98,957 | 144,495 € | 98,957 | 144,495 € | |||||||
| Total Ending Finished Goods Inventory | 249,965 | 256,441 € | 249,965 | 256,441 € | 249,965 | 256,441 € | |||||||
| Cost of Goods Sold | |||||||||||||
| 250-Strawberry | 0.740 € | 2,995,194 | 2,216,444 € | 0.740 € | 300,000 | 222,000 € | 0.740 € | 3,295,194 | 2,438,444 € | ||||
| 250-Lemon | 0.765 € | 3,195,983 | 2,444,927 € | 0.765 € | 340,000 | 260,100 € | 0.765 € | 3,535,983 | 2,705,027 € | ||||
| 250-Grape | 0.715 € | 2,673,241 | 1,911,367 € | 0.715 € | 280,000 | 200,200 € | 0.715 € | 2,953,241 | 2,111,567 € | ||||
| Total 250ml | 8,864,418 | 6,572,738 € | 920,000 | 682,300 € | 9,784,418 | 7,255,038 € | |||||||
| 500-Strawberry | 1.464 € | 2,325,643 | 3,404,741 € | 1.464 € | 245,000 | 358,680 € | 1.464 € | 2,570,643 | 3,763,421 € | ||||
| 500-Lemon | 1.500 € | 2,356,456 | 3,534,684 € | 1.500 € | 275,000 | 412,500 € | 1.500 € | 2,631,456 | 3,947,184 € | ||||
| 500-Grape | 1.413 € | 2,337,362 | 3,302,693 € | 1.413 € | 230,000 | 324,990 € | 1.413 € | 2,567,362 | 3,627,683 € | ||||
| Total 500ml | 7,019,461 | 10,242,118 € | 750,000 | 1,096,170 € | 7,769,461 | 11,338,288 € | |||||||
| Cost of Goods Sold before standard cost variance | 15,883,879 | 16,814,856 € | 1,670,000 | 1,778,470 € | 17,553,879 | 18,593,326 € | |||||||
| Fixed overhead variance (overapplied) underapplied | - 436,125 € | 0 | - 436,125 € | ||||||||||
| Total Cost of Goods Sold | 15,883,879 | 16,378,730 € | 1,670,000 | 1,778,470 € | 17,553,879 | 18,157,200 € | |||||||
| Gross Profit (includes bad debt expense and fixed overhead variance) | |||||||||||||
| 250-Strawberry | 1.200 € | 2,995,194 | 3,594,982 € | 1.200 € | 300,000 | 360,075 € | 1.200 € | 3,295,194 | 3,955,057 € | ||||
| 250-Lemon | 1.175 € | 3,195,983 | 3,756,079 € | 1.175 € | 340,000 | 399,585 € | 1.175 € | 3,535,983 | 4,155,664 € | ||||
| 250-Grape | 1.225 € | 2,673,241 | 3,275,389 € | 1.225 € | 280,000 | 343,070 € | 1.225 € | 2,953,241 | 3,618,459 € | ||||
| Total 250ml | 8,864,418 | 10,626,449 € | 920,000 | 1,102,730 € | 9,784,418 | 11,729,179 € | |||||||
| 500-Strawberry | 1.939 € | 2,325,643 | 4,508,840 € | 1.939 € | 245,000 | 474,994 € | 1.939 € | 2,570,643 | 4,983,834 € | ||||
| 500-Lemon | 1.903 € | 2,356,456 | 4,483,747 € | 1.903 € | 275,000 | 523,256 € | 1.903 € | 2,631,456 | 5,007,003 € | ||||
| 500-Grape | 1.990 € | 2,337,362 | 4,650,766 € | 1.990 € | 230,000 | 457,643 € | 1.990 € | 2,567,362 | 5,108,409 € | ||||
| Total 500ml | 7,019,461 | 13,643,353 € | 750,000 | 1,455,893 € | 7,769,461 | 15,099,246 € | |||||||
| Fixed overhead variance overapplied (underapplied) | 436,125.38 € | - 0 € | 436,125.38 € | ||||||||||
| Total Gross Profit | 15,883,879 | 24,705,928 € | 1,670,000 | 2,558,623 € | 17,553,879 | 27,264,550 € | |||||||
| Less, Selling, General and Administrative Expenses | |||||||||||||
| Commissions | 2.0% | of net sales | 821,693 € | 2.0% | of net sales | 86,742 € | 2.0% | of net sales | 908,435 € | ||||
| Advertising | 4,853,000 € | 375,000 € | 5,228,000 € | ||||||||||
| Salaries and wages | 4,642,539 € | 887,508 € | 5,530,047 € | ||||||||||
| Depreciation and amortization | 1,916,667 € | 403,333 € | 2,320,000 € | ||||||||||
| Maintenance and repairs | 404,025 € | 86,805 € | 490,830 € | ||||||||||
| Freight and logistics | 2.777% | of net sales | 1,141,006 € | 2.777% | of net sales | 120,450 € | 2.777% | of net sales | 1,261,456 € | ||||
| Office expenses | 179,858 € | 40,972 € | 220,830 € | ||||||||||
| Office rent | 833,333 € | 166,667 € | 1,000,000 € | ||||||||||
| Research and development | 541,667 € | 133,333 € | 675,000 € | ||||||||||
| Other expenses | 100,000 € | 25,000 € | 125,000 € | ||||||||||
| Total SG&A | 15,433,788 € | 2,325,810 € | 17,759,598 € | ||||||||||
| Operating Income | 9,272,140 € | 232,813 € | 9,504,952 € | ||||||||||
| $ 10,000,000 | Stretch for double bonus | ||||||||||||
Financial Data-PeanutButter2
| Tiger Energy Drink | |||||||||||||||||||||||
| Budgeted Income Statement | Only November/December product cost are changed; annual projections are a blended rate--not valid | ||||||||||||||||||||||
| 2016 | |||||||||||||||||||||||
| Actual Results as of October 31, | Budget for month ended December 31, | Projection for year ended December 31 | |||||||||||||||||||||
| Revenue | Unit Cost | Units | Unit Cost | Units | Unit Cost | Units | |||||||||||||||||
| 250-Strawberry | $ 1.490 | 1,666,667 | $ 2,483,334 | $ 1.490 | 333,333 | $ 496,666 | $ 1.490 | 2,000,000 | $ 2,980,000 | Increase sales Nov/Dec by: | 0.0% | ||||||||||||
| 250-Lemon | $ 1.490 | 2,000,000 | $ 2,980,000 | $ 1.490 | 400,000 | 596,000 | $ 1.490 | 2,400,000 | 3,576,000 | ||||||||||||||
| 250-Rasberry | $ 1.490 | 1,208,334 | $ 1,800,418 | $ 1.490 | 241,666 | 360,082 | $ 1.490 | 1,450,000 | 2,160,500 | ||||||||||||||
| 250-Melon | $ 1.490 | 1,500,000 | $ 2,235,000 | $ 1.490 | 300,000 | 447,000 | $ 1.490 | 1,800,000 | 2,682,000 | ||||||||||||||
| 250-Blueberry | $ 1.490 | 791,667 | $ 1,179,584 | $ 1.490 | 158,333 | 235,916 | $ 1.490 | 950,000 | 1,415,500 | ||||||||||||||
| Total 250ml | 7,166,668 | $ 10,678,335 | 1,433,332 | $ 2,135,665 | 8,600,000 | $ 12,814,000 | |||||||||||||||||
| 500-Strawberry | $ 2.490 | 1,208,334 | $ 3,008,752 | $ 2.490 | 241,666 | 601,748 | $ 2.490 | 1,450,000 | 3,610,500 | ||||||||||||||
| 500-Lemon | $ 2.490 | 1,458,334 | $ 3,631,252 | $ 2.490 | 291,666 | 726,248 | $ 2.490 | 1,750,000 | 4,357,500 | ||||||||||||||
| 500-Rasberry | $ 2.490 | 925,000 | $ 2,303,250 | $ 2.490 | 185,000 | 460,650 | $ 2.490 | 1,110,000 | 2,763,900 | ||||||||||||||
| 500-Melon | $ 2.490 | 1,083,334 | $ 2,697,502 | $ 2.490 | 216,666 | 539,498 | $ 2.490 | 1,300,000 | 3,237,000 | ||||||||||||||
| 500-Blueberry | $ 2.490 | 541,667 | $ 1,348,751 | $ 2.490 | 108,333 | 269,749 | $ 2.490 | 650,000 | 1,618,500 | ||||||||||||||
| Total 500ml | 5,216,669 | $ 12,989,506 | 1,043,331 | $ 2,597,894 | 6,260,000 | $ 15,587,400 | |||||||||||||||||
| 750-Strawberry | $ 3.390 | 791,667 | $ 2,683,751 | $ 3.390 | 158,333 | 536,749 | $ 3.390 | 950,000 | 3,220,500 | ||||||||||||||
| 750-Lemon | $ 3.390 | 933,334 | $ 3,164,002 | $ 3.390 | 186,666 | 632,798 | $ 3.390 | 1,120,000 | 3,796,800 | ||||||||||||||
| 750-Rasberry | $ 3.390 | 683,334 | $ 2,316,502 | $ 3.390 | 136,666 | 463,298 | $ 3.390 | 820,000 | 2,779,800 | ||||||||||||||
| 750-Melon | $ 3.390 | 725,000 | $ 2,457,750 | $ 3.390 | 145,000 | 491,550 | $ 3.390 | 870,000 | 2,949,300 | ||||||||||||||
| 750-Blueberry | $ 3.390 | 316,667 | $ 1,073,501 | $ 3.390 | 63,333 | 214,699 | $ 3.390 | 380,000 | 1,288,200 | ||||||||||||||
| Total 750ml | 3,450,002 | $ 11,695,507 | 689,998 | $ 2,339,093 | 4,140,000 | $ 14,034,600 | |||||||||||||||||
| Total Gross Revenue | 15,833,339 | $ 35,363,348 | 3,166,661 | 7,072,652 | 19,000,000 | 42,436,000 | |||||||||||||||||
| Less, Bad Debt Expenses | 2.50% | $ 884,084 | 2.50% | 176,816 | 2.50% | 1,060,900 | Have used 2.5% for last two years; however, actual bad debts have ranged from 1% to 4% over last 10 years | ||||||||||||||||
| Net Revenue | $ 34,479,264 | $ 6,895,836 | $ 41,375,100 | ||||||||||||||||||||
| Cost of Goods Sold | Units | Units | Units | ||||||||||||||||||||
| Beginning Finished Goods Inventory | |||||||||||||||||||||||
| 250-Strawberry | $ 0.631 | 202,500 | $ 127,778 | $ 0.631 | 202,500 | $ 127,778 | $ 0.631 | 202,500 | $ 127,778 | ||||||||||||||
| 250-Lemon | $ 0.655 | 233,258 | 152,784 | $ 0.655 | 233,258 | 152,784 | $ 0.655 | 233,258 | 152,784 | ||||||||||||||
| 250-Rasberry | $ 0.642 | 131,008 | 84,107 | $ 0.642 | 131,008 | 84,107 | $ 0.642 | 131,008 | 84,107 | ||||||||||||||
| 250-Melon | $ 0.605 | 175,250 | 106,026 | $ 0.605 | 175,250 | 106,026 | $ 0.605 | 175,250 | 106,026 | ||||||||||||||
| 250-Blueberry | $ 0.640 | 92,574 | 59,247 | $ 0.640 | 92,574 | 59,247 | $ 0.640 | 92,574 | 59,247 | ||||||||||||||
| Total 250ml | 834,590 | $ 529,942 | 834,590 | $ 529,942 | 834,590 | $ 529,942 | |||||||||||||||||
| 500-Strawberry | $ 1.246 | 123,125 | 153,414 | $ 1.246 | 123,125 | 153,414 | $ 1.246 | 123,125 | 153,414 | ||||||||||||||
| 500-Lemon | $ 1.282 | 123,250 | 158,007 | $ 1.282 | 123,250 | 158,007 | $ 1.282 | 123,250 | 158,007 | ||||||||||||||
| 500-Rasberry | $ 1.248 | 103,568 | 129,253 | $ 1.248 | 103,568 | 129,253 | $ 1.248 | 103,568 | 129,253 | ||||||||||||||
| 500-Melon | $ 1.195 | 131,582 | 157,240 | $ 1.195 | 131,582 | 157,240 | $ 1.195 | 131,582 | 157,240 | ||||||||||||||
| 500-Blueberry | $ 1.241 | 68,536 | 85,053 | $ 1.241 | 68,536 | 85,053 | $ 1.241 | 68,536 | 85,053 | ||||||||||||||
| Total 500ml | 550,061 | $ 682,967 | 550,061 | $ 682,967 | 550,061 | $ 682,967 | |||||||||||||||||
| 750-Strawberry | $ 1.826 | 78,132 | 142,669 | $ 1.826 | 78,132 | 142,669 | $ 1.826 | 78,132 | 142,669 | ||||||||||||||
| 750-Lemon | $ 1.879 | 89,500 | 168,171 | $ 1.879 | 89,500 | 168,171 | $ 1.879 | 89,500 | 168,171 | ||||||||||||||
| 750-Rasberry | $ 1.830 | 53,852 | 98,549 | $ 1.830 | 53,852 | 98,549 | $ 1.830 | 53,852 | 98,549 | ||||||||||||||
| 750-Melon | $ 1.742 | 79,893 | 139,174 | $ 1.742 | 79,893 | 139,174 | $ 1.742 | 79,893 | 139,174 | ||||||||||||||
| 750-Blueberry | $ 1.821 | 36,984 | 67,348 | $ 1.821 | 36,984 | 67,348 | $ 1.821 | 36,984 | 67,348 | ||||||||||||||
| Total 750ml | 338,361 | $ 615,910 | 338,361 | $ 615,910 | 338,361 | $ 615,910 | |||||||||||||||||
| Total Beginning Finished Goods Inventory | 1,723,012 | 1,828,819 | 1,723,012 | $ 1,828,819 | 1,723,012 | $ 1,828,819 | |||||||||||||||||
| Plus, Cost of Goods Manufactured | VC | FMOH | Total cost | Annual Budget | Actual through Nov | Budget | |||||||||||||||||
| 250-Strawberry | $ 0.631 | 1,666,667 | $ 1,051,667 | $ 0.627 | 366,666 | $ 229,900 | $ 0.630 | 2,033,333 | $ 1,281,566 | Increase production by: | 10.0% | Budgeted Production | |||||||||||
| 250-Lemon | $ 0.655 | 2,000,000 | 1,310,000 | $ 0.651 | 440,000 | 286,440 | $ 0.654 | 2,440,000 | 1,596,440 | 250-Strawberry | $ 0.351 | $ 0.280 | $ 0.631 | 2,000,000 | 1,666,667 | 333,333 | |||||||
| 250-Rasberry | $ 0.642 | 1,208,334 | 775,750 | $ 0.638 | 265,833 | 169,601 | $ 0.641 | 1,474,167 | 945,352 | 250-Lemon | $ 0.375 | $ 0.280 | $ 0.655 | 2,400,000 | 2,000,000 | 400,000 | |||||||
| 250-Melon | $ 0.605 | 1,500,000 | 907,500 | $ 0.601 | 330,000 | 198,330 | $ 0.604 | 1,830,000 | 1,105,830 | 250-Rasberry | $ 0.362 | $ 0.280 | $ 0.642 | 1,450,000 | 1,208,334 | 241,666 | |||||||
| 250-Blueberry | $ 0.640 | 791,667 | 506,667 | $ 0.636 | 174,166 | 110,770 | $ 0.639 | 965,833 | 617,436 | 250-Melon | $ 0.325 | $ 0.280 | $ 0.605 | 1,800,000 | 1,500,000 | 300,000 | |||||||
| Total 250ml | 7,166,668 | $ 4,551,584 | 1,576,665 | $ 995,041 | 8,743,333 | $ 5,546,625 | 435,160 | 250-Blueberry | $ 0.360 | $ 0.280 | $ 0.640 | 950,000 | 791,667 | 158,333 | |||||||||
| Total 250ml | 8,600,000 | 7,166,668 | 1,433,332 | ||||||||||||||||||||
| 500-Strawberry | $ 1.246 | 1,208,334 | 1,505,584 | $ 1.236 | 265,833 | 328,570 | $ 1.244 | 1,474,167 | 1,834,154 | ||||||||||||||
| 500-Lemon | $ 1.282 | 1,458,334 | 1,869,584 | $ 1.272 | 320,833 | 408,100 | $ 1.280 | 1,779,167 | 2,277,684 | 500-Strawberry | $ 0.685 | $ 0.561 | $ 1.246 | 1,450,000 | 1,208,334 | 241,666 | |||||||
| 500-Rasberry | $ 1.248 | 925,000 | 1,154,400 | $ 1.238 | 203,500 | 251,933 | $ 1.246 | 1,128,500 | 1,406,333 | 500-Lemon | $ 0.721 | $ 0.561 | $ 1.282 | 1,750,000 | 1,458,334 | 291,666 | |||||||
| 500-Melon | $ 1.195 | 1,083,334 | 1,294,584 | $ 1.185 | 238,333 | 282,425 | $ 1.193 | 1,321,667 | 1,577,009 | 500-Rasberry | $ 0.687 | $ 0.561 | $ 1.248 | 1,110,000 | 925,000 | 185,000 | |||||||
| 500-Blueberry | $ 1.241 | 541,667 | 672,209 | $ 1.231 | 119,166 | 146,693 | $ 1.239 | 660,833 | 818,902 | 500-Melon | $ 0.634 | $ 0.561 | $ 1.195 | 1,300,000 | 1,083,334 | 216,666 | |||||||
| Total 500ml | 5,216,669 | $ 6,496,361 | 1,147,665 | $ 1,417,720 | 6,364,334 | $ 7,914,081 | 632,363 | 500-Blueberry | $ 0.680 | $ 0.561 | $ 1.241 | 650,000 | 541,667 | 108,333 | |||||||||
| Total 500ml | 6,260,000 | 5,216,669 | 1,043,331 | ||||||||||||||||||||
| 750-Strawberry | $ 1.826 | 791,667 | 1,445,584 | $ 1.812 | 174,166 | 315,589 | $ 1.823 | 965,833 | 1,761,173 | ||||||||||||||
| 750-Lemon | $ 1.879 | 933,334 | 1,753,735 | $ 1.865 | 205,333 | 382,946 | $ 1.876 | 1,138,667 | 2,136,681 | 750-Strawberry | $ 0.985 | $ 0.841 | $ 1.826 | 950,000 | 791,667 | 158,333 | |||||||
| 750-Rasberry | $ 1.830 | 683,334 | 1,250,501 | $ 1.816 | 150,333 | 273,005 | $ 1.827 | 833,667 | 1,523,506 | 750-Lemon | $ 1.038 | $ 0.841 | $ 1.879 | 1,120,000 | 933,334 | 186,666 | |||||||
| 750-Melon | $ 1.742 | 725,000 | 1,262,950 | $ 1.728 | 159,500 | 275,616 | $ 1.739 | 884,500 | 1,538,566 | 750-Rasberry | $ 0.989 | $ 0.841 | $ 1.830 | 820,000 | 683,334 | 136,666 | |||||||
| 750-Blueberry | $ 1.821 | 316,667 | 576,651 | $ 1.807 | 69,666 | 125,886 | $ 1.818 | 386,333 | 702,537 | 750-Melon | $ 0.901 | $ 0.841 | $ 1.742 | 870,000 | 725,000 | 145,000 | |||||||
| Total 750ml | 3,450,002 | $ 6,289,420 | 758,998 | $ 1,373,042 | 4,209,000 | $ 7,662,462 | 627,691 | 750-Blueberry | $ 0.980 | $ 0.841 | $ 1.821 | 380,000 | 316,667 | 63,333 | |||||||||
| Total Cost of Goods Manufactured | 15,833,339 | 17,337,366 | 3,483,328 | $ 3,785,803 | 19,316,667 | $ 21,123,169 | $ 1,695,214 | FMOH in COGM (Nov/Dec) | Total 750ml | 4,140,000 | 3,450,002 | 689,998 | |||||||||||
| Total budgeted production | 19,000,000 | 15,833,339 | 3,166,661 | ||||||||||||||||||||
| Less, Ending Finished Goods Inventory | |||||||||||||||||||||||
| 250-Strawberry | $ 0.631 | 202,500 | $ 127,778 | $ 0.627 | 235,833 | $ 147,867 | $ 0.630 | 235,833 | $ 148,641 | ||||||||||||||
| 250-Lemon | $ 0.655 | 233,258 | 152,784 | $ 0.651 | 273,258 | 177,891 | $ 0.654 | 273,258 | 178,787 | ||||||||||||||
| 250-Rasberry | $ 0.642 | 131,008 | 84,107 | $ 0.638 | 155,175 | 99,002 | $ 0.641 | 155,175 | 99,510 | Budgeted | |||||||||||||
| 250-Melon | $ 0.605 | 175,250 | 106,026 | $ 0.601 | 205,250 | 123,355 | $ 0.604 | 205,250 | 124,028 | Bottle Size | Original Forcast | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | ||||||||
| 250-Blueberry | $ 0.640 | 92,574 | 59,247 | $ 0.636 | 108,407 | 68,947 | $ 0.639 | 108,407 | 69,302 | 250 ml | 8,600,000 | 2,150,000,000 | 25.64% | $ 2,410,256 | $0.280 | ||||||||
| Total 250ml | 834,590 | $ 529,942 | 977,923 | $ 617,062 | 977,923 | $ 620,268 | 39,560 | 500 ml | 6,260,000 | 3,130,000,000 | 37.33% | $ 3,508,885 | $0.561 | ||||||||||
| 750 ml | 4,140,000 | 3,105,000,000 | 37.03% | $ 3,480,859 | $0.841 | ||||||||||||||||||
| 500-Strawberry | $ 1.246 | 123,125 | 153,414 | $ 1.236 | 147,292 | 182,053 | $ 1.244 | 147,292 | 183,260 | Total | 8,385,000,000 | $ 9,400,000 | |||||||||||
| 500-Lemon | $ 1.282 | 123,250 | 158,007 | $ 1.272 | 152,417 | 193,874 | $ 1.280 | 152,417 | 195,124 | ||||||||||||||
| 500-Rasberry | $ 1.248 | 103,568 | 129,253 | $ 1.238 | 122,068 | 151,120 | $ 1.246 | 122,068 | 152,121 | Overhead allocated through November 30, 2016 | $ 7,834,670 | ||||||||||||
| 500-Melon | $ 1.195 | 131,582 | 157,240 | $ 1.185 | 153,249 | 181,600 | $ 1.193 | 153,249 | 182,856 | Overhead budgeted for December 31, 2016 | $ 1,565,330 | $ 9,400,000 | |||||||||||
| 500-Blueberry | $ 1.241 | 68,536 | 85,053 | $ 1.231 | 79,369 | 97,703 | $ 1.239 | 79,369 | 98,354 | ||||||||||||||
| Total 500ml | 550,061 | $ 682,967 | 654,395 | $ 806,351 | 654,395 | $ 811,715 | 57,488 | Diff b/t FMOH in COGM & Add'l in FG | $ 1,541,103 | ||||||||||||||
| ??? | $ 24,227 | ||||||||||||||||||||||
| 750-Strawberry | $ 1.826 | 78,132 | 142,669 | $ 1.812 | 93,965 | 170,265 | $ 1.823 | 93,965 | 171,343 | ||||||||||||||
| 750-Lemon | $ 1.879 | 89,500 | 168,171 | $ 1.865 | 108,167 | 201,731 | $ 1.876 | 108,167 | 202,973 | ||||||||||||||
| 750-Rasberry | $ 1.830 | 53,852 | 98,549 | $ 1.816 | 67,519 | 122,615 | $ 1.827 | 67,519 | 123,389 | ||||||||||||||
| 750-Melon | $ 1.742 | 79,893 | 139,174 | $ 1.728 | 94,393 | 163,111 | $ 1.739 | 94,393 | 164,194 | What If: | |||||||||||||
| 750-Blueberry | $ 1.821 | 36,984 | 67,348 | $ 1.807 | 43,317 | 78,274 | $ 1.818 | 43,317 | 78,771 | Bottle Size | Revised Forecast | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | ||||||||
| Total 750ml | 338,361 | $ 615,910 | 407,361 | $ 735,995 | 407,361 | $ 740,670 | 57,063 | 250 ml | 8,743,333 | 2,185,833,250 | 25.64% | $ 2,410,256 | $0.276 | ||||||||||
| Total Ending Finished Goods Inventory | 1,723,012 | 1,828,819 | 2,039,679 | $ 2,159,408 | 2,039,679 | $ 2,172,653 | 154,111 | Add'l FMOH in FG (NOV/DEC) | 500 ml | 6,364,334 | 3,182,167,000 | 37.33% | $ 3,508,885 | $0.551 | |||||||||
| 750 ml | 4,209,000 | 3,156,750,000 | 37.03% | $ 3,480,859 | $0.827 | ||||||||||||||||||
| Cost of Goods Sold | Total | 8,524,750,250 | $ 9,400,000 | ||||||||||||||||||||
| 250-Strawberry | $ 0.631 | 1,666,667 | $ 1,051,667 | $ 0.629 | 333,333 | $ 209,810 | $ 0.630 | 2,000,000 | $ 1,260,703 | ||||||||||||||
| 250-Lemon | $ 0.655 | 2,000,000 | $ 1,310,000 | $ 0.653 | 400,000 | $ 261,333 | $ 0.654 | 2,400,000 | $ 1,570,437 | ||||||||||||||
| 250-Rasberry | $ 0.642 | 1,208,334 | $ 775,750 | $ 0.640 | 241,666 | $ 154,707 | $ 0.641 | 1,450,000 | $ 929,949 | ||||||||||||||
| 250-Melon | $ 0.605 | 1,500,000 | $ 907,500 | $ 0.603 | 300,000 | $ 181,001 | $ 0.604 | 1,800,000 | $ 1,087,828 | ||||||||||||||
| 250-Blueberry | $ 0.640 | 791,667 | $ 506,667 | $ 0.638 | 158,333 | $ 101,070 | $ 0.639 | 950,000 | $ 607,382 | ||||||||||||||
| Total 250ml | 7,166,668 | $ 4,551,584 | 1,433,332 | $ 907,921 | 8,600,000 | $ 5,456,299 | |||||||||||||||||
| 500-Strawberry | $ 1.246 | 1,208,334 | $ 1,505,584 | $ 1.241 | 241,666 | $ 299,930 | $ 1.244 | 1,450,000 | $ 1,804,307 | ||||||||||||||
| 500-Lemon | $ 1.282 | 1,458,334 | $ 1,869,584 | $ 1.276 | 291,666 | $ 372,232 | $ 1.280 | 1,750,000 | $ 2,240,567 | ||||||||||||||
| 500-Rasberry | $ 1.248 | 925,000 | $ 1,154,400 | $ 1.244 | 185,000 | $ 230,066 | $ 1.246 | 1,110,000 | $ 1,383,465 | ||||||||||||||
| 500-Melon | $ 1.195 | 1,083,334 | $ 1,294,584 | $ 1.191 | 216,666 | $ 258,065 | $ 1.193 | 1,300,000 | $ 1,551,393 | ||||||||||||||
| 500-Blueberry | $ 1.241 | 541,667 | $ 672,209 | $ 1.237 | 108,333 | $ 134,043 | $ 1.239 | 650,000 | $ 805,601 | ||||||||||||||
| Total 500ml | 5,216,669 | $ 6,496,361 | 1,043,331 | $ 1,294,336 | 6,260,000 | $ 7,785,333 | |||||||||||||||||
| 750-Strawberry | $ 1.826 | 791,667 | $ 1,445,584 | $ 1.819 | 158,333 | $ 287,993 | $ 1.824 | 950,000 | $ 1,732,499 | ||||||||||||||
| 750-Lemon | $ 1.879 | 933,334 | $ 1,753,735 | $ 1.872 | 186,666 | $ 349,385 | $ 1.877 | 1,120,000 | $ 2,101,878 | ||||||||||||||
| 750-Rasberry | $ 1.830 | 683,334 | $ 1,250,501 | $ 1.822 | 136,666 | $ 248,939 | $ 1.828 | 820,000 | $ 1,498,666 | ||||||||||||||
| 750-Melon | $ 1.742 | 725,000 | $ 1,262,950 | $ 1.736 | 145,000 | $ 251,679 | $ 1.740 | 870,000 | $ 1,513,545 | ||||||||||||||
| 750-Blueberry | $ 1.821 | 316,667 | $ 576,651 | $ 1.815 | 63,333 | $ 114,961 | $ 1.819 | 380,000 | $ 691,114 | ||||||||||||||
| Total 750ml | 3,450,002 | $ 6,289,420 | 689,998 | $ 1,252,957 | 4,140,000 | $ 7,537,702 | |||||||||||||||||
| Total Cost of Goods Sold | 15,833,339 | 17,337,366 | 3,166,661 | $ 3,455,214 | 19,000,000 | $ 20,779,335 | |||||||||||||||||
| Gross Profit | |||||||||||||||||||||||
| 250-Strawberry | $ 0.822 | 1,666,667 | $ 1,369,584 | $ 0.823 | 333,333 | $ 274,440 | $ 0.822 | 2,000,000 | $ 1,644,797 | ||||||||||||||
| 250-Lemon | $ 0.798 | 2,000,000 | 1,595,500 | $ 0.799 | 400,000 | 319,767 | $ 0.798 | 2,400,000 | 1,916,163 | ||||||||||||||
| 250-Rasberry | $ 0.811 | 1,208,334 | 979,657 | $ 0.813 | 241,666 | 196,373 | $ 0.811 | 1,450,000 | 1,176,539 | ||||||||||||||
| 250-Melon | $ 0.848 | 1,500,000 | 1,271,625 | $ 0.849 | 300,000 | 254,824 | $ 0.848 | 1,800,000 | 1,527,122 | ||||||||||||||
| 250-Blueberry | $ 0.813 | 791,667 | 643,427 | $ 0.814 | 158,333 | 128,948 | $ 0.813 | 950,000 | 772,731 | ||||||||||||||
| Total 250ml | 7,166,668 | $ 5,859,793 | 1,433,332 | $ 1,174,352 | 8,600,000 | $ 7,037,351 | |||||||||||||||||
| 500-Strawberry | $ 1.182 | 1,208,334 | 1,427,949 | $ 1.187 | 241,666 | 286,774 | $ 1.183 | 1,450,000 | 1,715,930 | ||||||||||||||
| 500-Lemon | $ 1.146 | 1,458,334 | 1,670,886 | $ 1.152 | 291,666 | 335,860 | $ 1.147 | 1,750,000 | 2,007,996 | ||||||||||||||
| 500-Rasberry | $ 1.180 | 925,000 | 1,091,269 | $ 1.184 | 185,000 | 219,068 | $ 1.181 | 1,110,000 | 1,311,337 | ||||||||||||||
| 500-Melon | $ 1.233 | 1,083,334 | 1,335,480 | $ 1.237 | 216,666 | 267,946 | $ 1.234 | 1,300,000 | 1,604,682 | ||||||||||||||
| 500-Blueberry | $ 1.187 | 541,667 | 642,823 | $ 1.190 | 108,333 | 128,962 | $ 1.188 | 650,000 | 772,436 | ||||||||||||||
| Total 500ml | 5,216,669 | $ 6,168,407 | 1,043,331 | $ 1,238,611 | 6,260,000 | $ 7,412,382 | |||||||||||||||||
| 750-Strawberry | $ 1.479 | 791,667 | 1,171,073 | $ 1.486 | 158,333 | 235,337 | $ 1.482 | 950,000 | 1,407,489 | ||||||||||||||
| 750-Lemon | $ 1.426 | 933,334 | 1,331,168 | $ 1.434 | 186,666 | 267,593 | $ 1.429 | 1,120,000 | 1,600,002 | ||||||||||||||
| 750-Rasberry | $ 1.475 | 683,334 | 1,008,088 | $ 1.484 | 136,666 | 202,776 | $ 1.478 | 820,000 | 1,211,639 | ||||||||||||||
| 750-Melon | $ 1.563 | 725,000 | 1,133,356 | $ 1.570 | 145,000 | 227,583 | $ 1.566 | 870,000 | 1,362,022 | ||||||||||||||
| 750-Blueberry | $ 1.484 | 316,667 | 470,013 | $ 1.490 | 63,333 | 94,371 | $ 1.487 | 380,000 | 564,881 | ||||||||||||||
| Total 750ml | 3,450,002 | $ 5,113,699 | 689,998 | $ 1,027,659 | 4,140,000 | $ 6,146,033 | |||||||||||||||||
| Total Gross Profit | 15,833,339 | 17,141,898 | 3,166,661 | $ 3,440,622 | 19,000,000 | $ 20,595,765 | |||||||||||||||||
| Less, Selling, General and Administrative Expenses | Any change in expenses will result in equivalent change in operating income. | ||||||||||||||||||||||
| Commissions | 2.0% | of net sales | $ 689,585 | 2.0% | of net sales | $ 137,917 | 2.0% | of net sales | $ 827,502 | ||||||||||||||
| Advertising | 1,250,000 | 250,000 | 1,500,000 | ||||||||||||||||||||
| Salaries and wages | 2,625,000 | 525,000 | 3,150,000 | ||||||||||||||||||||
| Depreciation and amortization | 1,420,000 | 284,000 | 1,704,000 | ||||||||||||||||||||
| Maintenance and repairs | 520,830 | 104,166 | 624,996 | ||||||||||||||||||||
| Freight and logistics | 1,204,500 | 240,900 | 1,445,400 | ||||||||||||||||||||
| Office expenses | 145,830 | 29,166 | 174,996 | ||||||||||||||||||||
| Research and development | 750,000 | 150,000 | 900,000 | ||||||||||||||||||||
| Other expenses | 125,000 | 25,000 | 150,000 | ||||||||||||||||||||
| Total SG&A | $ 8,730,745 | $ 1,746,149 | $ 10,476,894 | ||||||||||||||||||||
| Operating Income | $ 8,411,153 | $ 1,694,473 | $ 10,118,871 | Bonus reached @ $14.0M | |||||||||||||||||||
| $ 10,500,000 | Stretch for double bonus | ||||||||||||||||||||||
| 10,094,645 | FMOH reconcile | ||||||||||||||||||||||
| 10,094,976 | Original projection | ||||||||||||||||||||||
Standard and Actual Costs
| Tiger Energy Drinks (NOVEMBER 30, 2014) | |||||||||||||||||
| Standard Costs | Actual Costs | Market Price (List price less Sales Discounts, Selling & Admin. Costs, etc) | |||||||||||||||
| Variable | Fixed | Total Std. Cost | Variable | Fixed | Total Actual Costs | Bottle Size | Market Price | ||||||||||
| Strawberry | 250 ml | $0.352 | $0.388 | $0.740 | Strawberry | 250 ml | $0.352 | ERROR:#REF! | ERROR:#REF! | Strawberry | 250 ml | $1.056 | |||||
| 500 ml | $0.687 | $0.777 | $1.464 | 500 ml | $0.687 | ERROR:#REF! | ERROR:#REF! | 500 ml | $1.565 | ||||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $2.012 | ||||||||
| Variable | Fixed | Total Std. Cost | Variable | Fixed | Total Actual Costs | ||||||||||||
| Lemon | 250 ml | $0.377 | $0.388 | $0.765 | Lemon | 250 ml | $0.377 | ERROR:#REF! | ERROR:#REF! | Lemon | 250 ml | $1.045 | |||||
| 500 ml | $0.723 | $0.777 | $1.500 | 500 ml | $0.723 | ERROR:#REF! | ERROR:#REF! | 500 ml | $1.513 | ||||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $1.999 | ||||||||
| Variable | Fixed | Total Std. Cost | Variable | Fixed | Total Actual Costs | ||||||||||||
| Raspberry | 250 ml | ERROR:#REF! | $0.388 | ERROR:#REF! | Raspberry | 250 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | Raspberry | 250 ml | $1.058 | |||||
| 500 ml | ERROR:#REF! | $0.777 | ERROR:#REF! | 500 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 500 ml | $1.569 | ||||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $2.018 | ||||||||
| Variable | Fixed | Total Std. Cost | Variable | Fixed | Total Actual Costs | ||||||||||||
| Melon | 250 ml | $0.327 | $0.388 | $0.715 | Melon | 250 ml | $0.327 | ERROR:#REF! | ERROR:#REF! | Melon | 250 ml | $1.055 | |||||
| 500 ml | $0.636 | $0.777 | $1.413 | 500 ml | $0.636 | ERROR:#REF! | ERROR:#REF! | 500 ml | $1.562 | ||||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $2.007 | ||||||||
| Variable | Fixed | Total Std. Cost | Variable | Fixed | Total Actual Costs | ||||||||||||
| Blueberry | 250 ml | ERROR:#REF! | $0.388 | ERROR:#REF! | Blueberry | 250 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | Blueberry | 250 ml | $1.058 | |||||
| 500 ml | ERROR:#REF! | $0.777 | ERROR:#REF! | 500 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 500 ml | $1.563 | ||||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $2.017 | ||||||||
| Production Fixed Cost: | |||||||||||||||||
| Flavor | Bottle Size | Sales mix? | Units Sold | FMOH | Forecast after 11 of 12 months: | $8,616,667 | |||||||||||
| Strawberry | 250 ml | 24.30% | 2,049,055 | $ 795,033 | |||||||||||||
| 500 ml | 23.35% | 1,605,503 | $ 1,247,475 | Actual: | $4,850,000 | ||||||||||||
| 750 ml | 24.59% | ERROR:#REF! | ERROR:#REF! | ||||||||||||||
| Variance: | $3,766,667 | ||||||||||||||||
| Lemon | 250 ml | 27.81% | 2,345,643 | $ 910,109 | Actual Allocation of FC (they produced the amounts that they planned in the first 11 months of the year): | ||||||||||||
| 500 ml | 23.37% | 1,607,010 | $ 1,248,647 | ||||||||||||||
| 750 ml | 27.77% | ERROR:#REF! | ERROR:#REF! | Bottle Size | Forecasted Units | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | FMOH Using Standard | |||||||
| 250 ml | 8,433,333 | 2,108,333,333 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | $ 3,272,133 | |||||||||||
| 500 ml | 6,875,000 | 3,437,500,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | $ 5,341,875 | |||||||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | |||||||||||
| Total | ERROR:#REF! | ERROR:#REF! | |||||||||||||||
| Raspberry | 250 ml | 17.27% | 1,456,112 | $ 564,971 | |||||||||||||
| 500 ml | 18.17% | 1,249,042 | $ 970,505 | ||||||||||||||
| 750 ml | 15.03% | ERROR:#REF! | ERROR:#REF! | ||||||||||||||
| Melon | 250 ml | 20.04% | 1,689,868 | $ 655,669 | |||||||||||||
| 500 ml | 23.08% | 1,586,894 | $ 1,233,016 | ||||||||||||||
| 750 ml | 22.29% | ERROR:#REF! | ERROR:#REF! | ||||||||||||||
| Blueberry | 250 ml | 10.58% | 892,655 | $ 346,350 | |||||||||||||
| 500 ml | 12.02% | 826,552 | $ 642,231 | ||||||||||||||
| 750 ml | 10.32% | ERROR:#REF! | ERROR:#REF! | ||||||||||||||
| 250 | 100.00% | 8,433,333 | 3,272,133 | ||||||||||||||
| 500 | 100.00% | 6,875,000 | 5,341,875 | ||||||||||||||
| 750 | 100.00% | ERROR:#REF! | ERROR:#REF! |
Inventory End of November 2014
| Tiger Energy Drinks Inventory (November 30, 2014) | |||||||||||||
| Based on Std. Costs | Based on Actual Costs | ||||||||||||
| Flavor | Bottle Size | Units | Standard Cost | Inventory Value | Sales mix? | Flavor | Bottle Size | Units | Actual Cost | Inventory Value | |||
| Strawberry | 250 ml | 212,500 | $0.740 | $157,250.00 | 24.3% | Strawberry | 250 ml | 212,500 | ERROR:#REF! | ERROR:#REF! | |||
| 500 ml | 133,125 | $1.464 | $194,895.00 | 23.4% | 500 ml | 133,125 | ERROR:#REF! | ERROR:#REF! | |||||
| 750 ml | 88,132 | ERROR:#REF! | ERROR:#REF! | 24.6% | 750 ml | 88,132 | ERROR:#REF! | ERROR:#REF! | |||||
| Lemon | 250 ml | 243,258 | $0.765 | $186,092.37 | 27.8% | Lemon | 250 ml | 243,258 | ERROR:#REF! | ERROR:#REF! | |||
| 500 ml | 133,250 | $1.500 | $199,875.00 | 23.4% | 500 ml | 133,250 | ERROR:#REF! | ERROR:#REF! | |||||
| 750 ml | 99,500 | ERROR:#REF! | ERROR:#REF! | 27.8% | 750 ml | 99,500 | ERROR:#REF! | ERROR:#REF! | |||||
| Raspberry | 250 ml | 151,008 | ERROR:#REF! | ERROR:#REF! | 17.3% | Raspberry | 250 ml | 151,008 | ERROR:#REF! | ERROR:#REF! | |||
| 500 ml | 103,568 | ERROR:#REF! | ERROR:#REF! | 18.2% | 500 ml | 103,568 | ERROR:#REF! | ERROR:#REF! | |||||
| 750 ml | 53,852 | ERROR:#REF! | ERROR:#REF! | 15.0% | 750 ml | 53,852 | ERROR:#REF! | ERROR:#REF! | |||||
| Melon | 250 ml | 175,250 | $0.715 | $125,303.75 | 20.0% | Melon | 250 ml | 175,250 | ERROR:#REF! | ERROR:#REF! | |||
| 500 ml | 131,582 | $1.413 | $185,925.37 | 23.1% | 500 ml | 131,582 | ERROR:#REF! | ERROR:#REF! | |||||
| 750 ml | 79,893 | ERROR:#REF! | ERROR:#REF! | 22.3% | 750 ml | 79,893 | ERROR:#REF! | ERROR:#REF! | |||||
| Blueberry | 250 ml | 92,574 | ERROR:#REF! | ERROR:#REF! | 10.6% | Blueberry | 250 ml | 92,574 | ERROR:#REF! | ERROR:#REF! | |||
| 500 ml | 68,536 | ERROR:#REF! | ERROR:#REF! | 12.0% | 500 ml | 68,536 | ERROR:#REF! | ERROR:#REF! | |||||
| 750 ml | 36,984 | ERROR:#REF! | ERROR:#REF! | 10.3% | 750 ml | 36,984 | ERROR:#REF! | ERROR:#REF! | |||||
| TOTAL | ERROR:#REF! | TOTAL | ERROR:#REF! | ||||||||||
| 250 | 874,590 | ||||||||||||
| 500 | 570,061 | ||||||||||||
| 750 | 358,361 | ||||||||||||
| VARIANCE: | ERROR:#REF! | ||||||||||||
| (Potential inventory devaluation) |
Expected Inventory End of 2014
| Production Fixed Cost: | Production Fixed Cost Expected on December 31, 2014: | ||||||||||||
| Forecast after 11 of 12 months: | $8,616,667 | Forecast | $9,400,000 | ||||||||||
| Actual: | $4,850,000 | Actual: | $5,300,000 | ||||||||||
| Variance: | $3,766,667 | Variance: | $4,100,000 | ||||||||||
| Standard Cost Allocation until November 30, 2014 | Allocation based on Actual Cost on November 30, 2014: | ||||||||||||
| Bottle Size | Forecasted Units | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | Bottle Size | Forecasted Units | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | ||
| 250 ml | 8,433,333 | 2,108,333,333 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 250 ml | 8,433,333 | 2,108,333,333 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||
| 500 ml | 6,875,000 | 3,437,500,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 500 ml | 6,875,000 | 3,437,500,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||
| Total | ERROR:#REF! | ERROR:#REF! | Total | ERROR:#REF! | ERROR:#REF! | ||||||||
| Remaining Production in December 2014 | Expected Actual Production FC per Unit on December 31, 2014 | ||||||||||||
| Bottle Size | Units | Bottle Size | Forecasted Units | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | ||||||
| 250 ml | 766,667 | 250 ml | 9,200,000 | 2,300,000,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||||||
| 500 ml | 625,000 | 500 ml | 7,500,000 | 3,750,000,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||||||
| Total | ERROR:#REF! | ERROR:#REF! | |||||||||||
| Energy Drinks Expected Standard Costs and Actual Costs on December 31, 2014 | |||||||||||||
| Standard Costs | Actual Costs | ||||||||||||
| Variable | Fixed | Total Std. Cost | Variable | Fixed | Total Actual Costs | ||||||||
| Strawberry | 250 ml | $0.352 | $0.388 | $0.740 | Strawberry | 250 ml | $0.349 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | $0.687 | $0.777 | $1.464 | 500 ml | $0.680 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $0.980 | ERROR:#REF! | ERROR:#REF! | ||||||
| Variable | Fixed | Variable | Fixed | ||||||||||
| Lemon | 250 ml | $0.377 | $0.388 | $0.765 | Lemon | 250 ml | $0.377 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | $0.723 | $0.777 | $1.500 | 500 ml | $0.724 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $1.038 | ERROR:#REF! | ERROR:#REF! | ||||||
| Variable | Fixed | Variable | Fixed | ||||||||||
| Raspberry | 250 ml | ERROR:#REF! | $0.388 | ERROR:#REF! | Raspberry | 250 ml | $0.360 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | ERROR:#REF! | $0.777 | ERROR:#REF! | 500 ml | $0.681 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $0.980 | ERROR:#REF! | ERROR:#REF! | ||||||
| Variable | Fixed | Variable | Fixed | ||||||||||
| Melon | 250 ml | $0.327 | $0.388 | $0.715 | Melon | 250 ml | $0.329 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | $0.636 | $0.777 | $1.413 | 500 ml | $0.641 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $0.909 | ERROR:#REF! | ERROR:#REF! | ||||||
| Variable | Fixed | Variable | Fixed | ||||||||||
| Blueberry | 250 ml | ERROR:#REF! | $0.388 | ERROR:#REF! | Blueberry | 250 ml | $0.361 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | ERROR:#REF! | $0.777 | ERROR:#REF! | 500 ml | $0.678 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $0.987 | ERROR:#REF! | ERROR:#REF! | ||||||
| Tiger Energy Drinks Inventory (Expected on Demeber 31, 2014) | |||||||||||||
| Based on Std. Costs | Based on Actual Costs | ||||||||||||
| Flavor | Bottle Size | Units | Standard Cost | Inventory Value | Flavor | Bottle Size | Units | Actual Cost | Inventory Value | ||||
| Strawberry | 250 ml | 202,500 | $0.740 | $149,850.00 | Strawberry | 250 ml | 202,500 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 123,125 | $1.464 | $180,255.00 | 500 ml | 123,125 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 78,132 | ERROR:#REF! | ERROR:#REF! | 750 ml | 78,132 | ERROR:#REF! | ERROR:#REF! | ||||||
| Lemon | 250 ml | 233,258 | $0.765 | $178,442.37 | Lemon | 250 ml | 233,258 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 123,250 | $1.500 | $184,875.00 | 500 ml | 123,250 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 89,500 | ERROR:#REF! | ERROR:#REF! | 750 ml | 89,500 | ERROR:#REF! | ERROR:#REF! | ||||||
| Raspberry | 250 ml | 131,008 | ERROR:#REF! | ERROR:#REF! | Raspberry | 250 ml | 131,008 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 103,568 | ERROR:#REF! | ERROR:#REF! | 500 ml | 103,568 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 53,852 | ERROR:#REF! | ERROR:#REF! | 750 ml | 53,852 | ERROR:#REF! | ERROR:#REF! | ||||||
| Melon | 250 ml | 175,250 | $0.715 | $125,303.75 | Melon | 250 ml | 175,250 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 131,582 | $1.413 | $185,925.37 | 500 ml | 131,582 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 79,893 | ERROR:#REF! | ERROR:#REF! | 750 ml | 79,893 | ERROR:#REF! | ERROR:#REF! | ||||||
| Blueberry | 250 ml | 92,574 | ERROR:#REF! | ERROR:#REF! | Blueberry | 250 ml | 92,574 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 68,536 | ERROR:#REF! | ERROR:#REF! | 500 ml | 68,536 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 36,984 | ERROR:#REF! | ERROR:#REF! | 750 ml | 36,984 | ERROR:#REF! | ERROR:#REF! | ||||||
| TOTAL | ERROR:#REF! | TOTAL | ERROR:#REF! | ||||||||||
| VARIANCE: | ERROR:#REF! | ||||||||||||
| (Potential inventory devaluation) |
What if
| WHAT IF THE COMPANY PRODUCES 20,000 UNITS LESS OF EACH PRODUCT IN DECEMBER 2014? | |||||||||||||
| Production Fixed Cost on November 30, 2014: | Production Fixed Cost Expected on December 31, 2014: | ||||||||||||
| Forecast after 11 of 12 months: | $8,616,667 | Forecast | $9,400,000 | ||||||||||
| Actual: | $4,850,000 | Actual: | $5,300,000 | ||||||||||
| Variance: | $3,766,667 | Variance: | $4,100,000 | ||||||||||
| Standard Cost Allocation on November 30, 2014 | Allocation based on Actual Cost on November 30, 2014: | ||||||||||||
| Bottle Size | Forecasted Units | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | Bottle Size | Forecasted Units | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | ||
| 250 ml | 8,433,333 | 2,108,333,333 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 250 ml | 8,433,333 | 2,108,333,333 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||
| 500 ml | 6,875,000 | 3,437,500,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 500 ml | 6,875,000 | 3,437,500,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||
| Total | ERROR:#REF! | ERROR:#REF! | Total | ERROR:#REF! | ERROR:#REF! | ||||||||
| Remaining Production in December 2014 | Adjusted Expected Actual Production FC per Unit on December 31, 2014 | ||||||||||||
| Bottle Size | Units | Bottle Size | Forecasted Units | Total ml | % of ml | Allocation of Prod. FC | Prod. FC per Unit | ||||||
| 250 ml | 766,667 | 250 ml | 9,100,000 | 2,275,000,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||||||
| 500 ml | 625,000 | 500 ml | 7,400,000 | 3,700,000,000 | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | ||||||
| Total | ERROR:#REF! | ERROR:#REF! | |||||||||||
| We assume that the company will have roughly the same amount of units in inventory - 10,000 for each product to refelct the additional production volume in December | |||||||||||||
| Energy Drinks Adjusted Standard Costs and Actual Costs | |||||||||||||
| Standard Costs | Actual Costs | ||||||||||||
| Variable | Fixed | Total Std. Cost | Variable | Fixed | Total Actual Costs | ||||||||
| Strawberry | 250 ml | $0.352 | $0.388 | $0.740 | Strawberry | 250 ml | $0.349 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | $0.687 | $0.777 | $1.464 | 500 ml | $0.680 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $0.980 | ERROR:#REF! | ERROR:#REF! | ||||||
| Variable | Fixed | Variable | Fixed | ||||||||||
| Lemon | 250 ml | $0.377 | $0.388 | $0.765 | Lemon | 250 ml | $0.377 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | $0.723 | $0.777 | $1.500 | 500 ml | $0.724 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $1.038 | ERROR:#REF! | ERROR:#REF! | ||||||
| Variable | Fixed | Variable | Fixed | ||||||||||
| Raspberry | 250 ml | ERROR:#REF! | $0.388 | ERROR:#REF! | Raspberry | 250 ml | $0.360 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | ERROR:#REF! | $0.777 | ERROR:#REF! | 500 ml | $0.681 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $0.980 | ERROR:#REF! | ERROR:#REF! | ||||||
| Variable | Fixed | Variable | Fixed | ||||||||||
| Melon | 250 ml | $0.327 | $0.388 | $0.715 | Melon | 250 ml | $0.329 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | $0.636 | $0.777 | $1.413 | 500 ml | $0.641 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $0.909 | ERROR:#REF! | ERROR:#REF! | ||||||
| Variable | Fixed | Variable | Fixed | ||||||||||
| Blueberry | 250 ml | ERROR:#REF! | $0.388 | ERROR:#REF! | Blueberry | 250 ml | $0.361 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | ERROR:#REF! | $0.777 | ERROR:#REF! | 500 ml | $0.678 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | ERROR:#REF! | ERROR:#REF! | ERROR:#REF! | 750 ml | $0.987 | ERROR:#REF! | ERROR:#REF! | ||||||
| Tiger Energy Drinks Inventory (Expected on Demeber 31, 2014) | |||||||||||||
| Based on Std. Costs | Based on Actual Costs | ||||||||||||
| Flavor | Bottle Size | Units | Standard Cost | Inventory Value | Flavor | Bottle Size | Units | Actual Cost | Inventory Value | ||||
| Strawberry | 250 ml | 192,500 | $0.740 | $142,450.00 | Strawberry | 250 ml | 192,500 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 113,125 | $1.464 | $165,615.00 | 500 ml | 113,125 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 68,132 | ERROR:#REF! | ERROR:#REF! | 750 ml | 68,132 | ERROR:#REF! | ERROR:#REF! | ||||||
| Lemon | 250 ml | 223,258 | $0.765 | $170,792.37 | Lemon | 250 ml | 223,258 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 113,250 | $1.500 | $169,875.00 | 500 ml | 113,250 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 79,500 | ERROR:#REF! | ERROR:#REF! | 750 ml | 79,500 | ERROR:#REF! | ERROR:#REF! | ||||||
| Raspberry | 250 ml | 131,008 | ERROR:#REF! | ERROR:#REF! | Raspberry | 250 ml | 131,008 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 83,568 | ERROR:#REF! | ERROR:#REF! | 500 ml | 83,568 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 33,852 | ERROR:#REF! | ERROR:#REF! | 750 ml | 33,852 | ERROR:#REF! | ERROR:#REF! | ||||||
| Melon | 250 ml | 155,250 | $0.715 | $111,003.75 | Melon | 250 ml | 155,250 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 111,582 | $1.413 | $157,665.37 | 500 ml | 111,582 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 59,893 | ERROR:#REF! | ERROR:#REF! | 750 ml | 59,893 | ERROR:#REF! | ERROR:#REF! | ||||||
| Blueberry | 250 ml | 72,574 | ERROR:#REF! | ERROR:#REF! | Blueberry | 250 ml | 72,574 | ERROR:#REF! | ERROR:#REF! | ||||
| 500 ml | 48,536 | ERROR:#REF! | ERROR:#REF! | 500 ml | 48,536 | ERROR:#REF! | ERROR:#REF! | ||||||
| 750 ml | 16,984 | ERROR:#REF! | ERROR:#REF! | 750 ml | 16,984 | ERROR:#REF! | ERROR:#REF! | ||||||
| TOTAL | ERROR:#REF! | TOTAL | ERROR:#REF! | ||||||||||
| VARIANCE: | ERROR:#REF! | ||||||||||||
| (Potential inventory devaluation) |
