Reference no: EM132855925

This year, Cairo Company sold 35,000 units its only product for $16 per unit. Manufacturing and selling the product required $120,000 of fixed manufacturing costs and $180,000 of fixed selling and administrative costs.

Its per unit variable costs were as follows:

Material $ 4.00
Direct labour $ 3.00
Variable overhead costs $ 0.40
variable selling and administrative expenses $ 0.20

• Next year, the company will use new material that is easier to work. The new material will reduce material costs by 60% and direct labour costs by 40% and will not affect quality or marketability. Management is considering an increase in the unit sales price to reduce the number of units sold because the factory's output is nearing its annual capacity of 40,000 units. Two plans are being considered. Under Plan 1, the company will keep the price at the current level and sell the same volume as last year. Under Plan 2, the company will increase price by 25%. This price will decrease unit sales volume by 10%. Under both plans, total fixed costs and the variable costs per unit for overhead and for selling and administrative costs will remain the same.

Problem 1. Using Excel, generate fully labelled breakeven chart for both plans (2 charts). Charts should display all major and minor gridlines.

Problem 2. Using Excel, create a forecasted contribution statement for the current situation and both plans.

Problem 3. Advise the company, based on your results for requirements 1 and 2.

Problem 4. Why is spreadsheet analysis an important tool for management accountants?