please fill in the boxes and show formulas as well as answer bonus question

entory -20 16,899 78,531 40,368 $ $ 15 Sales = $132,110 Cost of Goods Sold = $87,257 Total Overhead Expenses = $31,987 (40% Variable, 60% Fixed) Inventory on Hand = $16,627 Net Accounts Receivable = $2,992 Cash-on-Hand = $7,895 Other Current Assets = $963 Fixed Assets = $42,104 Inventory Carrying Costs = 20%/year Note: All dollar figures are in millions 11,515 17,273 28,788 $ S 5 5 14,964 2,693 7,106 867 25,629 $ S 25,629 S 1. Populate the spreadsheet template, calculating Net Income S, Profit Margin %, Asset Turnover %, Return on Assets %, and Inventory Turns. Assume the average inventory for the year is equal to the inventory on-hand. 2. Recalculate metrics based on a 10% reduction in inventory. Assume that all inventory carrying cost reductions come out of variable OH expenses. 3. Recalculate metrics based on a 20% reduction in inventory. Assume that all inventory carrying cost reductions come out of variable OH expenses. G 1 1 K M N RETURN ON ASSETS SPREADSHEET SCMN 3710 - LOGISTICS (All dollar figures are in millions) BASE CASE 10% Inventory -20% Inventory S $ S 132,110 87,257 S 5 5 118,899 78,531 40,368 5 $ S 105,688 69.806 35,882 44 853 + $ 5 S $ 12.795 19,192 31.987 $ $ $ 11,515 17.272 28,788 10,216 15,354 25,590 $ 3 4 5 6 7 Sales ($) #COGS (5) 9 Grow Profit (5) 10 11 Variable OH Expenses 15) 12 Fred OH Expenses (5) 13 Total Overhead (5) 14 15 Inventory is) 16 Net A/RS) 17 Cash on Hand ($) 18 Other Current Assets (5) 19 Total Current Assets (5) 20 21 Total Current Asserts (5) 22 Fixed Assets (5) 23 Total Assets (5) 24 25 25 Gross Profit 15) 27 Total OH Cost (5) 28 Net Income (5) S $ $ 16,627 2.992 7,895 963 28,477 $ 5 $ $ $ 14.964 2.693 7,106 867 25,629 S $ $ $ 5 13,302 2.196 6,316 770 22.782 S 28.477 s 25,629 s 22,782 Mere e center Conditional Formatting Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates C21 x B D E G 1 1 K M 33 34 Total Sales (5) 35 Total Assets (5) 36 Asset Turnover (%) 37 38 Net Income (5) 39 Total Assets (5) 40 Return on Assets(X) + 42 Inventory Turns 43 44 BONUS 15 points) 45 Which company is represented in the base case? 46 (data from annual report year ending 1/31/2021) 47 48 49 50 51 52 53 entory -20 16,899 78,531 40,368 $ $ 15 Sales = $132,110 Cost of Goods Sold = $87,257 Total Overhead Expenses = $31,987 (40% Variable, 60% Fixed) Inventory on Hand = $16,627 Net Accounts Receivable = $2,992 Cash-on-Hand = $7,895 Other Current Assets = $963 Fixed Assets = $42,104 Inventory Carrying Costs = 20%/year Note: All dollar figures are in millions 11,515 17,273 28,788 $ S 5 5 14,964 2,693 7,106 867 25,629 $ S 25,629 S 1. Populate the spreadsheet template, calculating Net Income S, Profit Margin %, Asset Turnover %, Return on Assets %, and Inventory Turns. Assume the average inventory for the year is equal to the inventory on-hand. 2. Recalculate metrics based on a 10% reduction in inventory. Assume that all inventory carrying cost reductions come out of variable OH expenses. 3. Recalculate metrics based on a 20% reduction in inventory. Assume that all inventory carrying cost reductions come out of variable OH expenses. G 1 1 K M N RETURN ON ASSETS SPREADSHEET SCMN 3710 - LOGISTICS (All dollar figures are in millions) BASE CASE 10% Inventory -20% Inventory S $ S 132,110 87,257 S 5 5 118,899 78,531 40,368 5 $ S 105,688 69.806 35,882 44 853 + $ 5 S $ 12.795 19,192 31.987 $ $ $ 11,515 17.272 28,788 10,216 15,354 25,590 $ 3 4 5 6 7 Sales ($) #COGS (5) 9 Grow Profit (5) 10 11 Variable OH Expenses 15) 12 Fred OH Expenses (5) 13 Total Overhead (5) 14 15 Inventory is) 16 Net A/RS) 17 Cash on Hand ($) 18 Other Current Assets (5) 19 Total Current Assets (5) 20 21 Total Current Asserts (5) 22 Fixed Assets (5) 23 Total Assets (5) 24 25 25 Gross Profit 15) 27 Total OH Cost (5) 28 Net Income (5) S $ $ 16,627 2.992 7,895 963 28,477 $ 5 $ $ $ 14.964 2.693 7,106 867 25,629 S $ $ $ 5 13,302 2.196 6,316 770 22.782 S 28.477 s 25,629 s 22,782 Mere e center Conditional Formatting Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates C21 x B D E G 1 1 K M 33 34 Total Sales (5) 35 Total Assets (5) 36 Asset Turnover (%) 37 38 Net Income (5) 39 Total Assets (5) 40 Return on Assets(X) + 42 Inventory Turns 43 44 BONUS 15 points) 45 Which company is represented in the base case? 46 (data from annual report year ending 1/31/2021) 47 48 49 50 51 52 53