Hi, I need help with answering the attached case questions. One document has the questions, the other has the actual case study.

Please answer in Excel, thank you for your help!

Case Assignment #2 Read the "Case Information - Glacier Peak Cycles" and prepare a Word document or PDF that answers the following questions. Supporting schedules and documentation can be provided in another file (e.g. Excel or PowerPoint) with reference to the exhibits being made in the submitted Word document or PDF. 1. Using the budget data, calculating the following amounts: (a) Contribution margin per unit, (b) Contribution margin ratio, (c) Break-even point in units, and (d) Break-even points in sales. 2. Using the budget data, calculate the total expected cost per unit if all manufacturing costs and overhead (both variable and fixed) were allocated to planned production. Calculate the actual total cost per unit of units produced during 2015. Discuss what is causing the difference in expected and actual total costs per unit. 3. Prepare a flexible budget for the 10,800 bicycles produced in 2015 and calculate flexible budget variances using the actual results for 2015. 4. For each department, calculate material quantity, material price, labor efficiency & labor rate variances for 2015. Also, calculate the overhead spending variances for that year. 5. Discuss some strategies or decisions that Mark Bowman should consider for improving future performance based on the analysis performed in the previous questions. Running head: MILE HIGH CYCLES 1 Mile High Cycles Student's Name Institution Mile High Cycles MILE HIGH CYCLES 2 Bob Moyer should be highly concerned about Mile High Cycle's performance and productivity. This is because the actual costs were higher than budgeted costs recorded. Bob should examine the final assembly step and determine if he can reduce the final assembly step were $17 higher than budgeted for, and thus he should look into the posted cost. Since, the stage of assembly is really the only one he should direct his attention to because the costs were all higher they were not as bad as the kits in the final assembly stage. The final assembly labor variances were the highest. The final assembly step recorded the greatest unfavorable material usage, price cost variable, labor rates and efficiency cost variances. When evaluating the final assembly to inspected process of frame assembly which is more time consuming and frequently inspected, the final assembly steps looks to be incredibly inefficient. It has the largest unfavorable labor efficiency variance, while the frame assembly step which is the most time consuming process has the lowest efficiency variance. Bob should discuss with the managers within the final assembly to determine if they can employee more and highly skilled employees in the company. By observing the high level of material usage variance for final assembly, the support needs to have highly skilled workers and improved materials. In spite of all, the company was able to manage to expand their net income by $490,400. The company indicated an increase in the overall cost variances from budget to actual production. The extra costs of producing 800 bicycles, but unfavorable still allowed the organization to turn an increase in profit. As a result, the Bob Myer should be concerned about the company due to the production cycle, which mainly indicates concerns on the material costs. Since, the material usage cost variances were high and Mile should reduce the costs. EMBA 612 - Spring 2016 Glacier Peak Cycles In 2015, Mark Bowman was reviewing production costs for Glacier Peak Cycles. Located in Seattle, Washington, the company sold very high-quality, handcrafted mountain bikes to bicycle retailers throughout the country. Sales for the company were $13 million that year. Mark Bowman had been an avid cyclist in college, racing for the Stanford University cycling team while completing his degree in mechanical engineering. After working for a few years as a design engineer for a company in Seattle, Mark decided to start his own business. As a hobby, he had designed and built several prototypes of a mountain bike, which had been enthusiastically received by his mountain-biking friends. Approaching several friends and relatives for start-up money, Glacier Peak Cycles was founded in 2013. A mountain bike is a bicycle with 15 to 21 speeds, designed and built to take the punishment of riding on dirt trails and roads. The bikes were first made by avid cyclists who customized their 10-speed bikes in order to ride on mountain trails and dirt roads. Some with framebuilding experience began to experiment making their own frames in order to better handle the additional demands of off-road riding. Mountain bikes had taken off in popularity, not only for use off-road but also for use in the city, where their sturdy construction could withstand the pounding from potholes and curbs. In addition, many casual cyclists preferred the mountain bike's more upright riding position in comparison to that of the hunched position of the 10-speed bike. Sales of all bicycles in the United States had declines in the early 2000s. However, over the same time period, sales of mountain bikes increased to more than 2.0 million units. Mark Bowman had planned to produce 10,000 bikes in 2015, all of one model. Operations as Glacier Peak Cycles consisted of three departments: frames, wheel assembly, and final assembly. In frames, steel tubing was cut to length for the components of the frame. Then the pieces were carefully welded together to form the completed frameset. This part of the process was quite time-consuming, requiring frequent inspection and measurement to ensure that the frameset was aligned perfectly. After welding, the frame was painted in one of 10 different color schemes and prepared for final assembly. In wheel assembly, front and rear wheels were assembled from their key components: hubs, spokes, and rims. All of the components were purchased from an outside supplier. Glacier Peak Cycles used a high-quality automatic lacing and truing machine to build its wheels. This machine would lace the spokes between the hub and rim and then automatically tighten the spokes to the appropriate tension. The machine was quite precise but would occasionally damage spokes during the insertion process. In such a case, the operator would replace any damaged parts and restart the machine. Each wheel would also be inspected and trued by hand in order to insure that the wheels were in perfect alignment. In final assembly, the frame and wheels were combined with other purchased parts to create the final package that would then be shipped to bicycle dealers. In this area, the front form and many other key components were attached to the frame, and the inner tubes and tires were mounted on the wheels. In order to minimize damage while shopping, some of the bicycle's components were left packaged for the dealer to assemble before selling the bike to the final customer. All of the components were purchased from outside suppliers and then were combined to form kits for the bicycles. Glacier Peak Cycles carried an inventory of spare parts to replace any parts damaged during assembly or shipping, although such replacement was quite infrequent. In reviewing his costs, Mark notes that he had produced 10,800 bicycles in 2015, 800 more than planned. Mark thought the operations during the year had done well to meet the additional demand, but he wondered if Glacier Peak Cycles was doing a good job in managing its costs. Exhibit 1 shows the planned and actual income statement results for 2015. Exhibit 2 shows the detailed planned material, labor, and overhead costs, and Exhibit 3 shows the detailed actual material, labor, and overhead costs for that year. Exhibit 1 2015 Preliminary Report on the Results of Operations Budget (10,000 units) Actual (10,800 units) $ 12,500,000 $ 13,095,000 Variable Costs Frame assembly Wheel assembly Final assembly Subtotal 4,825,000 1,265,000 3,605,000 9,695,000 5,128,337 1,416,850 4,124,600 10,669,787 Overhead Costs Rent Office staff Depreciation Other costs Subtotal 250,000 100,000 100,000 750,000 1,200,000 250,000 100,000 100,000 850,000 1,300,000 100,000 U 100,000 U Total costs 10,895,00 11,969,787 1,074,787 U Net income $ 1,605,000 $ 1,125,213 $ 479,787 U Revenue Variance (800 units) $ 595,000 F 303,337 151,850 519,600 974,787 U U U U Exhibit 2 2015 Planned Production Costs Budget based on 10,000 bicycles production Frame assembly: Steel tubing Paint Labor Total frame assembly $ 3,300,000 (110,000 lbs. @ $30.00/lb.) 25,000 (1,250 gals. @ $20.00/gal.) 1,500,000 (100,000 hrs. @15.00/hr.) 4,825,000 Wheel assembly: Parts Labor Total wheel assembly 1,200,000 (10,000 kits @ $120.00/kit) 65,000 (5,000 hrs. @ $13.00/hr.) 1,265,000 Final assembly: Parts Labor Total final assembly 3,500,000 (10,000 kits @ $350.00/kit) 105,000 (7,500 hrs. @ $14.00/hr.) 3,605,000 Overhead costs: Rent Office staff Depreciation Other costs Total overhead costs Total annual costs 250,000 100,000 100,000 750,000 (estimated to be 2/3 variable) 1,200,000 $ 10,895,000 Exhibit 3 2015 Actual Production Costs Actual based on 10,800 bicycles production Frame assembly: Steel tubing Paint Labor Total frame assembly $ 3,572,100 (113,400 lbs. @ $31.50/lb.) 28,187 (1,375 gals. @ $20.50/gal.) 1,528,050 (100,200 hrs. @15.25/hr.) 5,128,337 Wheel assembly: Parts Rework parts Labor Total wheel assembly 1,317,600 (10,800 kits @ $122.00/kit) 25,000 (spokes and rims) 74,250 (5,500 hrs. @ $13.50/hr.) 1,416,850 Final assembly: Parts Rework parts Labor Total final assembly 3,963,600 (10,800 kits @ $367.00/kit) 45,000 (miscellaneous parts) 116,000 (8,000 hrs. @ $14.50/hr.) 4,124,600 Overhead costs: Rent Office staff Depreciation Other costs Total overhead costs Total annual costs 250,000 100,000 100,000 850,000 1,300,000 $ 11,969,787