See the attached pdf. All 18 questions to be answered in excel with proper calculation inside the cell. Need the solution within 6 hoursssanakhaio988
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
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.