Study Question 5, and 6 on page 541 - 542. Supply Management edition 10. Thank you

Using the spreadsheet template that is available just below this assignment tab. Construct a supply chain finance model and calculate the profit margin; ROA; inventory turns; and transportation, warehousing, and inventory costs as a percentage of revenue for the following:

Sales = $200,000,000

Transportation cost = $12,000,000

Warehousing cost = $3,000,000

Inventory carrying cost = 30%

Cost of goods sold = $90,000,000

Other operating costs = $50,000,000

Average inventory = $10,000,000

Accounts receivable = $30,000,000

Cash = $15,000,000

Net fixed assets = $90,000,000

Interest = $10,000,000

Taxes = 40% of (EBIT Interest)

Current liabilities = $65,000,000

Long-term liabilities = $35,000,000

Stockholders equity = $45,000,000

Question 6

Using the supply chain finance model developed for Study Question 5, calculate the impact on profit margin; ROA; inventory turns; and transportation, warehousing, and inventory costs as a percentage of revenue for the following scenarios:

Transportation costs increase = 20%

Warehousing costs decrease = 5%

Average inventory decrease = 10%

Warehousing is outsourced with:

Net fixed assets reduced = 20%

Inventory reduced = 15%

Warehousing costs = $0

Transportation costs reduced = 5%

Outsourcing provider costs = $2,500,000

E F G H I J K L A B C D 1 Excel Template to complete Q13.5 and Q13.6 2 Make Changes in the Yellow Cells 3 Input an Increase as the Percentage Value and a Decrease as a Nefative Percentage Value 4 Q6A Transportation Cost Change 5 Q6A Warehouse Cost Change 6 Q6A Average Inventory Change 7 8 Q6B Net Fixed Assets Change 9 Q6B Inventory Change 10 Q6B Warehouse Cost 11 Transportation Costs Change 12 Outsourcing Provider Costs 13 14 15 16 17 Question 5 Question 6 a and b Whour A B H I 17 Question 5 Q5 Base $ Symbol S CGS GM=S-CGS TC WC W IC=INxW OOC D E F. G Question 6 a and b Warehouse TC Increase WC Decrease IN Decrease Outsource $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0% 0% $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0% 0% 0% 0% $ $ $ $ $ $ $ $ 0% 0% $ 18 19 Sales 20 Cost of Goods Sold 21 Gross Margin 22 Transportation 23 Warehousing 24 Inventory Carrying Cost 25 Inventory Carrying 26 Other Operating Cost 27 Total operating Cost 28 Earnings before Interest and Taxes 29 Interest 30 Tax Rate 31 Taxes 32 Net Income 33 34 Asset Deplyment 35 Inventory 36 Accounts Receivable 37 Cash 38 Fixed Assets $ TOC EBIT $ INT TR TX NI $ $ IN AR CA FA $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ E F G H I J K L A B C D 1 Excel Template to complete Q13.5 and Q13.6 2 Make Changes in the Yellow Cells 3 Input an Increase as the Percentage Value and a Decrease as a Nefative Percentage Value 4 Q6A Transportation Cost Change 5 Q6A Warehouse Cost Change 6 Q6A Average Inventory Change 7 8 Q6B Net Fixed Assets Change 9 Q6B Inventory Change 10 Q6B Warehouse Cost 11 Transportation Costs Change 12 Outsourcing Provider Costs 13 14 15 16 17 Question 5 Question 6 a and b Whour A B H I 17 Question 5 Q5 Base $ Symbol S CGS GM=S-CGS TC WC W IC=INxW OOC D E F. G Question 6 a and b Warehouse TC Increase WC Decrease IN Decrease Outsource $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0% 0% $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0% 0% 0% 0% $ $ $ $ $ $ $ $ 0% 0% $ 18 19 Sales 20 Cost of Goods Sold 21 Gross Margin 22 Transportation 23 Warehousing 24 Inventory Carrying Cost 25 Inventory Carrying 26 Other Operating Cost 27 Total operating Cost 28 Earnings before Interest and Taxes 29 Interest 30 Tax Rate 31 Taxes 32 Net Income 33 34 Asset Deplyment 35 Inventory 36 Accounts Receivable 37 Cash 38 Fixed Assets $ TOC EBIT $ INT TR TX NI $ $ IN AR CA FA $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $