Question

In: Accounting

Tipton One-Stop Decorating sells paint and paint supplies, carpet, and wallpaper at a single-store location in...

Tipton One-Stop Decorating sells paint and paint supplies, carpet, and wallpaper at a single-store location in suburban Des Moines. Although the company has been very profitable over the years, management has seen a significant decline in wallpaper sales and earnings. Much of this decline is attributable to the Internet and to companies that advertise deeply discounted prices in magazines and offer customers free shipping and toll-free telephone numbers. Recent figures follow. Paint and Supplies Carpeting Wallpaper Sales $ 380,000 $ 460,000 $ 140,000 Variable costs $ 228,000 $ 322,000 $ 112,000 Fixed costs 56,000 75,000 45,000 Total costs $ 284,000 $ 397,000 $ 157,000 Operating income (loss) $ 96,000 $ 63,000 $ (17,000 ) Tipton is studying whether to drop wallpaper because of the changing market and accompanying loss. If the line is dropped, the following changes are expected to occur: The vacated space will be remodeled at a cost of $12,400 and will be devoted to an expanded line of high-end carpet. Sales of carpet are expected to increase by $120,000, and the line’s overall contribution margin ratio will rise by five percentage points. Tipton can cut wallpaper’s fixed costs by 40 percent. Remaining fixed costs will continue to be incurred. Customers who purchased wallpaper often bought paint and paint supplies. Sales of paint and paint supplies are expected to fall by 20 percent. The firm will increase advertising expenditures by $25,000 to promote the expanded carpet line. Build a spreadsheet : Construct an Excel spreadsheet to solve requirement (1) above. Show how the solution will change if the following information changes: sales were $400,000, $450,000, and $130,000, for paint and supplies, carpeting, and wallpaper respectively. I need the calculations for the Excel spreadsheet.

Solutions

Expert Solution

1) Show how the solution will change if the following information changes: sales were $400,000, $450,000, and $130,000, for paint and supplies, carpeting, and wallpaper respectively
Given: Paint and Supplies Carpeting Wallpaper
Sales $380,000.00 $460,000.00 $140,000.00
Less: Variable costs $228,000.00 $322,000.00 $112,000.00
Contribution Margin $152,000.00 $138,000.00 $28,000.00
Contribution Margin Ratio = CM/Sales 40.00% 30.00% 20.00%
Paint and Supplies Carpeting Wallpaper
Sales $400,000.00 $450,000.00 $130,000.00
Less: Variable costs = (1 - Contribution Margin Ratio) x Sales $240,000.00 $315,000.00 $104,000.00
Contribution Margin $160,000.00 $135,000.00 $26,000.00
Contribution Margin Ratio = CM/Sales 40.00% 30.00% 20.00%
if Tipton closes its wallpaper operation
Loss of wallpaper contribution margin -$26,000.00
Remodeling -$12,400.00
Added profitability from carpet sales* $64,500.00
Fixed cost savings ($45,000 x 40%) $18,000.00
Decreased contribution margin from paint and supplies ($152,000 x 20%) -$32,000.00
Increased advertising -$25,000.00
Income (loss) from closure -$12,900.00
Increased Carpet Sales = $460,000 + $120,000 $570,000.00
Increased Carpet's CM ratio (30% + 5%) 35.00%
New Contribution Margin $199,500.00
Old Contribution Margin $135,000.00
Difference $64,500.00



Related Solutions

Tipton One-Stop Decorating sells paint and paint supplies, carpet, and wallpaper at a single-store location in...
Tipton One-Stop Decorating sells paint and paint supplies, carpet, and wallpaper at a single-store location in suburban Des Moines. Although the company has been very profitable over the years, management has seen a significant decline in wallpaper sales and earnings. Much of this decline is attributable to the Internet and to companies that advertise deeply discounted prices in magazines and offer customers free shipping and toll-free telephone numbers. Recent figures follow. Paint and Supplies Carpeting Wallpaper Sales $ 380,000 $...
ACC 650 - REVISED PROBLEM 14-47 #3 Tipton One-Stop Decorating sells paint and paint supplies, carpet,...
ACC 650 - REVISED PROBLEM 14-47 #3 Tipton One-Stop Decorating sells paint and paint supplies, carpet, and wallpaper at a single-store location in suburban Des Moines. Although the company has been very profitable over the years, management has seen a significant decline in wallpaper sales and earnings. Much of this decline is attributable to the Internet and to companies that advertise deeply discounted prices in magazines and offer customers free shipping and toll-free telephone numbers. Recent figures follow. Paint and...
ACC 650 - REVISED PROBLEM 14-47 #2 Tipton One-Stop Decorating sells paint and paint supplies, carpet,...
ACC 650 - REVISED PROBLEM 14-47 #2 Tipton One-Stop Decorating sells paint and paint supplies, carpet, and wallpaper at a single-store location in suburban Des Moines. Although the company has been very profitable over the years, management has seen a significant decline in wallpaper sales and earnings. Much of this decline is attributable to the Internet and to companies that advertise deeply discounted prices in magazines and offer customers free shipping and toll-free telephone numbers. Recent figures follow. Paint and...
ACC 650 - REVISED PROBLEM 14-47 #1 Tipton One-Stop Decorating sells paint and paint supplies, carpet,...
ACC 650 - REVISED PROBLEM 14-47 #1 Tipton One-Stop Decorating sells paint and paint supplies, carpet, and wallpaper at a single-store location in suburban Des Moines. Although the company has been very profitable over the years, management has seen a significant decline in wallpaper sales and earnings. Much of this decline is attributable to the Internet and to companies that advertise deeply discounted prices in magazines and offer customers free shipping and toll-free telephone numbers. Recent figures follow. Paint and...
ACC 650 - REVISED PROBLEM 14-47 #4 Tipton One-Stop Decorating sells paint and paint supplies, carpet,...
ACC 650 - REVISED PROBLEM 14-47 #4 Tipton One-Stop Decorating sells paint and paint supplies, carpet, and wallpaper at a single-store location in suburban Des Moines. Although the company has been very profitable over the years, management has seen a significant decline in wallpaper sales and earnings. Much of this decline is attributable to the Internet and to companies that advertise deeply discounted prices in magazines and offer customers free shipping and toll-free telephone numbers. Recent figures follow. Paint and...
The I. Kruger Paint and Wallpaper Store is a large retail distributor of the Supertrex brand...
The I. Kruger Paint and Wallpaper Store is a large retail distributor of the Supertrex brand of vinyl wallcoverings. Kruger will enhance its citywide image in Miami if it can sell more rolls of Supertrex next year than other local stores. It is able to estimate the demand function as follows: Number of rolls of Supertrex sold = 20 X Dollars spent on advertising + 6.8 X Dollars spent on in-store displays + 12 X Dollars invested in on-hand wallpaper...
Everyday Supplies Pty Ltd is a single-store retailer that sells a variety of tools, garden supplies,...
Everyday Supplies Pty Ltd is a single-store retailer that sells a variety of tools, garden supplies, timber, small appliances, and electrical fixtures to the public, although about half of Everyday Supplies’ sales are to construction contractors on account. Retail customers pay for merchandise by cash or credit card at cash registers when merchandise is purchased. A contractor may purchase merchandise on account, if approved by the credit manager based only on the manager’s familiarity with the contractor’s reputation. After credit...
Everyday Supplies Pty Ltd is a single-store retailer that sells a variety of tools, garden supplies,...
Everyday Supplies Pty Ltd is a single-store retailer that sells a variety of tools, garden supplies, timber, small appliances, and electrical fixtures to the public, although about half of Everyday Supplies’ sales are to construction contractors on account. Retail customers pay for merchandise by cash or credit card at cash registers when merchandise is purchased. A contractor may purchase merchandise on account, if approved by the credit manager based only on the manager’s familiarity with the contractor’s reputation. After credit...
Everyday Supplies Pty Ltd is a single-store retailer that sells a variety of tools, garden supplies,...
Everyday Supplies Pty Ltd is a single-store retailer that sells a variety of tools, garden supplies, timber, small appliances, and electrical fixtures to the public, although about half of EverydaySupplies’ sales are to construction contractors on account. Retail customers pay for merchandise by cash or credit card at cash registers when merchandise is purchased. A contractor may purchase merchandise on account, if approved by the credit manager based only on the manager’s familiarity with the contractor’s reputation. After credit is...
Alex Home Supplies Inc. is specialized in selling paint, carpet, and flooring supplies. Company has experienced...
Alex Home Supplies Inc. is specialized in selling paint, carpet, and flooring supplies. Company has experienced net operating losses in its Flooring Supplies line during the last few periods. Company management team thinks that the store will improve its profitability if it discontinued the Flooring Supplies line. The operating results from the most recent period are as follows. Currently, the store support expenses are allocated among the three product line based on sales dollars of the product lines. Product Profitability...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT