See the attached pdf. All 18 questions to be answered in excel with proper calculation inside the cell. Need the solution within 6 hours

ACC500 Comprehensive Case Study

Concord Company manufactures hiking boots for three major retailers in the greater New Hampshire

area. It plans to grow by producing high-quality hiking boots at a low cost that are delivered in a timely

manner. There are a number of other manufacturers who produce similar boots. Concord believes that

continuously improving its manufacturing processes and having satisfied employees are critical to

implementing its strategy. The company utilizes a balanced scorecard approach to managing and

monitoring the business.

For simplicity, assume that the company produces a single product, sales are equal to production, and

inventory levels are zero.

Below are the standard costs per boot:

Standard Quantity Standard Price

of Input Allowed per Unit

per Unit of Output of Input______

Direct materials 3 pounds $3 per pound

Direct labor 1 hour $17 per hour

Below is the budgeted information for the month of July:

Units produced and sold 20,000

Average selling price per unit $42

Direct materials – based on the standards per unit

Direct labor – based on the standards per unit

Variable factory overhead per unit $5 per direct labor hour

Fixed factory overhead $50,000

Variable shipping costs per unit $3

Variable selling cost per unit $1

Fixed selling costs $15,000

Fixed administrative costs $20,000

Below are the actual results for the month of July:

Units produced and sold 20,120

Actual sales revenue (see table below by customer) $829,820

Direct materials (65,000 lbs used) $191,750

Direct labor (19,500 actual hours) $333,450

Variable factory overhead $103,000

Fixed factory overhead $54,000

Variable shipping costs * $61,000

Variable selling cost $19,850

Fixed selling costs $17,000

Fixed administrative costs $21,000

Southern New Hampshire University – ACC500 Comprehensive Case Study Page 2

*include variable shipping costs in cost of goods sold when preparing the income statement

Actual sales units and selling prices by customer for July:

Units Selling Price

Customer A 12,000 $39 per unit

Customer B 5,850 $44 per unit

Customer C 2,270 $46 per unit

Required:

1. Prepare an income statement in the traditional format for the month of July. 2. Prepare a flexible budget in the contribution format for Concord Company for the following

three activity levels: 18,000 units, 22,000 units, and 25,000 units.

3. Prepare an operating income schedule for July in the contribution format showing the actual results, flexible budget variances, flexible budget, sales-activity variance, and static budget.

4. Calculate the labor price and quantity variances for July. 5. Calculate the materials price and quantity variances for July. 6. Calculate the variable factory overhead efficiency and spending variances for July. 7. Comment on all of the variances calculated in the previous four requirements. What might be

causing these variances?

8. Calculate the breakeven point in terms of units and sales dollars for Concord based on budgeted numbers.

9. Calculate the breakeven point in terms of units and sales dollars for Concord based on the actual July results.

10. Calculate the number of units and sales dollars required to reach a target operating income of $80,000 based on budgeted numbers.

11. Assuming that variable costs per unit are the same regardless of customer and fixed costs are allocated to the three customers based on units sold, prepare a schedule showing the operating

income per customer (show sales, variable costs, contribution margin, fixed costs, operating

income and operating income as a percent of sales).

12. Based on the above analysis should the company discontinue selling to one of its customers assuming that no fixed costs can be eliminated if the company discontinues selling to one

customer and the company only produces enough units to sell to the remaining two customers?

Why, or why not?

13. If the company could eliminate one-half of the fixed costs, would that change your answer to the previous question? Why, or why not?

14. Now assume that variable costs per unit are the same regardless of customer and budgeted fixed costs are allocated to the three customers based on the ABC (Activity-Based Costing)

schedule below, prepare a schedule showing the actual operating income per customer (show

sales, variable costs, contribution margin, fixed costs, operating income and operating income as

a percent of sales)

Customer A Customer B Customer C

Fixed factory overhead

Setup costs $34,000 (# of setups) 50 40 60

Rent $20,000 (square feet) 3,000 2,250 2,000

Fixed selling (based on direct support) $3,873 $6,757 $4,370

Fixed admin (based on units sold) 12,000 5,850 2,270

Southern New Hampshire University – ACC500 Comprehensive Case Study Page 3

15. Comment on how the change in allocation impacted the profitability by customer. Which customer is now the least profitable?

16. Going back to the original results for the month of July, what if the company decides to raise the price to Customer A by $2 per unit but sells 8% less units to Customer A as a result, should they

do it? Why, or why not?

17. Going back to the original results for the month of July, what if the company received a proposal from a subcontractor to manufacture all of units that were sold to Customer B for $37 per unit

and Concord would only manufacture enough units related to the demand from Customer A and

C, should they accept the proposal or continue to manufacture the units in-house? Explain.

(Assume that Concord cannot reduce their fixed costs).

18. Would your answer change to the question above if Concord was able to reduce their fixed costs by 50% as a result of the decision to outsource Customer B units? Explain.

Prepare your response in accordance with the grading rubric for a short paper/case study, and please

show the detail of your calculations used to arrive at your answers.