References Mailings Review View Home Insert Design Layout PM on Sunday, March 29, 2020 Acrobat 9 Tell me what you want to do LLIC C ALCROSSCUSSCHULE The Case Required: Linda Thueblood, president of Pro Skate, was stanng at the most recent quarterly performance report and was not pleased How can this be possible she asked of her senior management team "I thought the market for our products had improved and that all we needed to do was maintain our budgeted market share But this report tells me that our profits are below expectations, and I do not understand that she then turned her attention to the manufacturing manager, wondering what story was going to emerge from his side The performance report she was reviewing contained the following information 1 Complete a vanance analysis in Excel You may use the variance tree method of the equation method. Be sure to label each of the variances as layoutable. (F) or unfavourable ) Provide a brief 30 word, maximum) analysis of the variances that have occurred, and suggest al least two possible reasons why some of the variances happened Remember that some variances can affect other variances (le materials and labout) so your analysis should be bg picture 3 Prepare the standard cost cards for both the professional skale model and the amateur skade model Standard cost cards should include the direct materials, direct labour and variable manufacturing overhead per unit You should also have the standard quantity/hours per unit and the standard price rate per unit, multiplied by each other lo gel standard cos! 4 Using the above information, prepare the state budget for the company in the same format as the performance report (income statement provided above Recall that the static budget is based on the planned / onginal budget sales levels, and is not a flexible budget $2,880,000 $617,256 992.750 Sales Less Cost of goods sold Variable Fixed Grass margin Less Selling and administration Variable Fixed Operating income 1.610,006 $1,269,994 The suggested format in Excel is to have 3 tabs (one for Required #1 and 2 as they are linked one for Required #3, and one for Required #4) $172,800 450.000 622,800 $ 647,194 Hints Other information is as follows Start with the vanance analysis - complete what information you know from above and determine what you still need to complete the analysis, then attempt to calculate or find it The static budget is like a puzzle, you really need to think about what the budgeted numbers would have been use the informaton above to figure out the static budget and then solve for the missing pieces Remember that gross margin and contribution margin are different a b c d Overall profit vanance was $107,294 U Sales revenues were $144,000 lower than the budgeted amount Contrbution margin was $105,144 lower than the budgeted amount Actual market share was about 0 535% lower than the budgeted 10% ProSkate manufactures two types of skates professional and amateur The following additional information is available 1 Cheating and plaqsansm This is an individual assignment You are to prepare the vanance analysis project using Excel Start your project with a blank Excel spreadsheet The Spreadsheet should not be someone else's or downloaded from anywhere on the internet Sharing spreadsheets is considered cheating Allowing another student to use or copy your spreadsheet is plagiarism ALL instances of cheating or plagiarism will be reported to the Dean's office and may result in suspension from the Business Program 2 3 4 5 The company sold 7,200 pairs of prolessional skates and 18,000 pairs of amateur skates during the quarter compared with the budgeted quantities of 8,000 and 17,600, respectively Budgeted unit contnbution margins for the professional and amateur models were 185 55 and $40 38, respectively Direct materials were purchased at the budgeted price of $28 per kilogram, all matenals purchased were used during the penod Direct labou was paid $0 50 per hour higher than the budgeted amount of $14 per hour Total direct matenals vanance amounted to $5,040 (Uglavourable) Direct materials and direct labour used for the amateur model were the same as the standard quandly and hours (0 20 kilograms per unit and 0 40 hours per una respectively) Standard quandly and hours per unit of the professional model were 0.40 kilograms and 0.75 hours The total direct matenals and direct labour used for the professional model during the quarter were 3,000 klograrns and 5,040 hours, respectively Direct labour-hours used for the professional model were less than the budgeted quantity by 0 05 hours per und 7 References Mailings Review View Home Insert Design Layout PM on Sunday, March 29, 2020 Acrobat 9 Tell me what you want to do LLIC C ALCROSSCUSSCHULE The Case Required: Linda Thueblood, president of Pro Skate, was stanng at the most recent quarterly performance report and was not pleased How can this be possible she asked of her senior management team "I thought the market for our products had improved and that all we needed to do was maintain our budgeted market share But this report tells me that our profits are below expectations, and I do not understand that she then turned her attention to the manufacturing manager, wondering what story was going to emerge from his side The performance report she was reviewing contained the following information 1 Complete a vanance analysis in Excel You may use the variance tree method of the equation method. Be sure to label each of the variances as layoutable. (F) or unfavourable ) Provide a brief 30 word, maximum) analysis of the variances that have occurred, and suggest al least two possible reasons why some of the variances happened Remember that some variances can affect other variances (le materials and labout) so your analysis should be bg picture 3 Prepare the standard cost cards for both the professional skale model and the amateur skade model Standard cost cards should include the direct materials, direct labour and variable manufacturing overhead per unit You should also have the standard quantity/hours per unit and the standard price rate per unit, multiplied by each other lo gel standard cos! 4 Using the above information, prepare the state budget for the company in the same format as the performance report (income statement provided above Recall that the static budget is based on the planned / onginal budget sales levels, and is not a flexible budget $2,880,000 $617,256 992.750 Sales Less Cost of goods sold Variable Fixed Grass margin Less Selling and administration Variable Fixed Operating income 1.610,006 $1,269,994 The suggested format in Excel is to have 3 tabs (one for Required #1 and 2 as they are linked one for Required #3, and one for Required #4) $172,800 450.000 622,800 $ 647,194 Hints Other information is as follows Start with the vanance analysis - complete what information you know from above and determine what you still need to complete the analysis, then attempt to calculate or find it The static budget is like a puzzle, you really need to think about what the budgeted numbers would have been use the informaton above to figure out the static budget and then solve for the missing pieces Remember that gross margin and contribution margin are different a b c d Overall profit vanance was $107,294 U Sales revenues were $144,000 lower than the budgeted amount Contrbution margin was $105,144 lower than the budgeted amount Actual market share was about 0 535% lower than the budgeted 10% ProSkate manufactures two types of skates professional and amateur The following additional information is available 1 Cheating and plaqsansm This is an individual assignment You are to prepare the vanance analysis project using Excel Start your project with a blank Excel spreadsheet The Spreadsheet should not be someone else's or downloaded from anywhere on the internet Sharing spreadsheets is considered cheating Allowing another student to use or copy your spreadsheet is plagiarism ALL instances of cheating or plagiarism will be reported to the Dean's office and may result in suspension from the Business Program 2 3 4 5 The company sold 7,200 pairs of prolessional skates and 18,000 pairs of amateur skates during the quarter compared with the budgeted quantities of 8,000 and 17,600, respectively Budgeted unit contnbution margins for the professional and amateur models were 185 55 and $40 38, respectively Direct materials were purchased at the budgeted price of $28 per kilogram, all matenals purchased were used during the penod Direct labou was paid $0 50 per hour higher than the budgeted amount of $14 per hour Total direct matenals vanance amounted to $5,040 (Uglavourable) Direct materials and direct labour used for the amateur model were the same as the standard quandly and hours (0 20 kilograms per unit and 0 40 hours per una respectively) Standard quandly and hours per unit of the professional model were 0.40 kilograms and 0.75 hours The total direct matenals and direct labour used for the professional model during the quarter were 3,000 klograrns and 5,040 hours, respectively Direct labour-hours used for the professional model were less than the budgeted quantity by 0 05 hours per und 7