help! please show work in excel format. Use Parenthesis to mark unfavorables.
Osiek Motors Monthly Performance Report For the Month of May, 202x Planning Units Budget18,000Actual14,000Variance(4,000) Sales Variable Mfg Costs: Direct Material Direct Labor Other Variable Mfg Total Var Mfg Costs Variable Shipping Costs: Total Var Ship Costs Total Variable Costs Contribution Margin Total Fixed Costs Operating Income(Loss) $864,000$686,000$(178,000) $108,000$85,40022,600 $288,000$246,00042,000 $88,000$72,60015,400 $484,000$404,00080,000 $28,800$28,000800 $512,800$432,00080,800 $351,200$254,000$(97,200) $260,000$261,200(1,200) $91,200$(7,200)$(98,400) 06/02/202x 11:30pm To: Lydia Branton From: Charles Garrison Subject: May 202x Monthly Performance Report As promised, here is the performance report for May. I'm sure you will find the bottom line disappointing, but plant performance is quite good. Note that the plant is favorable against budget for every cost category except supervision, and supervision is higher only because the other costs were controlled! Because I worked late, I'm taking tomorrow off. I have a 9.00am tee time, so I will not be available for any phone calls or questions. Here is the additional data you requested: 1. Budgeted costs per unit are $6 for Direct Material and $16 for Direct Labor, $4,89 for Other Variable Mfg. Costs and $1.60 for Variable Shipping costs. 2. Actual selling price per unit is $49 versus budget of $48. Osiek Motors Case When Lydia Branton arrived at her office at Osiek Motors on June 3, she was pleased to find the monthly performance report on her desk. Lydia's job as division controller was to analyze results of operations each month and to prepare a narrative report on monthly results which she would send to McCadden Enterprises, Osiek's parent company. Osiek Motors was a wholly owned subsidiary of McCadden. The atmosphere at Osiek had been anxious throughout the month of May. Today would provide the opportunity to find out how well Osiek's management had responded to the recent loss of a major customer contract. Osiek Motors manufactured small electric motors which were sold to household appliance manufacturers. What started as a small, family-owned business had grown to become a major player in the industry. It was at this point that McCadden came in with an offer that the Osiek family could not refuse. After the sale of Osiek closed, few changes had been made by McCadden in either processes or staff, as they wanted to see how well the business functioned before making any changes. Three months after the sale, Lydia Branton, who had earned her B.S. in Accounting from Ohio State University, transferred from McCadden's headquarters controller's office to Osiek Motors. She was joined soon after by Savannah Turner, also from McCadden, to be the new general manager at Osiek. Disaster struck soon after. A major appliance maker, and long-time Osiek customer, had cancelled their contract at the end of April and moved production to Southeast Asia. Because of the lost contract, and the potential negative financial impact, Lydia had asked the plant chief accountant, Charles Garrison, to prepare the monthly performance report for May as soon as possible. She was amazed that the report had been prepared so quickly. Back at headquarters it had taken at least a week to receive the monthly reports. Even though Charles had promised that he could prepare the report in a single day with some overtime, she was surprised that he had done so. Osiek had prepared their planning budget for 202x based on estimated sales and production expenses. Because sales were not seasonal, the monthly budget was simply 1/12 of the annual planning budget. No adjustments had been made to the planning budget when the contract had been lost in April. A glance at the monthly performance report for May confirmed Lydia's worst fears. Instead of the budgeted operating profit of $91,200, Osiek had lost $7,200 in May. Even with the impact of the lost contract, Lydia had expected a better result. The original performance report is shown in the Excel file that accompanies this case. The following is a reproduction of Charles's memo to Lydia that was attached to the report: 1. Using the Planning Budget data, what are Osiek's break-even units? (5 pts). What does that tell you about May's financial results? (5 pts) 2. Using the Planning Budget data, what was the total expected cost per unit if all manufacturing and shipping overhead was allocated to production? (10 pts) 3. Calculate the Actual unit cost. How does that compare with the Planning Budget? (10 pts) 4. Comment on Charles's Performance Report. Do you think it should be submitted as is, or revised? (10 pts) 5. Prepare your own Monthly Performance Report in Excel, using the textbook format from Exhibit 9-8 (50 pts) 6. What is the Total Revenue and Spending Variance? (5 pts) What is the Total Activity Variance? (5 pts) Be sure to indicate whether they are favorable or unfavorable
