Please solve using Excel formulas to show work.

A/S DANSK MINOX, COPENHAGEN A/S Dansk Minox in Copenhagen, specializing in branded vacuum-packed meat and other food products, had for many years sold vacuum-packed sliced pork in gravy, a very popular dish in Denmark. In 1995 the product represented about 15% of the firm's total sales in the country in a product range which comprises 30 products. The Danish house-wife very often serves this dish together with a red cabbage salad. This salad is rather time-consuming to prepare at home and certain competitors of A/S Dansk Minox had recently introduced red cabbage salad in either vacuum-packed, canned, or frozen form. However, A/S Dansk Minox estimated that the major part of the red cabbage sold was still prepared at home, and although sales of ready-made red cabbage salad expanded rapidly, it was felt, and consumer research confirmed this, that there was still a great untapped potential for such a product. At the end of 1995 A/S Dansk Minox had not marketed vacuum-packed red cabbage salad, but in view of existing market potential, and since it was so often eaten together with sliced pork, company management also considered producing red cabbage salad. A/S Dansk Minox had during the last year considered introducing a specialty line of complete meals", which were to be sold in an attractive carton containing vacuum- sealed bags with the different ingredients for the meal. The management decided that the first product in this specialty line was to be "sliced pork in gravy with red cabbage" and the product was to be packed in a carton containing the standard vacuum-sealed bag with sliced pork plus another bag with the red cabbage. Cost allocation problems arose in this connection, leading to long discussions between the marketing and finance departments of the Danish company. The standard product "sliced pork in gravy" was sold in a 450-gram bag at a consumer price of D.Cr. 4.85. This was the "ideal" quantity for an average family, giving between 3 and 4 servings. Therefore, when considering the "complete meal" product, the marketing department did not wish to change the quantity of sliced pork in gravy. Extensive testing showed that the average family consumed between 500 and 600 grams of red cabbage salad with 450 grams of sliced pork in gravy. It was therefore decided to sell the complete meal" product in a 1-kilogram pack, containing the standard 450-gram bag with sliced pork in gravy plus another vacuum sealed bag with 550 grams of red cabbage salad. TI The marketing department received a preliminary selling price calculation from the finance department, based on the assumption that the new product should produce approximately the same profit per kilogram as the standard sliced pork in gravy, i.e., D.Cr. 0.30 per kilogram (see Exhibit 1). The difference in consumer price between the two packs as proposed by the finance department meant that the consumer would have to pay D-Cr. 3.35 (8.20 - 4.85) for the red cabbage salad, since the sliced pork in gravy content of the two packs was the same. The marketing department protested that this price difference was prohibitive, since the ingredients for making the red cabbage salad at home could be bought for approximately D.Cr. 1.10 and the labor costs at home (if counted at all) would not amount to more than approximately 0.70. The marketing department argued that A/S Dansk Minox could not expect the consumer to pay more than D.Cr. 2.00 at the most for the red cabbage salad and added convenience, thus leaving a consumer price for the new pack of 4.85 +2.00 or 6.85. The marketing department contended, furthermore, that the selling price calculation showed that the raw material and labor costs amounted to only 0.75 for the red cabbage salad and that it was unreasonable that the addition of the other cost elements should result in a total consumer price difference of 3.35. The marketing department then proposed its own price calculation, based on the assumption that the consumer price for the "complete meal" would be D.Cr. 6.85 as indicated above (see Exhibit 2). There was no disagreement between the marketing and finance departments with regard to the raw material, labor, packaging material, transport and storage, and sundry variable costs. The item "Other product-related fixed expenses" covered mainly advertising; consequently, the marketing department could not argue with the finance department about this item either, since it was under the control of the marketing department. The two items "Margins and discounts" and "General overheads" are, as a standard rule in the company, calculated as fixed percentages of the price to the retailer (8% and 4%, respectively). Although this procedure might be open to question, the marketing department knew these calculated costs would decrease automatically if a lower selling price could be agreed upon. The main discussion, therefore, centered upon the item "Production fixed expenses." After internal agreement on the sales budget every year, the total production fixed expenses were divided by the total sales quantity, expressed in kilograms. This computation resulted in a rate of D.Cr. 1.20 per kilogram for the year under consideration. This rate was then applied to all products from the company's factory. There was no need to buy any new equipment for making the red cabbage salad and there was spare capacity available for the estimated production of the new "complete meal" product. The estimated sales of the new product were included in the budgeted sales quantity. The finance department claimed that any departure downwards from the rate of D.Cr. 1.20 per kilogram for production fixed expenses would result in an undercoverage of fixed expenses. The marketing department replied that a strict application of this rule would lead to unreasonable consequences in this case, where a relatively cheap component (red cabbage) is added to an expensive component (sliced pork in gravy), and where the cheap component more than doubles the weight of the new pack and thus also doubles the fixed burden charged to the product. The finance department stated that it would be impractical to use different burden rates per kilogram for different products. It was supported in this view by the managing director, who said that the product should not be introduced if a normal selling price calculation did not show an operating profit. The marketing department responded that selling the new product at D.Cr. 8.20 per pack was out of the question; therefore, only two alternatives remained: (a) Abandon the whole project (b) Establish a consumer price of D.Cr. 6.85 and a price to the retailer of D.Cr. 4.78. The 8% margins and discounts to wholesalers and the 4% general overhead would then amount to 0.38 +0.19 instead of 0.46 + 0.23, a reduction of 0.12. The production fixed expense would need to be reduced from 1.20 to 0.54, the same amount as for one standard pack of sliced pork in gravy. The managing director decided in spite of the marketing department's arguments that the new product should not be introduced without full coverage of fixed expenses. It was introduced at a consumer price of D.Cr. 8.20, and the sales budget was set at 85 tons. This was about forty-five percent of the budgeted sales of the standard pack of sliced pork in gravy, which reflected the assumption that the upward sales trend of recent years would continue. In other words, the company did not expect that the new "complete meal" product would steal sales from the standard pack. Some customers would certainly switch over from the old product to the new, but these losses would be offset by the added sales resulting from greater consumer awareness of Minox products due to the planned advertising campaigns for the "complete meal" item. In the months that followed, a number of complaints about the high price of the new product were received from retailers and consumers, and sales for the first year amounted to only 30 tons in contrast to the budgeted 85 tons. Sales of the standard pack, on the other hand, exceeded the budgeted amount by a small percentage. ADDITIONAL INFORMATION Assume that 1 ton = 1000 Kg. The budgeted sales volume for standard pack pork with gravy for 1996 was 189 tons. Budgeted production fixed expenses for the company for 1996 was Cr. 1.51 million. Budgeted direct labor expense for the company for 1996 was Cr. 700,000. Assume the cost item "other product related fixed expenses" is advertising and that the annual budget for the item must be committed at the beginning of the year for any product that will be sold that year. The cost item "transportation, storage" represents an allocated share of the expense for operating a fleet of company owned delivery trucks and a company owned finished goods warehouse. The company believes these expenses should be considered volume dependent because the alternative to company ownership would be use of public freight companies and a public warehouse, both of which charge a price per kilo of product handled. A/S DANSK MINOX EXHIBIT 1 FINANCE DEPARTMENT PROPOSAL NEW PACK STD.PACK DIFF. 8.20 4.85 3.35 .91 Consumer Price Turnover Tax (12.5% of consumer price before tax) Consumer Price Before Tax Retailer's Margin (27.5% of price to retailer) .37 2.98 7.29 1.57 .93 .64 Price to Retailer 5.72 3.38 2.34 COSTS: 1.67 1.67 .25 06 Material: Pork Labor. Pork Material: Cabbage Labor: Cabbage Packaging Transportation, storage Margins and discounts to wholesalers (8%) Sundry Variable Costs Total Variable Costs Production Fixed Expenses (D.Cr. 1.20 per kilogram) Other product related Fixed Expenses General, selling and admin. expenses and overhead (4% of price to retailer) TOTAL COST PROFIT 3.69 43 1.26 1.20 .54 .66 .16 $ .09 25 13 2.17 17 30 A/S DANSK MINOX EXHIBIT 2 MARKETING DEPARTMENT PROPOSAL NEW PACK STD.PACK DIFF. 6.85 4.85 2.00 .76 .22 Consumer Price Turnover Tax (12.5% of consumer price before tax) Consumer Price Before Tax Retailer's Margin (27.5% of price to retailer) .54 4.31 6.09 1.31 .93 .38 Price to Retailer 4.78 3.38 1.40 COSTS: 1.67 1.67 .25 .26 .20 04 1.06 Material: Pork Labor. Pork Material: Cabbage Labor: Cabbage Packaging Transportation, storage Margins and discounts to wholesalers (8%) Sundry Variable Costs Total Variable Costs Production Fixed Expenses (D.Cr. 1.20 per kilogram) Other product related Fixed Expenses General, selling and admin. expenses and overhead (4% of price to retailer) TOTAL COST PROFIT 2.43 1.18 .54 .16 .05 .01 QUESTIONS QUESTION 1. Given the decision to introduce the "complete meal" product and advertise it according to the plan, how much better or worse off was the firm by producing and selling 30 tons at a retail price of Cr. 8.20? QUESTION 2. Given the decision to introduce the "complete meal" product and advertise it according to the plan, how much better or worse off would the firm have been if 85 tons had been produced and sold at a retail price of Cr. 6.85? QUESTION 3. Combining questions 1 and 2, which retail price produces more incremental profit for the firm, and how much more? QUESTION 4. What sales volume is required at a retail price of Cr. 6.85 to give the same incremental profit as selling 30 tons at a retail price of Cr. 8.20? QUESTION 5. What is the total unit cost and per unit profit for 1 Kg of "complete meal" at a retail selling price of Cr. 6.85 and with an allocation of Cr. 1.20 for production fixed expenses? QUESTION 6. How much production fixed expenses should be allocated to 1 kg of the "complete meal." Give a specific number and your logic to support the number. QUESTION 7. What is your recommendation to management regarding the new "complete meal" product? A/S DANSK MINOX, COPENHAGEN A/S Dansk Minox in Copenhagen, specializing in branded vacuum-packed meat and other food products, had for many years sold vacuum-packed sliced pork in gravy, a very popular dish in Denmark. In 1995 the product represented about 15% of the firm's total sales in the country in a product range which comprises 30 products. The Danish house-wife very often serves this dish together with a red cabbage salad. This salad is rather time-consuming to prepare at home and certain competitors of A/S Dansk Minox had recently introduced red cabbage salad in either vacuum-packed, canned, or frozen form. However, A/S Dansk Minox estimated that the major part of the red cabbage sold was still prepared at home, and although sales of ready-made red cabbage salad expanded rapidly, it was felt, and consumer research confirmed this, that there was still a great untapped potential for such a product. At the end of 1995 A/S Dansk Minox had not marketed vacuum-packed red cabbage salad, but in view of existing market potential, and since it was so often eaten together with sliced pork, company management also considered producing red cabbage salad. A/S Dansk Minox had during the last year considered introducing a specialty line of complete meals", which were to be sold in an attractive carton containing vacuum- sealed bags with the different ingredients for the meal. The management decided that the first product in this specialty line was to be "sliced pork in gravy with red cabbage" and the product was to be packed in a carton containing the standard vacuum-sealed bag with sliced pork plus another bag with the red cabbage. Cost allocation problems arose in this connection, leading to long discussions between the marketing and finance departments of the Danish company. The standard product "sliced pork in gravy" was sold in a 450-gram bag at a consumer price of D.Cr. 4.85. This was the "ideal" quantity for an average family, giving between 3 and 4 servings. Therefore, when considering the "complete meal" product, the marketing department did not wish to change the quantity of sliced pork in gravy. Extensive testing showed that the average family consumed between 500 and 600 grams of red cabbage salad with 450 grams of sliced pork in gravy. It was therefore decided to sell the complete meal" product in a 1-kilogram pack, containing the standard 450-gram bag with sliced pork in gravy plus another vacuum sealed bag with 550 grams of red cabbage salad. TI The marketing department received a preliminary selling price calculation from the finance department, based on the assumption that the new product should produce approximately the same profit per kilogram as the standard sliced pork in gravy, i.e., D.Cr. 0.30 per kilogram (see Exhibit 1). The difference in consumer price between the two packs as proposed by the finance department meant that the consumer would have to pay D-Cr. 3.35 (8.20 - 4.85) for the red cabbage salad, since the sliced pork in gravy content of the two packs was the same. The marketing department protested that this price difference was prohibitive, since the ingredients for making the red cabbage salad at home could be bought for approximately D.Cr. 1.10 and the labor costs at home (if counted at all) would not amount to more than approximately 0.70. The marketing department argued that A/S Dansk Minox could not expect the consumer to pay more than D.Cr. 2.00 at the most for the red cabbage salad and added convenience, thus leaving a consumer price for the new pack of 4.85 +2.00 or 6.85. The marketing department contended, furthermore, that the selling price calculation showed that the raw material and labor costs amounted to only 0.75 for the red cabbage salad and that it was unreasonable that the addition of the other cost elements should result in a total consumer price difference of 3.35. The marketing department then proposed its own price calculation, based on the assumption that the consumer price for the "complete meal" would be D.Cr. 6.85 as indicated above (see Exhibit 2). There was no disagreement between the marketing and finance departments with regard to the raw material, labor, packaging material, transport and storage, and sundry variable costs. The item "Other product-related fixed expenses" covered mainly advertising; consequently, the marketing department could not argue with the finance department about this item either, since it was under the control of the marketing department. The two items "Margins and discounts" and "General overheads" are, as a standard rule in the company, calculated as fixed percentages of the price to the retailer (8% and 4%, respectively). Although this procedure might be open to question, the marketing department knew these calculated costs would decrease automatically if a lower selling price could be agreed upon. The main discussion, therefore, centered upon the item "Production fixed expenses." After internal agreement on the sales budget every year, the total production fixed expenses were divided by the total sales quantity, expressed in kilograms. This computation resulted in a rate of D.Cr. 1.20 per kilogram for the year under consideration. This rate was then applied to all products from the company's factory. There was no need to buy any new equipment for making the red cabbage salad and there was spare capacity available for the estimated production of the new "complete meal" product. The estimated sales of the new product were included in the budgeted sales quantity. The finance department claimed that any departure downwards from the rate of D.Cr. 1.20 per kilogram for production fixed expenses would result in an undercoverage of fixed expenses. The marketing department replied that a strict application of this rule would lead to unreasonable consequences in this case, where a relatively cheap component (red cabbage) is added to an expensive component (sliced pork in gravy), and where the cheap component more than doubles the weight of the new pack and thus also doubles the fixed burden charged to the product. The finance department stated that it would be impractical to use different burden rates per kilogram for different products. It was supported in this view by the managing director, who said that the product should not be introduced if a normal selling price calculation did not show an operating profit. The marketing department responded that selling the new product at D.Cr. 8.20 per pack was out of the question; therefore, only two alternatives remained: (a) Abandon the whole project (b) Establish a consumer price of D.Cr. 6.85 and a price to the retailer of D.Cr. 4.78. The 8% margins and discounts to wholesalers and the 4% general overhead would then amount to 0.38 +0.19 instead of 0.46 + 0.23, a reduction of 0.12. The production fixed expense would need to be reduced from 1.20 to 0.54, the same amount as for one standard pack of sliced pork in gravy. The managing director decided in spite of the marketing department's arguments that the new product should not be introduced without full coverage of fixed expenses. It was introduced at a consumer price of D.Cr. 8.20, and the sales budget was set at 85 tons. This was about forty-five percent of the budgeted sales of the standard pack of sliced pork in gravy, which reflected the assumption that the upward sales trend of recent years would continue. In other words, the company did not expect that the new "complete meal" product would steal sales from the standard pack. Some customers would certainly switch over from the old product to the new, but these losses would be offset by the added sales resulting from greater consumer awareness of Minox products due to the planned advertising campaigns for the "complete meal" item. In the months that followed, a number of complaints about the high price of the new product were received from retailers and consumers, and sales for the first year amounted to only 30 tons in contrast to the budgeted 85 tons. Sales of the standard pack, on the other hand, exceeded the budgeted amount by a small percentage. ADDITIONAL INFORMATION Assume that 1 ton = 1000 Kg. The budgeted sales volume for standard pack pork with gravy for 1996 was 189 tons. Budgeted production fixed expenses for the company for 1996 was Cr. 1.51 million. Budgeted direct labor expense for the company for 1996 was Cr. 700,000. Assume the cost item "other product related fixed expenses" is advertising and that the annual budget for the item must be committed at the beginning of the year for any product that will be sold that year. The cost item "transportation, storage" represents an allocated share of the expense for operating a fleet of company owned delivery trucks and a company owned finished goods warehouse. The company believes these expenses should be considered volume dependent because the alternative to company ownership would be use of public freight companies and a public warehouse, both of which charge a price per kilo of product handled. A/S DANSK MINOX EXHIBIT 1 FINANCE DEPARTMENT PROPOSAL NEW PACK STD.PACK DIFF. 8.20 4.85 3.35 .91 Consumer Price Turnover Tax (12.5% of consumer price before tax) Consumer Price Before Tax Retailer's Margin (27.5% of price to retailer) .37 2.98 7.29 1.57 .93 .64 Price to Retailer 5.72 3.38 2.34 COSTS: 1.67 1.67 .25 06 Material: Pork Labor. Pork Material: Cabbage Labor: Cabbage Packaging Transportation, storage Margins and discounts to wholesalers (8%) Sundry Variable Costs Total Variable Costs Production Fixed Expenses (D.Cr. 1.20 per kilogram) Other product related Fixed Expenses General, selling and admin. expenses and overhead (4% of price to retailer) TOTAL COST PROFIT 3.69 43 1.26 1.20 .54 .66 .16 $ .09 25 13 2.17 17 30 A/S DANSK MINOX EXHIBIT 2 MARKETING DEPARTMENT PROPOSAL NEW PACK STD.PACK DIFF. 6.85 4.85 2.00 .76 .22 Consumer Price Turnover Tax (12.5% of consumer price before tax) Consumer Price Before Tax Retailer's Margin (27.5% of price to retailer) .54 4.31 6.09 1.31 .93 .38 Price to Retailer 4.78 3.38 1.40 COSTS: 1.67 1.67 .25 .26 .20 04 1.06 Material: Pork Labor. Pork Material: Cabbage Labor: Cabbage Packaging Transportation, storage Margins and discounts to wholesalers (8%) Sundry Variable Costs Total Variable Costs Production Fixed Expenses (D.Cr. 1.20 per kilogram) Other product related Fixed Expenses General, selling and admin. expenses and overhead (4% of price to retailer) TOTAL COST PROFIT 2.43 1.18 .54 .16 .05 .01 QUESTIONS QUESTION 1. Given the decision to introduce the "complete meal" product and advertise it according to the plan, how much better or worse off was the firm by producing and selling 30 tons at a retail price of Cr. 8.20? QUESTION 2. Given the decision to introduce the "complete meal" product and advertise it according to the plan, how much better or worse off would the firm have been if 85 tons had been produced and sold at a retail price of Cr. 6.85? QUESTION 3. Combining questions 1 and 2, which retail price produces more incremental profit for the firm, and how much more? QUESTION 4. What sales volume is required at a retail price of Cr. 6.85 to give the same incremental profit as selling 30 tons at a retail price of Cr. 8.20? QUESTION 5. What is the total unit cost and per unit profit for 1 Kg of "complete meal" at a retail selling price of Cr. 6.85 and with an allocation of Cr. 1.20 for production fixed expenses? QUESTION 6. How much production fixed expenses should be allocated to 1 kg of the "complete meal." Give a specific number and your logic to support the number. QUESTION 7. What is your recommendation to management regarding the new "complete meal" product