how to do this assignment, i want direct answers and excel chart to me back with solutions or explanations of how to solve this problem.thanks

NOTE ON VARIANCE ANALYSIS Variance analysis is an analytical process for evaluating and explaining business performance by comparing it to an accepted benchmark. It is an important component of a business' Management Control Cycle as it provides information about what is happening in the business (both good and bad) upon which management can take action. Consider the following simple example: A trip to Grandma's house in Washington D.C. Grandma offered to pay for your trip ... but she's tough! Explain to Grandma why it cost you $4.00 more than you had planned to drive to Washington from Boston: Miles Driven X Gallons per Mile = Gallons Used PLAN 500 1/20 25 $2.00 $50.00 ACTUAL 540 1/18 30 $1.80 $54.00 X $/Gallon = Total Cost VARIANCE $ (4.00) Unfavorable In this example, the process is the development of a plan and measurement of actual results against that plan. The plan becomes the benchmark. In order to explain the unfavorable $4.00 extra cost, you must evaluate each of the variables impacting that cost versus the benchmark. The variables that need to be examined fall into 3 categories: VOLUME: Typically units in a manufacturing environment but, more broadly, whatever measure of output that is meaningful for the analysis. For instance, miles would be the volume variable in Grandma's; hours (or # billable professionals x average hours per period) would be the volume variable in a professional firm (consulting, law etc.). MIX: When more than one product or service is provided, the percentage each represents of total volume is its relative mix. For instance, if plan is to sell 60 oranges and 40 apples, the planned mix is 60% oranges and 40% apples. If actual results show sale of 90 oranges and 30 apples, the actual mix is 75% oranges and 25% apples. The increase in total units sold from plan of 100 to actual of 120 would be explained in the volume variable above. There is no mix variable in Grandma's. RATE: There are many types of rate variables that can be used to explain variances. In Grandma's, there is a gasoline utilization rate - gallons per mile - and a gasoline cost rate - $ per gallon. Other rates may include market share, sales price, labor rate per hour, material used per unit, etc. This leads to three basic rules of variance analysis: 1 7/24/2016 1. Always change variables in the sequence of VOLUME, MIX, RATE 2. Change only ONE variable at a time; once changed it stays at the new number 3. When multiple rates exist, change the $ rate last so you are pricing out actual quantity The following chart may help visualize the above rules: START Step 1 Volume Step 2 Mix Step 3 Rate 1 Step 3 Rate 2= END VOLUME Plan Actual Actual Actual Actual MIX Plan Plan Actual Actual Actual RATE 1 Plan Plan Plan Actual Actual RATE 2 Plan Plan Plan Plan Actual ** We will defer mix variances until later in the semester and focus on volume and rate variances for now. There are two approaches to doing variance analysis, both of which yield the same result. The first approach follows the framework in the above chart. It changes the variable to actual and recomputes the total cost. It then calculates the change in total cost from the prior line giving the variance caused by that variable. Applying this methodology to your trip to Grandma's in Washington: START VOLUME 500 RATE 1 x 1/20 RATE 2 x $2.00 = TOTAL $50.00 Step 1 Step 2 540 540 x 1/20 x 1/18 x x $2.00 $2.00 = = $54.00 $60.00 $(4.00) Unfav. $(6.00) Unfav. Volume Gals/Mile END 540 1/18 $1.80 = $54.00 $ 6.00 Fav. $/Gallon TOTAL VARIANCE VARIANCE $(4.00) Unfav. The second approach uses the difference in each variable to calculate the variance directly: Volume: (500 - 540) x 1/20 x $2.00 = $(4.00) Unfav. Volume Gals/Mile: 540 (actual) x (1/20 - 1/18) x $2.00 = $(6.00) Unfav. Gals/Mile $/Gallon: 540 = $ 6.00 Fav. $/Gallon x 1/18 (actual) x ($2.00 - $1.80) TOTAL VARIANCE $(4.00) Unfav. The results are identical; the only difference is where the variance is calculated. Now use the results of the variance analysis to tell Grandma what happened and WHAT ACTIONS you would take next time to avoid the cost overrun: What Happened Corrective Action Plan 1. 2. 3. \"BRACKETS ARE BAD\" Favorable variances- spending less than plan, bringing in more revenue/profit than plan - are POSITIVE values. Unfavorable variances- spending more than plan, bringing in less revenue/profit than plan - are NEGATIVE values. They are best displayed inside brackets ( XXX.XX) In order to make the variance calculations come out with the correct +/- signs, you need to use the following conventions: Revenue/profit related items use Actual - Budget Expense related items use Budget - Actual Please make sure you label your variances with a \"U\" for Unfavorable and an \"F\" for Favorable 3 7/24/2016 Process for doing Variance Analysis Variance analysis is most commonly done between a budget and actual results - how did you do versus budget? There are many other pairings, however, for which variance analysis can be useful: Budget and a current forecast Strategic plan and budget Current forecast to next year's budget Current actual results to prior period actual results To do variance analysis, it is helpful to consider a six step process: 1. 2. 3. 4. 5. 6. Set the benchmark (budget, strategic plan, etc.) Record the \"actual\" results to compare to benchmark Compute the variances Assign responsibility Determine causes of significant variances (both good and bad) Take corrective action Let's define the primary variances we will work with at this point in the semester: Sales Volume Variance: Change in \"unit\" volume x budget contribution margin per unit. This tells you how much more or less contribution margin you should have earned as a result of the change in volume from budget. Sales Price Variance: \"actual\" volume x change in price. This tells you the impact your pricing actions had on the actual volume of units. Material Usage Variance: \"actual\" volume x change in quantity used per unit x budget cost per unit. This tells you how much more or less you should have spent on materials you actually used versus budget to make your product. Material Cost Variance: \"actual\" volume x \"actual\" quantity used per unit x change in cost per unit. This tells you how much more or less you actually paid versus plan for the materials you actually used. Labor Usage (Efficiency) Variance: \"actual\" volume x change in amount of time used per unit x budget cost per unit of time. This tells you how much more or less you should have spent on labor you actually used versus budget to make your product. Labor Cost Variance: \"actual\" volume x \"actual\" time used per unit x change in cost per unit of time. This tells you how much more or less you actually paid versus plan for the labor you actually used. Let's work through a simple variance analysis problem to demonstrate the six step process for doing variance analysis: Sarah's Soaps. Sarah makes hand molded soap bars for sale to gift stores in packages of 12 bars. Her business performance last month was: Budget (Step 1) Actual (Step 2) Favorable(Unfav) Units (Dozens) 10,000 11,000 Revenue Materials Direct Labor Contribution Margin $100,000 20,000 16,000 $ 64,000 $104,500 21,780 18,150 $ 64,570 Revenue/Dozen Material Pounds/Dozen Material $/Pounds DL Hours/Dozen DL $/Hour $10.00 4.0 $ .50 0.20 $ 8.00 $ 9.50 4.4 $ .45 0.22 $ 7.50 1,000 F $ 4,500 (1,780) (2,150) $ 570 F U U F At first glance, it appears that Sarah did well, earning $570 more contribution margin than budget. But let's do some variance analysis (Step 3) to see what really happened. Sales Volume Variance = (11,000 - 10,000) x ($10.00 - $3.60) = $ 6,400 F (note: Actual - Budget) Sales Price Variance 11,000 x ($9.50 -$10.00) = = $(5,500) U \" \" Material Usage Variance = 11,000 x (4.0 - 4.4) x $0.50 = $(2,200) U (note: Budget - Actual) Material Cost Variance = 11,000 x 4.4 x ($0.50 - $0.45) = $ 2,420 F DL Usage/Effic. Var. = 11,000 x (0.20 - 0.22) x $8.00 = $(1,760) U DL Cost Variance = 11,000 x 0.22 x ($8.00 - $7.50) Total of ALL Variances MUST equal $570 F !!! \" \" = $ 1,210 F =$ \" \" \" \" 570 F Ah hah! It does!!!! So who does Sarah hold accountable for the results (Step 4)? Sales & Marketing is usually responsible for customer relationships and therefore both number of units sold to customers and price. How did they do? 5 7/24/2016 Sales & Marketing: Sales Volume Variance Sales Price Variance Total $ 6,400 F (5,500) U $ 900 F They dropped the price by $0.50 per dozen and saw some favorable elasticity - an extra 1,000 units - resulting in a net improvement for the business of $900 - looks like a good job for the month. Manufacturing usually has responsibility for product cost including both the acquisition of resources (raw material and direct labor in this case) and manufacturing operations. The purchasing department within manufacturing would be specifically responsible for acquisition while the production department within manufacturing would be responsible for manufacturing operations. How did they do? Purchasing: Material Cost Variance DL Cost Variance Total $ 2,420 F 1,210 F $ 3,630 F Production: Material Usage Variance DL Usage/Efficiency Variance Total $(2,200) U (1,760) U $(3,960) U So what happened? We need to go talk to the three departments and try to find out (Step 5). Clearly both Sales & Marketing did well while production had a bad month. Each department needs to explain what happened. Marketing should explain why they dropped the price - a lucky guess? Or based on a thorough market study? Sales should be able to discuss customer reaction to the price reduction - are they stocking up at the lower price and potentially reducing future purchases? Purchasing should be able to explain what was happening in the market place to drive costs per unit down - were they able to maintain adequate levels of material quality and DL staff experience? Production needs to explain why they were significantly less efficient than budget - did machines break down? Were DL less experienced than necessary? Was raw material quality off causing production and scrap problems? Then, based on findings from your interviews and analysis, take corrective actions (Step 6). These will obviously depend on the results of your analysis. One question many students have is \"why don't the variances add up to the total variance for each line item in the Income Statement?\" The reason is quite simple. Let's use the total material variance of $(1,780) as an example. There are three components making up this variance: Volume: Sarah shipped an extra 1000 units x 4.0 pounds/unit x $0.50/pound = $(2,000) U Usage: Prod'n used 4.4 pounds/unit vs. 4.0 (extra 0.4) x $0.50/pound x 11,000 units = $(2,200) U Cost: Purch. paid $0.45/pound vs. $0.50 (saved $0.05/pound) x (4.4 x 11,000) pounds = $ 2,420 F Total Material Variance $(1,780) U The usage and cost variances were calculated above; the volume variance is part of Sales Volume Variance. Problem Assignment Let's expand our view of Sarah's Soaps with some additional information. SARAH'S SOAPS VARIANCE ANALYSIS 7 7/24/2016 Market Size(12 Packs) Sarah's Soap 12 Packs Share % Revenue Material Direct Labor Variable Mfg OH Contribution Margin Manufacturing Marketing/Advertising Engineering Administration Profit Budget 50000 10000 20.0% Actual 60000 11000 18.3% Fav(Unfav) 10000 1000 -1.7% 100000 20000 16000 15000 49000 10000 8000 10000 10000 11000 104500 21780 18150 13860 50710 11000 10000 8000 10500 11210 4500 (1780) (2150) 1140 1710 (1000) (2000) 2000 (500) 210 Specifically, we learn: The market for Sarah's soap is budgeted to be 50,000 dozen per month (therefore she is budgeting a 20% market share - 10,000/50,000). The actual market for the month turned out to be 60,000 dozen In addition to variable material and direct labor costs, Sarah's manufacturing operations incurs variable overhead - utilities for machinery, fringe benefits for direct labor, etc. She has budgeted this at 0.5 machine hours per dozen at a cost of $3.00 per machine hour; for the month. 0.45 machine hours were used per dozen at an actual cost of $2.80 per machine hour Sarah also has four categories of fixed costs with budget and actual results as follows: Budget Actual Manufacturing Marketing/Sales Engineering Administration 10000 8000 10000 10000 11000 10000 8000 10500 Using the blank template posted on Blackboard, re-compute all the variances for Sarah's Soaps for the month. Note: /\\ in the template means change in value. Additional variances you will need to calculate are: Market size variance Market share variance (together these two equal sales volume variance) Variable OH usage/efficiency variance (similar to DL usage/efficiency variance) Variable OH cost variance (similar to DL cost variance) The four fixed cost variances (for this class, we will use just the raw differences as above) Explain the $210 variance for the month and recommend possible corrective actions using the six step process. Sheet1 Variance Analysis Template Note: /\\ means change in value Sales & Marketing Market Size Variance Budget Share X /\\ Mkt Size X 10000 20.0% Budget (Price - Cost)/Unit 4.90 Market Share Variance Actual Mkt Size X /\\ Mkt Share X Budget (Price - Cost)/Unit Sales Volume Variance /\\ Units Budget (Price - Cost)/Unit X NOTE: Sales Vol. Var. is sum of Mkt Size and Mkt Share variances Price Variance Actual Mkt Size X Mktg/Adv Variance Act Mkt Share X Budget Mktg/Adv - Fav(Unfav) 9800 /\\ Price/Unit Act. Mktg/Adv Total Sales & Marketing Variance Manufacturing Mat'l Usage Variance Actual Units X /\\ Pounds/Unit X Budget $/Pound Mat'l Cost Variance Actual Units X Act Pounds/Unit X /\\ $/Pound Labor Efficiency Var Actual Units X /\\ Hours/Unit Labor Cost Variance Actual Units X Act Hours/Unit X Var. OH Usage Varianc Actual Units X /\\ Mach hrs/unit X budget $/mach hr Actual Units X Act Mach hrs/unit /\\ $/Mach hr Fixed Mfg. OH X Budget Fixed OH - Total Manufacturing Variance Budget Budget $/Hour /\\ $/Hour Actual Fixed OH Actual Engineering Variance Administration Variance Total Variances - MUST equal Profit Variance 07/22/2016 11:47:24 Page 1 qattachments_7b9fe063fd4b2e55d0848750bc7120c809ff33b3.xls Sheet1 Variance Analysis Template Note: /\\ means change in value Sales & Marketing Market Size Variance Budget Share X Budget (Price - Cost)/Unit Market Share Variance Actual Mkt Size X /\\ Mkt Share X Budget (Price - Cost)/Unit 1,000 Sales Volume Variance /\\ Units Budget (Price - Cost)/Unit 6,400 /\\ Mkt Size X X NOTE: Sales Vol. Var. is sum of Mkt Size and Mkt Share variances Price Variance Actual Mkt Size X Mktg/Adv Variance Act Mkt Share X Budget Mktg/Adv - /\\ Price/Unit Act. Mktg/Adv Total Sales & Marketing Variance Fav(Unfav) 10,000 $ (5,500) $ (2,000) $ (7,500) Manufacturing Mat'l Usage Variance Actual Units X /\\ Pounds/Unit X Budget $/Pound -2200 Mat'l Cost Variance Actual Units X Act Pounds/Unit X /\\ $/Pound 2420 Labor Efficiency Var Actual Units X /\\ Hours/Unit Budget $/Hour -1760 Labor Cost Variance Actual Units X Act Hours/Unit X /\\ $/Hour 1210 Var. OH Usage Varianc Actual Units X /\\ Mach hrs/unit X budget $/mach hr Actual Units X Act Mach hrs/unit /\\ $/Mach hr Fixed Mfg. OH X Budget Fixed OH - Total Manufacturing Variance Budget 550 $ Actual Fixed OH Actual 2,200 -1500 $ 700 Engineering Variance Administration Variance Total Variances - MUST equal Profit Variance 07/25/2016 22:03:36 Page 1 qattachments_2729e974da87f4176ce57247b7fd732f4aceeb1b.xls Sheet1 Fav Fav Fav UnFav UnFAV Fav UnFav Fav Unfav Fav Fav Fav Unfav 07/25/2016 22:03:36 Page 2 qattachments_2729e974da87f4176ce57247b7fd732f4aceeb1b.xls