PROJECT OVERVIEW Harrisburg Industries produces its product in two sequential departments: Blending and Forming. The old controller, who has just retired, has been manually preparing the production cost reports 9 each month. The new controller would like you, as an intern, to develop an Excel-based production 10 cost report for the month of MAY that will automatically calculate the cost per equivalent unit, etc., 11 when the monthly information is dropped into a data area of the worksheet (the data area is shown 12 as a blue box in the following worksheets). The controller has provided you with a basic template, 13 but needs you to finish the spreadsheet by adding formulas and cell references as needed. The only 14 place she ever wants to type in numbers is in the blue box. Although there are two departments, the controller wants you to focus your attention on the Blending department until she is sure that 15 your spreadsheet will provide accurate information. 16 17 As you begin to formulate the May spreadsheet, you'll need to get production and cost data for the 18 month of May. Some of the information can be found on last month's manually-prepared production 19 cost report, while other pieces of information can be found in the May general ledger. In addition, 20 the production supervisor has already reported that 220,000 units were started during May and that 21 30,000 units were 80% of the way completed at the end of the month. During your initial plant tour, 22 you found out that all of the direct materials were added at the very beginning of the Blending 23 process. 24 25 Once you finish the spreadsheet, the controller has more questions and tasks you see the 26 Additional Analysis worksheet after you have completed the May Production Cost Report). 27 28 29 30 31 STEP-BY-STEP INSTRUCTIONS 32 33 1) Start with the May Blending Dept Cost Report. Fill in the information about units and percentages of 34 completion in the May blue box. The blue box is the only place on the worksheet where you are allowed 35 to type in numbers. FORMAT percentages as %, not as decimals. Use the % formatting icon on the 36 Excel Ribbon (top bar of the screen) 37 38 2) Fill in the cost information in the May blue box. (Hint on finding information: Gather information 39 from April's cost report and from the General Ledger). Format in DOLLARS and CENTS using the $ 40 formatting icon on the ribbon. If you get a ######### it simply means your column is not wide enough 4) (if so, drag and drop the cell line so that the cell is wider, or double click on the cell line next to the column letter to auto-size the column width.) 42 43 44 45 3) Use cell references and formulas to populate the production cost report. For example, in cell E4 you should type in the following reference: = B3. In cell E6 you should type in the following formula: E4+05. NOTE: DO NOT TYPE IN ANY NUMBERS. ALL CELLS SHOULD CONTAIN FORMULAS OR REFERENCES TO CELLS IN THE BLUE BOX. Harrisburg Industries is using the weighted average method in its process costing system. 46 47 48 49 4) Make sure all of the cost information is in dollars and cents (use the $ format icon). 50 51 5) Clean up the spreadsheet formatting as necessary (for example, if anything is bold, change it back to 52 regular font: get rid of unnecessary decimal places; align numbers properly, etc.) 53 6) Once you finish the production cost report, go to the "Additional Analysis" worksheet for additional TA B D E F G Conversion EU Department Data 2 Information about units: 3 Units in Beginning WIP 4 Started during month 5 Unitsin Ending WIP 25,000 220,000 30,000 Production Cost Report: Blending Department - MAY FLOW OF UNITS Physical Units DM EU Units to account for: Beginning work in process 25,000 Started in production 220,000 Total units to account for 245,000 Units accounted for: Completed and transferred out Ending WIP Total units accounted for $ $ $ 215,000 $ 30,000 $ 245,000 $ 215,000 $ 30,000 $ 245,000$ 215,000 24,000.00 239,000 Total DM Conversion 6 7 Percentage of completion: 8 Direct materials 100% 9 Conversion 80% 10 11 Costsin Beginning WIP 12 DM in beginning WIP $ 132,500 13 Conversion costs in beginning WIP $ 78,300 14 15 Cost Information for the current month: 16 DM traced to Dept during the month $ 1,153,750 17 DL traced to Dept during the month $ 45,000 18 MOH allocated to dept during the month $ $ 617,600 19 20 NOTE: The blue box contains the data you'll need for 21 completing the Production Cost Report (gray box). Certain figures in the blue box must be typed in. Other figures in 22 the blue box can be cell referenced from another 23 worksheet (= cell address). 24 25 55 ALL cells in the Production Cost Report (gray box) should 26 be formulas or cell-referenced to figures in the blue box. 27 DO NOT TYPE ANY FIGURES on the Production Cost Report 28 or you will lose points on the assignment. Your goal is to 29 create a Production Cost Report that can be used every 30 month by just changing the data in the blu FLOW OF COSTS Costs to account for: Beginning work in process Costs added during month Total costs to account for $ $ $ 210,800 $ 1,816,350 $ 2,027,150 $ 132,500 $ 1,153,750 $ 1,286,250 $ 78,300 662,600 740,900 Cost per equivalent unit 8.35 $ 5.25$ 3.10 Costs accounted for: Completed and transferred out Ending WIP Total costs accounted for $ $ $ 1,795,250 S 231,900 $ 2,027,150 $ 1,128,750 $ 157,500 $ 1,286,250 $ 666,500 74,400 740,900 B D E F G H 1 Conversion EU NOTE: Do NOT complete this production cost report. Select information is provided for use elsewhere in the project. Conversion 1 Production Cost Report: Blending Dept.- APRIL 2 FLOW OF UNITS Physical Units DM EU 3 Units to account for: 4 Beginning work in process 5 5 Started in production 6 Total units to account for 7 8 Units accounted for: 9 Completed and transferred out 10 Ending WIP 25,000 11 Total units accounted for 12 13 FLOW OF COSTS Total DM 14 Costs to account for: 15 Beginning work in process 16 Costs added during month 17 Total costs to account for 18 19 Cost per equivalent unit 20 21 Costs accounted for: 22 Completed and transferred out 23 Ending WIP $ 24 Total costs accounted for 210,800 $ 132,500 $ 25 26 27 28 29 30 31 78,300 Production Cost Report: Forming Dept.- MAY Physical Units DM EU Conversion EU FLOW OF UNITS Units to account for: Beginning work in process Started in production Total units to account for NOTE: Do NOT complete this production cost report. Select information is provided for use elsewhere in the project. Units accounted for: Completed and transferred out Ending WIP 1 Total units accounted for 2 3 FLOW OF COSTS 14 Costs to account for: 15 Beginning work in process 16 Costs added during month 17 Total costs to account for Total DM Conversion 18 $ 1.50$ 0.60 19 Cost per equivalent unit 20 21 Costs accounted for: 22 Completed and transferred out 23 Ending WIP 24 Total costs accounted for 25 26 27 28 29 30 31 32 33 34 35 Go Directions A1 X fx May General Journal Entries- Partial list B D E F May General Journal Entries- Partial list Use a formula to find the total operating the month $ 45,000 WIP-Blending Wages Payable (to record DL traced to the Blending Dept) $ 45,000 For Income Statement: Total Operating Expenses $ 1,153,750 WIP-Blending Raw materials inventory (to record DM traced to the Blending Dept) $ 1,153,750 REMEMBER $ 617,600 WIP-Blending MOH (to record MOH allocated to the Blending Dept) $ 617,600 Asset and Expense accounts are increased through debits (first line of a jo Liabilities and Revenue accounts are increased through credits (second lin 750,000 WIP- Forming Wages Payable (to record DL traced to the Forming Dept) (Parenthetical explanation of transaction is listed below each Journal entry $ 750,000 $ 45,000 WIP-Forming Raw materials inventory (to record DM traced to the Forming Dept) $ 45,000 $ 617,600 WIP-Forming MOH-Forming (to record MOH allocated to the Forming Dept) $ 617,600 $ 275,000 Salary Expense Wages Payable (to record salaries and wages of selling, general and admin.stoff $ 275,000 $ 25,000 Rent Expense Rent Payable (to record monthly rent) $ 25,000 $ 30,000 Insurance Expense Prepaid Insurance (to record Insurance on selling, general, and admin. Bullding) $ 30,000 $ 3,600,000 Accounts Receivable Sales Revenue (to record the sale of 180,000 units at a price of $20 per unit) $ 3,600,000 $ 10,000 Utilities Expense Utilities Payable (to record Insurance on selling, general, and admin. Building) $ 10,000 $ 120,000 Advertising expense Accounts Payable (to record advertising expenses) $ 120,000 Directions May Blending Dept Cost Report April Blending Dept Cost Report May Formi A B D E F Additional analysis G 1) Journal entry needed WIP-Forming WIP-Blending 2) Total cost per unit: 0 11 12 13 14 15 3) 16 17 18 Henkel Industries Income Statement For the Month Ending May 31 Per unit Units Sold Sales price/unit 19 Total 20 21 22 23 24 t: 25 TH 26 27 28 Ending Units 4) Inventory Analysis WIP Inventory- Blending WIP Inventory-Forming Finished Goods Inventory Total Cost b) ) d) 29 30 Reconciling UNITS in End FG Inv Beg FG + Made -Sold = End FG 31 5A) Total cost per unit 32 33 5) Alt contr were the ese Directions May Blending Dept Cost Report April Blending Dept Cost Report Ready May Forming Dept Cost Repor 5A) Total cost per unit 5B) Henkel Industries Income Statement For the Month Ending May 31 Per unit Total 5C) Lower or higher when 60% is used? Difference in operating income: 5D) Briefly explain the reason for the difference. 5E) If managers want to inflate earnings in a particular period, what can they do? Directions May Blending Dept Cost Report April Blending Dept Cost Report May Forming De Ready Merge & Center $ % 4.0 .00 .00 7.0 Delete Conditional Format Formatting as Table Cell Styles Format choose Check for Updates. Sort Filter Check H J K L M N 0 P Q R S DIRECTIONS Now that you have completed the production cost report, the controller would like you to do a few more tasks. Use the space at the left to document your responses. IMPORTANT NOTE: Every figure on this spreadsheet (dollar amounts and units) should be cell referenced from elsewhere (not typed in) EXCEPT for the blue boxes (number of units sold, sales price per unit, WIP-Forming ending units and total cost, Beg FG units). You should only type in line labels and account names. To reference a cell in another worksheet, first select the cell where you want the answer and type =. Then, select the cell you want to get from a different sheet, and hit the Enter button. 1) How much cost, in total, needs to get transferred to the Forming Department? Complete the journal entry with the appropriate amount. 2) The controller wants to know the cost of making one unit of product in May, from START TO FINISH (including both the Blending and Forming Departments). Show your calculations by listing each of the costs per EU from all of the departments that make up the total cost (e.g., Blending DM, Blending CC, etc.). FORMAT the cost per unit in dollars and cents and HIGHLIGHT the total cost per unit (use the spilling paint bucket icon to fill in the cell with neon yellow color.) unit 3) The controller would like you to prepare a draft of the company's income statement. In addition, she would like to see the sales price per unit, cost per unit, and gross profit per unit. Hint: See general ledger for the information about transactions that occurred during the month. On the general ledger worksheet, calculate the total operating expenses in cell F6 using a formula. Make sure you format everything in dollars and cents. 4) At the beginning of May, the company had no Finished Goods inventory and no WIP in the Forming Department All units transferred out of the Blending Dept. in May also made it completely through the Forming Dept. in May. The controller would like you to prepare an analysis of the company's ending WIP and FG inventory at May 31. The controller wants to know the following: a) How many units are in each Department's ending WIP Inventory? b) What is the cost balance for each Department's ending WIP Inventory? c) How many units are in ending Finished Goods Inventory? (Use side purple box to figure it out.) d) What is the total cost balance in ending Finished Goods Inventory? FG Inv 5) Although the production manager said that the units in WIP at the end of May were 80% of the way through the process, the controller knows that figure is somewhat subjective. She has talked to the assistant production manager, who believes the units were only about 60% complete. She wants you to run your spreadsheet using 60% as the percent of completion to see what impact the estimate has on A) total cost per unit, and B) the company's income statement for the month. ing Dept Cost Report May Forming Dept Cost Report May General Ledger Additional Analysis EX Delete Merge & Center $ % 4.0 .00 .00 . Conditional Format Formatting as Table 11 Cell Styles Sort Format Filte! e Check for Updates. Chech N o P H 1 K L . a) what is the total cost balance menuing Finished Goods inventory? Q R S 5) Although the production manager said that the units in WIP at the end of May were 80% of the way through the process, the controller knows that figure is somewhat subjective. She has talked to the assistant production manager, who believes the units were only about 60% complete. She wants you to run your spreadsheet using 60% as the percent of completion to see what impact the estimate has on A) total cost per unit, and B) the company's income statement for the month. IMPORTANT: BEFORE doing the analysis in Step 5, copy your May production cost report worksheet to a new tab and rename it "What-if 60%" To do this, right click on the May tab at the bottom of the screen and choose "move or copy" then check the box, "create a copy". Then right click on the new copied spreadsheet and choose "rename". 5A) Calculate the total cost per unit, using 60% as the percentage of completion. Highlight the total cost per unit. 5B) Complete the company's income statement for the month, using 60% as the percentage of completion. 5C) Calculate the difference in operating income between the two possible percentages of completion (80% vs 60%) and indicate which direction it went (higher or lower). 5D) Briefly explain WHY the difference in income occurs. 5E) In general, if managers want to inflate earnings in a particular period, what could they do? 6) Finally, put your name on the Grading Rubric worksheet and check to make sure you have completed all requirements. Then save your file and upload it to Blackboard. 1 PROJECT OVERVIEW Harrisburg Industries produces its product in two sequential departments: Blending and Forming. The old controller, who has just retired, has been manually preparing the production cost reports 9 each month. The new controller would like you, as an intern, to develop an Excel-based production 10 cost report for the month of MAY that will automatically calculate the cost per equivalent unit, etc., 11 when the monthly information is dropped into a data area of the worksheet (the data area is shown 12 as a blue box in the following worksheets). The controller has provided you with a basic template, 13 but needs you to finish the spreadsheet by adding formulas and cell references as needed. The only 14 place she ever wants to type in numbers is in the blue box. Although there are two departments, the controller wants you to focus your attention on the Blending department until she is sure that 15 your spreadsheet will provide accurate information. 16 17 As you begin to formulate the May spreadsheet, you'll need to get production and cost data for the 18 month of May. Some of the information can be found on last month's manually-prepared production 19 cost report, while other pieces of information can be found in the May general ledger. In addition, 20 the production supervisor has already reported that 220,000 units were started during May and that 21 30,000 units were 80% of the way completed at the end of the month. During your initial plant tour, 22 you found out that all of the direct materials were added at the very beginning of the Blending 23 process. 24 25 Once you finish the spreadsheet, the controller has more questions and tasks you see the 26 Additional Analysis worksheet after you have completed the May Production Cost Report). 27 28 29 30 31 STEP-BY-STEP INSTRUCTIONS 32 33 1) Start with the May Blending Dept Cost Report. Fill in the information about units and percentages of 34 completion in the May blue box. The blue box is the only place on the worksheet where you are allowed 35 to type in numbers. FORMAT percentages as %, not as decimals. Use the % formatting icon on the 36 Excel Ribbon (top bar of the screen) 37 38 2) Fill in the cost information in the May blue box. (Hint on finding information: Gather information 39 from April's cost report and from the General Ledger). Format in DOLLARS and CENTS using the $ 40 formatting icon on the ribbon. If you get a ######### it simply means your column is not wide enough 4) (if so, drag and drop the cell line so that the cell is wider, or double click on the cell line next to the column letter to auto-size the column width.) 42 43 44 45 3) Use cell references and formulas to populate the production cost report. For example, in cell E4 you should type in the following reference: = B3. In cell E6 you should type in the following formula: E4+05. NOTE: DO NOT TYPE IN ANY NUMBERS. ALL CELLS SHOULD CONTAIN FORMULAS OR REFERENCES TO CELLS IN THE BLUE BOX. Harrisburg Industries is using the weighted average method in its process costing system. 46 47 48 49 4) Make sure all of the cost information is in dollars and cents (use the $ format icon). 50 51 5) Clean up the spreadsheet formatting as necessary (for example, if anything is bold, change it back to 52 regular font: get rid of unnecessary decimal places; align numbers properly, etc.) 53 6) Once you finish the production cost report, go to the "Additional Analysis" worksheet for additional TA B D E F G Conversion EU Department Data 2 Information about units: 3 Units in Beginning WIP 4 Started during month 5 Unitsin Ending WIP 25,000 220,000 30,000 Production Cost Report: Blending Department - MAY FLOW OF UNITS Physical Units DM EU Units to account for: Beginning work in process 25,000 Started in production 220,000 Total units to account for 245,000 Units accounted for: Completed and transferred out Ending WIP Total units accounted for $ $ $ 215,000 $ 30,000 $ 245,000 $ 215,000 $ 30,000 $ 245,000$ 215,000 24,000.00 239,000 Total DM Conversion 6 7 Percentage of completion: 8 Direct materials 100% 9 Conversion 80% 10 11 Costsin Beginning WIP 12 DM in beginning WIP $ 132,500 13 Conversion costs in beginning WIP $ 78,300 14 15 Cost Information for the current month: 16 DM traced to Dept during the month $ 1,153,750 17 DL traced to Dept during the month $ 45,000 18 MOH allocated to dept during the month $ $ 617,600 19 20 NOTE: The blue box contains the data you'll need for 21 completing the Production Cost Report (gray box). Certain figures in the blue box must be typed in. Other figures in 22 the blue box can be cell referenced from another 23 worksheet (= cell address). 24 25 55 ALL cells in the Production Cost Report (gray box) should 26 be formulas or cell-referenced to figures in the blue box. 27 DO NOT TYPE ANY FIGURES on the Production Cost Report 28 or you will lose points on the assignment. Your goal is to 29 create a Production Cost Report that can be used every 30 month by just changing the data in the blu FLOW OF COSTS Costs to account for: Beginning work in process Costs added during month Total costs to account for $ $ $ 210,800 $ 1,816,350 $ 2,027,150 $ 132,500 $ 1,153,750 $ 1,286,250 $ 78,300 662,600 740,900 Cost per equivalent unit 8.35 $ 5.25$ 3.10 Costs accounted for: Completed and transferred out Ending WIP Total costs accounted for $ $ $ 1,795,250 S 231,900 $ 2,027,150 $ 1,128,750 $ 157,500 $ 1,286,250 $ 666,500 74,400 740,900 B D E F G H 1 Conversion EU NOTE: Do NOT complete this production cost report. Select information is provided for use elsewhere in the project. Conversion 1 Production Cost Report: Blending Dept.- APRIL 2 FLOW OF UNITS Physical Units DM EU 3 Units to account for: 4 Beginning work in process 5 5 Started in production 6 Total units to account for 7 8 Units accounted for: 9 Completed and transferred out 10 Ending WIP 25,000 11 Total units accounted for 12 13 FLOW OF COSTS Total DM 14 Costs to account for: 15 Beginning work in process 16 Costs added during month 17 Total costs to account for 18 19 Cost per equivalent unit 20 21 Costs accounted for: 22 Completed and transferred out 23 Ending WIP $ 24 Total costs accounted for 210,800 $ 132,500 $ 25 26 27 28 29 30 31 78,300 Production Cost Report: Forming Dept.- MAY Physical Units DM EU Conversion EU FLOW OF UNITS Units to account for: Beginning work in process Started in production Total units to account for NOTE: Do NOT complete this production cost report. Select information is provided for use elsewhere in the project. Units accounted for: Completed and transferred out Ending WIP 1 Total units accounted for 2 3 FLOW OF COSTS 14 Costs to account for: 15 Beginning work in process 16 Costs added during month 17 Total costs to account for Total DM Conversion 18 $ 1.50$ 0.60 19 Cost per equivalent unit 20 21 Costs accounted for: 22 Completed and transferred out 23 Ending WIP 24 Total costs accounted for 25 26 27 28 29 30 31 32 33 34 35 Go Directions A1 X fx May General Journal Entries- Partial list B D E F May General Journal Entries- Partial list Use a formula to find the total operating the month $ 45,000 WIP-Blending Wages Payable (to record DL traced to the Blending Dept) $ 45,000 For Income Statement: Total Operating Expenses $ 1,153,750 WIP-Blending Raw materials inventory (to record DM traced to the Blending Dept) $ 1,153,750 REMEMBER $ 617,600 WIP-Blending MOH (to record MOH allocated to the Blending Dept) $ 617,600 Asset and Expense accounts are increased through debits (first line of a jo Liabilities and Revenue accounts are increased through credits (second lin 750,000 WIP- Forming Wages Payable (to record DL traced to the Forming Dept) (Parenthetical explanation of transaction is listed below each Journal entry $ 750,000 $ 45,000 WIP-Forming Raw materials inventory (to record DM traced to the Forming Dept) $ 45,000 $ 617,600 WIP-Forming MOH-Forming (to record MOH allocated to the Forming Dept) $ 617,600 $ 275,000 Salary Expense Wages Payable (to record salaries and wages of selling, general and admin.stoff $ 275,000 $ 25,000 Rent Expense Rent Payable (to record monthly rent) $ 25,000 $ 30,000 Insurance Expense Prepaid Insurance (to record Insurance on selling, general, and admin. Bullding) $ 30,000 $ 3,600,000 Accounts Receivable Sales Revenue (to record the sale of 180,000 units at a price of $20 per unit) $ 3,600,000 $ 10,000 Utilities Expense Utilities Payable (to record Insurance on selling, general, and admin. Building) $ 10,000 $ 120,000 Advertising expense Accounts Payable (to record advertising expenses) $ 120,000 Directions May Blending Dept Cost Report April Blending Dept Cost Report May Formi A B D E F Additional analysis G 1) Journal entry needed WIP-Forming WIP-Blending 2) Total cost per unit: 0 11 12 13 14 15 3) 16 17 18 Henkel Industries Income Statement For the Month Ending May 31 Per unit Units Sold Sales price/unit 19 Total 20 21 22 23 24 t: 25 TH 26 27 28 Ending Units 4) Inventory Analysis WIP Inventory- Blending WIP Inventory-Forming Finished Goods Inventory Total Cost b) ) d) 29 30 Reconciling UNITS in End FG Inv Beg FG + Made -Sold = End FG 31 5A) Total cost per unit 32 33 5) Alt contr were the ese Directions May Blending Dept Cost Report April Blending Dept Cost Report Ready May Forming Dept Cost Repor 5A) Total cost per unit 5B) Henkel Industries Income Statement For the Month Ending May 31 Per unit Total 5C) Lower or higher when 60% is used? Difference in operating income: 5D) Briefly explain the reason for the difference. 5E) If managers want to inflate earnings in a particular period, what can they do? Directions May Blending Dept Cost Report April Blending Dept Cost Report May Forming De Ready Merge & Center $ % 4.0 .00 .00 7.0 Delete Conditional Format Formatting as Table Cell Styles Format choose Check for Updates. Sort Filter Check H J K L M N 0 P Q R S DIRECTIONS Now that you have completed the production cost report, the controller would like you to do a few more tasks. Use the space at the left to document your responses. IMPORTANT NOTE: Every figure on this spreadsheet (dollar amounts and units) should be cell referenced from elsewhere (not typed in) EXCEPT for the blue boxes (number of units sold, sales price per unit, WIP-Forming ending units and total cost, Beg FG units). You should only type in line labels and account names. To reference a cell in another worksheet, first select the cell where you want the answer and type =. Then, select the cell you want to get from a different sheet, and hit the Enter button. 1) How much cost, in total, needs to get transferred to the Forming Department? Complete the journal entry with the appropriate amount. 2) The controller wants to know the cost of making one unit of product in May, from START TO FINISH (including both the Blending and Forming Departments). Show your calculations by listing each of the costs per EU from all of the departments that make up the total cost (e.g., Blending DM, Blending CC, etc.). FORMAT the cost per unit in dollars and cents and HIGHLIGHT the total cost per unit (use the spilling paint bucket icon to fill in the cell with neon yellow color.) unit 3) The controller would like you to prepare a draft of the company's income statement. In addition, she would like to see the sales price per unit, cost per unit, and gross profit per unit. Hint: See general ledger for the information about transactions that occurred during the month. On the general ledger worksheet, calculate the total operating expenses in cell F6 using a formula. Make sure you format everything in dollars and cents. 4) At the beginning of May, the company had no Finished Goods inventory and no WIP in the Forming Department All units transferred out of the Blending Dept. in May also made it completely through the Forming Dept. in May. The controller would like you to prepare an analysis of the company's ending WIP and FG inventory at May 31. The controller wants to know the following: a) How many units are in each Department's ending WIP Inventory? b) What is the cost balance for each Department's ending WIP Inventory? c) How many units are in ending Finished Goods Inventory? (Use side purple box to figure it out.) d) What is the total cost balance in ending Finished Goods Inventory? FG Inv 5) Although the production manager said that the units in WIP at the end of May were 80% of the way through the process, the controller knows that figure is somewhat subjective. She has talked to the assistant production manager, who believes the units were only about 60% complete. She wants you to run your spreadsheet using 60% as the percent of completion to see what impact the estimate has on A) total cost per unit, and B) the company's income statement for the month. ing Dept Cost Report May Forming Dept Cost Report May General Ledger Additional Analysis EX Delete Merge & Center $ % 4.0 .00 .00 . Conditional Format Formatting as Table 11 Cell Styles Sort Format Filte! e Check for Updates. Chech N o P H 1 K L . a) what is the total cost balance menuing Finished Goods inventory? Q R S 5) Although the production manager said that the units in WIP at the end of May were 80% of the way through the process, the controller knows that figure is somewhat subjective. She has talked to the assistant production manager, who believes the units were only about 60% complete. She wants you to run your spreadsheet using 60% as the percent of completion to see what impact the estimate has on A) total cost per unit, and B) the company's income statement for the month. IMPORTANT: BEFORE doing the analysis in Step 5, copy your May production cost report worksheet to a new tab and rename it "What-if 60%" To do this, right click on the May tab at the bottom of the screen and choose "move or copy" then check the box, "create a copy". Then right click on the new copied spreadsheet and choose "rename". 5A) Calculate the total cost per unit, using 60% as the percentage of completion. Highlight the total cost per unit. 5B) Complete the company's income statement for the month, using 60% as the percentage of completion. 5C) Calculate the difference in operating income between the two possible percentages of completion (80% vs 60%) and indicate which direction it went (higher or lower). 5D) Briefly explain WHY the difference in income occurs. 5E) In general, if managers want to inflate earnings in a particular period, what could they do? 6) Finally, put your name on the Grading Rubric worksheet and check to make sure you have completed all requirements. Then save your file and upload it to Blackboard. 1