You work in the corporate controller's office for Sharpe Glassworks, Inc. Sharpe manufactures 12 ounce beer bottles for smaller breweries who lack the scale or vertical integration to make their own. The bottles are sold to brewers by the case, at prices generally ranging between $25 and 528 per caso, and there are 100 bottles to a case STANDARD COSTING Direct materials Sand makes up about 85% of the weight of the finished product, and is the only material treated as a direct material. All other materials are treated as indirect, and thus part of manufacturing overhead. There are 19,700 grams of sand in one finished goods unit (case). but not all sand purchased makes it into the finished product. Waste and scrap in the normal manufacturing process results in approximately 4% of the sand purchased being lost Sand is purchased by the metric ton (one million grams) The cost from Sharpe's main supplier is $9.15 per metric ton, shipped locally at a cost to Sharpe of $18.25 per metric ton. Direct labor Each case is expected to require 0.26 direct labor hours to complete. This amount comes trom process analysis at Sharpe, and does not include setup and down time, which are expected to average 0.05 hours per case. Sharpe pays its direct laborets an hourly wage of $17.40. Payroll taxes and benefits average $3.65 per direct labor hour Manufacturing overhead Sharpe uses a traditional costing system and allocates overhead on the basis of standard direct labor hours. There are $426,000 of overhead costs expected in 2022, and 2022's unit sales are budgeted at 47.000 cases. JANUARY 2022 RESULTS In January, 2022, sales were $121.700 on 4,540 units sold. 94 metric tons of sand were purchased and used at a total cost, including shipping, of $2,600 Actual direct labor costs were $31,200, and actual direct labor hours were 1.550. Actual overhead costs were $37,500 INSTRUCTIONS 1. Complete the Standard Cost Worksheet in the "Standard Costs" tab. All items should be shown to at least two decimal places, except metric tons per case, which must be shown to at least four decimal places. 2. Complete the Standard Cost Income Statement in the "January Review" tab. 3. Prepare, in a separate Word document, a brief memo describing which variances you, in a controlling function, are most curious about, and why. Who would you contact for explanations about those variances. Your solution should use formulas and cell references instead of keyed in values where possible. For instance, each tab has an area to show your work, and you should use formulas to work out standard quantities, price variances, etc., then cell references to pull those results into the required schedule. Besides reinforcing good practices, this will make your work easier and less subject to error. Load both completed files into Canvas by 8:00 PM on April 26. - Note on decimal places (and why so many of them are needed for standard quantities). If, for instance, your standard quantity was 28,700 grams of sand per unit of finished goods, then your standard quantity would be 28,700 grams divided by one million grams per metric ton, or 0.0287 metric tons.] YOU to dit is safer to stay in Protected View Enable Edit B5 fo AD AE AI B D G H 1 ACC 3020 - Winter 2021 2 Excel Assignment - Standard Costs 3 4 Name: 5 Access ID: 6 7 You work in the corporate controller's office for Sharpe Glassworks, Inc. Sharpe manufactures 12 8 ounce beer bottles for smaller breweries who lack the scale or vertical integration to make their 9 own. The bottles are sold to brewers by the case, at prices generally ranging between $25 and $28 10 per case, and there are 100 bottles to a case. 11 12 13 14 You must enter 15 your AccessID in 16 cell BS. The facts 17 of this case will not 18 appear until you do. 19 20 21 22 24 25 26 27 28 29 30 cot safe to stay in Protected View AI B G H 1 M 1 2 E F Sharpe Glassworks, Inc. Standard Cost Worksheet Standard Quantity metric tons Standard Price Standard Cast X hours 4 Manufacturing Cost Element 5 Direct materials 6 7 Direct labor 8 9 Manufacturing overhead 10 11 Total 12 hours 14 15 Supporting calculations 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 33 Instructions Standard Costs January Review MEADY O a S Sharpe Glassworks, Inc. Income Statement *FOR INTERNAL USE ONLY 4 S Sales revenue 6 Cost of goods sold (at standard) 7 Standard gross profit 8 Variances 9 Materials price 10 Materials quantity 11 Labor price 12 Labor quantity 13 Manufacturing overhead 14 Total variance 15 Gross profit (actual) 16 17 18 19 Supporting calculations: 20 21 22 23 24 25 26 27 28 29 30 31 32 33 cructions Standard Costs January Review Select Paragraph Styles Editing This assignment needs to be done in using Excel ONLY You work in the corporate controller's office for Sharpe Glassworks, Inc. Sharpe manufactures 12 ounce beer bottles for smaller breweries who lack the scale or vertical integration to make their own. The bottles are sold to brewers by the case, at prices generally ranging between $25 and 528 per caso, and there are 100 bottles to a case STANDARD COSTING Direct materials Sand makes up about 85% of the weight of the finished product, and is the only material treated as a direct material. All other materials are treated as indirect, and thus part of manufacturing overhead. There are 19,700 grams of sand in one finished goods unit (case). but not all sand purchased makes it into the finished product. Waste and scrap in the normal manufacturing process results in approximately 4% of the sand purchased being lost Sand is purchased by the metric ton (one million grams) The cost from Sharpe's main supplier is $9.15 per metric ton, shipped locally at a cost to Sharpe of $18.25 per metric ton. Direct labor Each case is expected to require 0.26 direct labor hours to complete. This amount comes trom process analysis at Sharpe, and does not include setup and down time, which are expected to average 0.05 hours per case. Sharpe pays its direct laborets an hourly wage of $17.40. Payroll taxes and benefits average $3.65 per direct labor hour Manufacturing overhead Sharpe uses a traditional costing system and allocates overhead on the basis of standard direct labor hours. There are $426,000 of overhead costs expected in 2022, and 2022's unit sales are budgeted at 47.000 cases. JANUARY 2022 RESULTS In January, 2022, sales were $121.700 on 4,540 units sold. 94 metric tons of sand were purchased and used at a total cost, including shipping, of $2,600 Actual direct labor costs were $31,200, and actual direct labor hours were 1.550. Actual overhead costs were $37,500 INSTRUCTIONS 1. Complete the Standard Cost Worksheet in the "Standard Costs" tab. All items should be shown to at least two decimal places, except metric tons per case, which must be shown to at least four decimal places. 2. Complete the Standard Cost Income Statement in the "January Review" tab. 3. Prepare, in a separate Word document, a brief memo describing which variances you, in a controlling function, are most curious about, and why. Who would you contact for explanations about those variances. Your solution should use formulas and cell references instead of keyed in values where possible. For instance, each tab has an area to show your work, and you should use formulas to work out standard quantities, price variances, etc., then cell references to pull those results into the required schedule. Besides reinforcing good practices, this will make your work easier and less subject to error. Load both completed files into Canvas by 8:00 PM on April 26. - Note on decimal places (and why so many of them are needed for standard quantities). If, for instance, your standard quantity was 28,700 grams of sand per unit of finished goods, then your standard quantity would be 28,700 grams divided by one million grams per metric ton, or 0.0287 metric tons.] YOU to dit is safer to stay in Protected View Enable Edit B5 fo AD AE AI B D G H 1 ACC 3020 - Winter 2021 2 Excel Assignment - Standard Costs 3 4 Name: 5 Access ID: 6 7 You work in the corporate controller's office for Sharpe Glassworks, Inc. Sharpe manufactures 12 8 ounce beer bottles for smaller breweries who lack the scale or vertical integration to make their 9 own. The bottles are sold to brewers by the case, at prices generally ranging between $25 and $28 10 per case, and there are 100 bottles to a case. 11 12 13 14 You must enter 15 your AccessID in 16 cell BS. The facts 17 of this case will not 18 appear until you do. 19 20 21 22 24 25 26 27 28 29 30 cot safe to stay in Protected View AI B G H 1 M 1 2 E F Sharpe Glassworks, Inc. Standard Cost Worksheet Standard Quantity metric tons Standard Price Standard Cast X hours 4 Manufacturing Cost Element 5 Direct materials 6 7 Direct labor 8 9 Manufacturing overhead 10 11 Total 12 hours 14 15 Supporting calculations 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 33 Instructions Standard Costs January Review MEADY O a S Sharpe Glassworks, Inc. Income Statement *FOR INTERNAL USE ONLY 4 S Sales revenue 6 Cost of goods sold (at standard) 7 Standard gross profit 8 Variances 9 Materials price 10 Materials quantity 11 Labor price 12 Labor quantity 13 Manufacturing overhead 14 Total variance 15 Gross profit (actual) 16 17 18 19 Supporting calculations: 20 21 22 23 24 25 26 27 28 29 30 31 32 33 cructions Standard Costs January Review Select Paragraph Styles Editing This assignment needs to be done in using Excel ONLY