how do we put this in an excel

14-47 To complete all parts of the problems you should prepare an Excel workbook and Word document. Please note, you must show your work in Excel, which includes providing the formulas in the cells, not just the summary value. You may not earn full points if you do not show your work in detail. Provide supporting responses either as part of the Excel document or as a separate Word document (400-750 words). being sure to properly label each response. 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 S380,000 S460,000 $140,000 Variable costs $228,000 S322,000 $112.000 Fixed costs 56,000 75,000 45,000 Total costs S284,000 5397,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 S120,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 S25,000 to promote the expanded carpet line. Required: 1. Should Tipton close its wallpaper operation? Show computations to support your answer. 2. Assume that Tipton's wallpaper inventory at the time of the closure decision amounted to $23,700. How would you have treated this additional information in making the decision? 3. What advantages might Internet and magazine-based firms have over Tipton that would allow these organizations to offer deeply discounted prices-prices far below what Tipton can offer? 4. 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 S400,000, S450,000, and $130,000, for paint and supplies, carpeting, and wallpaper, respectively. Answers Variable costing: All variable costs are treated as product costs and all the fixed costs are treated as period costs. Hence, all costs relating manufacturing of products, that are variable, such as direct material, direct labor and variable manufacturing overhead, etc. are treated as product costs. And costs like selling and administration overheads which are fixed in nature, are treated as period costs as these expenses are incurred for a period. Contribution margin: Contribution margin per unit = per unit sales price - per unit variable costs. Computation of closing of operations of wallpaper department: Wallpaper Paint Carpeting 153 360000 450000 2280001 2000 152000 138000 540000 112000 28000 Particulars Sales Less: Variable Cost Contribution Martin wallpaper closed Loss of wallpaper contribution margin Remodeling expense Increase in profit of carpet sales Faed cost saving (45000-40%) Decreased contribution margin from paint and supplies (152000 2040 Increased advertising cost Loss - 12400 66000 18000 25000 - 12800 profit or loss from the closure of wallpaper department=Savings of costs and unavoidable expenses from closure + increase and decrease of profits from other departments. loss = $12,800 Hence, T should not close the wallpaper department Computation of increased contribution margin of carpeting: Particulars Sales (460000-120000) 580000 Loss: Variable cost (70%) 406000 Contribution marga 174000 Add: Increased contribution main (580000 80 29000 Total concubin margin 203000 Loss: Contribution marin before increase of sale 138000 65000 Difference of increase in profil Sales (460000-120000) 580000 Less: Variable cost (70%) 406000 Contribution margin 174000 Add: Increased contribution margin (580000 80%) 28000 Total contribution margin 203000 Less: Contribution margin before increase of sale 138000 65000 Difference of increase in profit Statement showing whether T should close or continue with the wallpaper department for changes in sales in three divisions: Carpeting Paint (S) 400000 228000 172000 450000 322000 128000 Wallpaper ($) 130000 112000 18000 Particulars Sales Less: Variable cost Contribution margin When Wallpaper is closed Loss of contribution margin Remodeling expenses Increase in profit of carpet sales Fixed cost of saving (4500040%) Decreased contribution margin from paint and supplies (172000-20%) Increased advertising cost LOSS - 18000 -12400 71500 18000 -34400 -25000 -300 profit or loss from the closure of wallpaper division = Savings of costs and unavoidable expenses from closure + increase and decrease of profits from other divisions loss = $300. Hence, T should not close the wallpaper division. Computation of increased contribution margin for carpeting: Particulars $ Sales (450000 + 120000) 570000 Less: Variable cost (70%) 399000 Contribution margin 171000 Add: Increased contribution (570000 5%) 28500 Total contribution margin 199500 Less. Contribution margin before increase of sale128000 Increase in Profit 71500 14-47 To complete all parts of the problems you should prepare an Excel workbook and Word document. Please note, you must show your work in Excel, which includes providing the formulas in the cells, not just the summary value. You may not earn full points if you do not show your work in detail. Provide supporting responses either as part of the Excel document or as a separate Word document (400-750 words). being sure to properly label each response. 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 S380,000 S460,000 $140,000 Variable costs $228,000 S322,000 $112.000 Fixed costs 56,000 75,000 45,000 Total costs S284,000 5397,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 S120,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 S25,000 to promote the expanded carpet line. Required: 1. Should Tipton close its wallpaper operation? Show computations to support your answer. 2. Assume that Tipton's wallpaper inventory at the time of the closure decision amounted to $23,700. How would you have treated this additional information in making the decision? 3. What advantages might Internet and magazine-based firms have over Tipton that would allow these organizations to offer deeply discounted prices-prices far below what Tipton can offer? 4. 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 S400,000, S450,000, and $130,000, for paint and supplies, carpeting, and wallpaper, respectively. Answers Variable costing: All variable costs are treated as product costs and all the fixed costs are treated as period costs. Hence, all costs relating manufacturing of products, that are variable, such as direct material, direct labor and variable manufacturing overhead, etc. are treated as product costs. And costs like selling and administration overheads which are fixed in nature, are treated as period costs as these expenses are incurred for a period. Contribution margin: Contribution margin per unit = per unit sales price - per unit variable costs. Computation of closing of operations of wallpaper department: Wallpaper Paint Carpeting 153 360000 450000 2280001 2000 152000 138000 540000 112000 28000 Particulars Sales Less: Variable Cost Contribution Martin wallpaper closed Loss of wallpaper contribution margin Remodeling expense Increase in profit of carpet sales Faed cost saving (45000-40%) Decreased contribution margin from paint and supplies (152000 2040 Increased advertising cost Loss - 12400 66000 18000 25000 - 12800 profit or loss from the closure of wallpaper department=Savings of costs and unavoidable expenses from closure + increase and decrease of profits from other departments. loss = $12,800 Hence, T should not close the wallpaper department Computation of increased contribution margin of carpeting: Particulars Sales (460000-120000) 580000 Loss: Variable cost (70%) 406000 Contribution marga 174000 Add: Increased contribution main (580000 80 29000 Total concubin margin 203000 Loss: Contribution marin before increase of sale 138000 65000 Difference of increase in profil Sales (460000-120000) 580000 Less: Variable cost (70%) 406000 Contribution margin 174000 Add: Increased contribution margin (580000 80%) 28000 Total contribution margin 203000 Less: Contribution margin before increase of sale 138000 65000 Difference of increase in profit Statement showing whether T should close or continue with the wallpaper department for changes in sales in three divisions: Carpeting Paint (S) 400000 228000 172000 450000 322000 128000 Wallpaper ($) 130000 112000 18000 Particulars Sales Less: Variable cost Contribution margin When Wallpaper is closed Loss of contribution margin Remodeling expenses Increase in profit of carpet sales Fixed cost of saving (4500040%) Decreased contribution margin from paint and supplies (172000-20%) Increased advertising cost LOSS - 18000 -12400 71500 18000 -34400 -25000 -300 profit or loss from the closure of wallpaper division = Savings of costs and unavoidable expenses from closure + increase and decrease of profits from other divisions loss = $300. Hence, T should not close the wallpaper division. Computation of increased contribution margin for carpeting: Particulars $ Sales (450000 + 120000) 570000 Less: Variable cost (70%) 399000 Contribution margin 171000 Add: Increased contribution (570000 5%) 28500 Total contribution margin 199500 Less. Contribution margin before increase of sale128000 Increase in Profit 71500