I need assistance with solving these practice problems in Excel. Can you show the work in excel spreadsheet so that I can have a better understanding of how to use excel when solving the problems?

Problem 1 Eagle Stores is a retailer of office supplies. The monthly retail demand for a product is provided below from January 2015 to October 2016. Demand Data Year Year month 2015 2016 January 616 758 February 832 974 March 898 940 April 964 1126 May 880 1032 June 1016 988 July 912 1004 August 828 870 September 894 1056 October 850 1020 November 1010 December 969 a. Use the nave method to forecast the demand for November and December 2016. b. Use the moving average method with 4 periods to forecast the demand for November and December 2016. c. Use the exponential smoothing method with smoothing constant 0.7 to forecast the demand for November and December 2016. Initialize the forecasting method using the actual demand in January 2015. d. Fit a trend line to the demand data from January 2015 to December 2015. What is the trend line equation? e. What is the forecasted demand for November and December 2016 if you use this trend line equation? f. How reliable is this forecast from a statistical perspective? g. Compute the MAD of the trend line method used in part (d). Use the forecasts from January 2016 to October 2016 to compute the MAD of the method. h. What is the interpretation of MAD? i. Explain clearly why it is important to compute and track forecast accuracy. Problem 2 The Global Gourmet Coffee Company (GGCC) is a distributor and processor of different blends of coffee. GGCC currently has 12 different coffees that it offers to gourmet shops in one-pound bags. The major cost is raw materials. There is also a substantial amount of manufacturing overhead in the predominantly automated roasting and packing processes. The company uses relatively little direct labor. GGCC prices its coffee at full product cost, including allocated overhead, plus a markup of 30 percent. The company competes primarily on the quality of its products, but customers are price-conscious as well. Data for the 2018 budget include manufacturing overhead of $12,000,000, which is allocated based on each product's direct-labor cost. The budgeted direct-labor cost for 2018 totals $1,200,000. Based on the sales budget and raw-material budget, purchases and use of raw materials (coffee beans) will total $5,800,000. The expected prime costs for one-pound bags of two of the company's products are as follows: Direct material Direct labor Jamaican $2.90 .40 Columbian $3.90 .40 An analysis of the 2018 budgeted manufacturing-overhead costs is shown in the following chart. Activity Cost driver Purchasing Material Handling Quality Control Roasting Blending Packaging Total Purchase Orders Setups Batches Roasting Hours Blending Hours Packaging Hours Budgeted Activity Level 2,316 3,600 1,440 192,200 67,200 52,000 Budgeted Cost $2,316,000 2,880,000 576,000 3,844,000 1,344,000 1,040,000 $12,000,000 Production data for Jamaican and Colombian coffees in 2018 are as follows: Budgeted Sales Batch Size Setups Purchase Order size Roasting time Blending time Packaging time Jamaican 2,000 lb 500 lb 3 per batch 500 lb 1 hr/200 lb .5 hr/200 lb .1 hr/200 lb Columbian 100,000 lb 20,000 lb 3 per batch 50,000 lb 1 hr/200 lb .5 hr/200 .1 hr/200 lb 1. Using GGCC's current product-costing system: a. Determine the company's predetermined overhead rate using direct-labor cost as the single cost driver. b. Determine the full product costs and selling prices of one pound of Jamaican coffee and one pound of Colombian coffee. 2. Develop a new product cost, using an activity-based costing approach, for one pound of Jamaican coffee and one pound of Colombian coffee. 3. Fully discuss the implications of the activity-based costing system with respect to: a. The use of direct labor as the sole basis for applying overhead to products? b. The use of the existing product-costing system as the basis for pricing? Problem 3 Eagle Machines purchases a subcomponent critical for its most popular product from another manufacturer. The annual demand for the subcomponent is 5000 units. The holding cost is assessed at 20% of the price of the subcomponent. The manufacturer of the subcomponent charges Eagle $30 for each unit if Eagle orders less than 200 units and $28 for each unit if Eagle orders 200 units or more. Eagle's cost to place an order is $20. In responding to the questions below, round off your responses to the nearest unit, number of orders, etc. a. What is the optimal order quantity for this subcomponent if the EOQ model is used? b. What is the total of annual relevant costs of Eagle Machines (considering the cost of inventory holding and cost of ordering)? c. If the EOQ model is used, what is the optimal number of orders to be placed in a year? d. If the manufacturer's lead time is 1 week and Eagle operates 50 weeks a year, what is the reorder point for the product? e. What is your interpretation of the reorder point you calculated in part (d)? Problem 4 ABC Corporation makes three different products. Data concerning these products appear below: Sales Volume Selling Price per unit Variable costs per unit Product A 200,000 $3.30 $2.50 Product B 400,000 $3.00 $1.40 Product C 800,000 $1.70 $0.50 Total fixed expenses are $800,000 per year. The company has no beginning or ending work in process or finished goods inventories. a. What is the corporation's current operating income? b. What is the corporation's over-all break-even point in dollar sales? c. What is the corporation's current operating leverage and what does it imply? d. Of the total fixed expenses of $800,000, $40,000 could be avoided if Product A is dropped, $160,000 if Product B is dropped, and $120,000 if Product C is dropped. The remaining fixed expenses of $480,000 consist of common fixed expenses such as administrative salaries and rent on the production facility that could be avoided only by going out of business entirely. i. What is the break-even point in unit sales for each product line? Ii Should any of these product lines be dropped? Explain why or why not. iii. If the corporation sells exactly the break-even quantity of each product, what will be the overall profit of the company? Explain this result. Problem 5 The diagram below represents a process where three components are made stations A1, A2, and A3 (one component is made at each station). These components are then assembled at Station B and moved through the rest of the process, where some additional work is completed at stations C and D. One and only one person is allowed to work at each station. The times given below for each station represent the amount of work that needs to be done at that station by that person, with no processing time variation. No inventory is allowed to build in the system. Station A1 3 minutes Station A2 2minutes Station B 5 minutes Station C 4 minutes Station D 1 minute Station A3 3 minutes a) What is the hourly throughput (output) of the process? b) Which station is the bottleneck? c) What does it mean for a station to be the bottleneck of a process? d) What is the flow time of a unit consisting of the three components? e) What is the cycle time of the process? f) What is the utilization rate of each worker? g) Can a service company benefit from process analysis? Explain clearly why or why not. Problem 6 You have been asked as a consultant to assist Lifetime Adventures Company in recovering from a recent computer hacking incident. The company produces a survival kit. Unfortunately, due to the computer hacking incident, only the following data from the standard cost card relating to production of a single survival kit are given below: Standard Price Standard cost per kit Standard Quantity Direct material $6 per yard Direct labor Variable production overhead $3 per direct labor hour Total Through interviews with company personnel, you learned variable production overhead is applied to production on the basis of direct labor-hours. During a recent month, 1,000 survival kits were produced and sold. The only other information recovered from the company's database relating to the recent month's production and the related variance analysis is given below. Variance Analysis Materials Used Direct Labor Standard Cost for 1,000 kits $16,800 $10,500 Actual costs incurred $15,000 a. Materials Price Variance Materials Quantity Variance b. Labor Rate Variance c. Labor Efficiency Variance d. Variable Overhead Rate Variance e. Variable Overhead Efficiency Variance Variable Production Overhead $4,200 $3,600 $1,200 Further interviews with company personnel revealed the following for the recent month's production Actual Direct Labor Hours Worked: 1,500: Standard variable overhead rate per direct labor hour $3.00. Difference between the standard and actual cost per kit during the recent month: $.15 Favorable. The company had no beginning or ending inventories of materials. Determine the variances shown in bold in the table above. Show all your work and calculations Problem 7 The following is the activity list for a project: Activity Predecessor A B C D E F G H A A B B C D E, F NORMAL DURATION (weeks) 3 5 4 6 5 7 3 4 CRASH DURATION (WEEKS) 2 4 3 4 4 6 2 3 NORMAL COST CRASH COST $12,000 $20,000 $16,000 $24,000 $ 20,000 $ 28,000 $ 14,000 $ 18,000 $24,000 $22,000 $20,000 $ 28,000 $24,000 $ 38,000 $20,000 $30,000 a. Draw an activity on node (AON) diagram to represent the project. b. Apply the critical path method and determine the following using the normal durations of the activities: i. The project duration, ii. The earliest start and finish time of each activity, iii. The latest start and finish time of each activity, iv. The slack of each activity, v. The critical path(s). c. Which activity or activities should be crashed and in which sequence to reduce the project duration by 2 weeks? d. What is the resulting total project cost? e. What is (are) the critical path(s) of the project after crashing? Question 1 Question 2 Question 4 Operating Income is negative (-$200,660) as per the data given b. Break even volume = Fixed Costs/(Price - Variable costs) Fixed Costs = 800,000 Total sales value = 108 + 209 + 408 = 725 (thousand) Price per unit = 1.70*108/725 + 1.90*209/725 + 1.25*408/725 = 1.5044 Variable cost per unit = 1.19*108/725 + 0.76*209/725 + 0.50*408/725 = 0.6777 Hence break even quantity = 800,000/(1.5044-0.6777) = 967,702.91 = 967,703 Units c.i. If company sells only A without B and C. total fixed costs = 800,000-160,000 -120,000 = 520,000 Breakeven point (A) = 520,000/(1.70-1.19) = 1,019,608 Units If company sells only B without A and C. total fixed costs = 800,000-40,000 -120,000 = 640,000 Breakeven (B) = 640,000/(1.90-0.76) = 561,404 Units If company sells on C without B and A, total fixed costs = 800,000 -40,000-160,000 = 600,000 Breakeven (C) = 600,000/(1.25-0.5) = 800,000 Units c.ii I would recommend Product A to be dropped since its breakeven is very high (more than 1 million u ciii. Breakeven quantity means that quantity which will make no profit or no loss Hence the overall profit of the company will be ZERO (0) if it sells at breakeven quantity d. I would recommend it dropping Product A to increase profitability 0 -120,000 = 520,000 0,000 = 640,000 ,000 = 600,000 h (more than 1 million units) Question 3 a) Optimal order Quantity = [2 x (Annual Usage in Units x Setup Cost) / Annual Carrying Cost per Unit = [2 x (5000 x 20) / (30x20%)](1/2) = 182.57 = 188 units b) Total annual relevant costs = Yearly Holding Cost + Yearly Ordering Cost = Q/2 x Holding cost + Annual usage/Q x Ordering cost = 188/2 x 6 + 5000/188 x 20 = 1095.91 = $1096 c) Optimal number of orders = Annual usage/Q = 5000/188 = 27 orders d) Safety stock should be maintained for atleast 14 days. Safety stock = 14 days x Average daily usage = 14 x 5000/(50x7) = 200 Reorder point = (Average daily usage x Delivery lead time) + Safety stock = (5000/(50x7) x 7) + 200 = 99.81 + 200 = 300 units e) Whenever the quantity reduces to 300 units, the company should order new quantities. Carrying Cost per Unit](1/2) Q x Ordering cost Question 5 Average time = time for A1 (since it is greater than A2) + B+C+D+E = 0.7+ 0.4+ 0.35 +0.65+ 0.60 +0.55 = 3.25 min Average hourly output = 60/3.25 = 18.46 Question 6 Question 1 Question 2 Question 4 Operating Income is negative (-$200,660) as per the data given b. Break even volume = Fixed Costs/(Price - Variable costs) Fixed Costs = 800,000 Total sales value = 108 + 209 + 408 = 725 (thousand) Price per unit = 1.70*108/725 + 1.90*209/725 + 1.25*408/725 = 1.5044 Variable cost per unit = 1.19*108/725 + 0.76*209/725 + 0.50*408/725 = 0.6777 Hence break even quantity = 800,000/(1.5044-0.6777) = 967,702.91 = 967,703 Units c.i. If company sells only A without B and C. total fixed costs = 800,000-160,000 -120,000 = 520,000 Breakeven point (A) = 520,000/(1.70-1.19) = 1,019,608 Units If company sells only B without A and C. total fixed costs = 800,000-40,000 -120,000 = 640,000 Breakeven (B) = 640,000/(1.90-0.76) = 561,404 Units If company sells on C without B and A, total fixed costs = 800,000 -40,000-160,000 = 600,000 Breakeven (C) = 600,000/(1.25-0.5) = 800,000 Units c.ii I would recommend Product A to be dropped since its breakeven is very high (more than 1 million u ciii. Breakeven quantity means that quantity which will make no profit or no loss Hence the overall profit of the company will be ZERO (0) if it sells at breakeven quantity d. I would recommend it dropping Product A to increase profitability 0 -120,000 = 520,000 0,000 = 640,000 ,000 = 600,000 h (more than 1 million units) Question 3 a) Optimal order Quantity = [2 x (Annual Usage in Units x Setup Cost) / Annual Carrying Cost per Unit = [2 x (5000 x 20) / (30x20%)](1/2) = 182.57 = 188 units b) Total annual relevant costs = Yearly Holding Cost + Yearly Ordering Cost = Q/2 x Holding cost + Annual usage/Q x Ordering cost = 188/2 x 6 + 5000/188 x 20 = 1095.91 = $1096 c) Optimal number of orders = Annual usage/Q = 5000/188 = 27 orders d) Safety stock should be maintained for atleast 14 days. Safety stock = 14 days x Average daily usage = 14 x 5000/(50x7) = 200 Reorder point = (Average daily usage x Delivery lead time) + Safety stock = (5000/(50x7) x 7) + 200 = 99.81 + 200 = 300 units e) Whenever the quantity reduces to 300 units, the company should order new quantities. Carrying Cost per Unit](1/2) Q x Ordering cost Question 5 Average time = time for A1 (since it is greater than A2) + B+C+D+E = 0.7+ 0.4+ 0.35 +0.65+ 0.60 +0.55 = 3.25 min Average hourly output = 60/3.25 = 18.46 Question 6