Create spreadsheet to calculate EBIT $, EBIT Margin %, Asset Turnover %, and Return on Assets...

  

Transcribed Image Text:

Create spreadsheet to calculate EBIT $, EBIT Margin %, Asset Turnover %, and Return on Assets %. 2. If there is a 5% increase in sales, recalculate metrics. (remember, COGS and variable overhead expense increase correspondingly, and assume that Inventory and Net A/R increase correspondingly as well). 3. If there is 5% decrease in cost (COGS + variable OH), recalculate metrics. Assume that COGS and Inventory decrease proportionally. Sales $500,343 Cost of Goods Sold = $373,396 Total Overhead Expenses = $106, 510 (40% Variable, 60% Fixed) Inventory on Hand = $43,783 Cash-on-Hand = $6,756 Fixed Assets = $144,858 Note: All dollar figures are in millions. Net Accounts Receivable = $5,614 Prepaid Expenses = $3,511 BUSINESS FINANCIAL MODEL RETURN ON ASSETS (%) EBIT MARGIN (%) ASSET TURNS (%) EBIT (S) SALES ($) GROSS PROFIT ($) TOTAL COST ($) SALES ($) CURRENT 3-g ASSETS ($) TOTAL ASSETS ($) FIXED ASSETS ($) SALES ($) COST OF GDS SOLD ($) VARIABLE O/H EXP ($) FIXED O/H EXPENSE ($) INVENTORY ($) NET ACCTS RECV ($) CASH ON HAND ($) Create spreadsheet to calculate EBIT $, EBIT Margin %, Asset Turnover %, and Return on Assets %. 2. If there is a 5% increase in sales, recalculate metrics. (remember, COGS and variable overhead expense increase correspondingly, and assume that Inventory and Net A/R increase correspondingly as well). 3. If there is 5% decrease in cost (COGS + variable OH), recalculate metrics. Assume that COGS and Inventory decrease proportionally. Sales $500,343 Cost of Goods Sold = $373,396 Total Overhead Expenses = $106, 510 (40% Variable, 60% Fixed) Inventory on Hand = $43,783 Cash-on-Hand = $6,756 Fixed Assets = $144,858 Note: All dollar figures are in millions. Net Accounts Receivable = $5,614 Prepaid Expenses = $3,511 BUSINESS FINANCIAL MODEL RETURN ON ASSETS (%) EBIT MARGIN (%) ASSET TURNS (%) EBIT (S) SALES ($) GROSS PROFIT ($) TOTAL COST ($) SALES ($) CURRENT 3-g ASSETS ($) TOTAL ASSETS ($) FIXED ASSETS ($) SALES ($) COST OF GDS SOLD ($) VARIABLE O/H EXP ($) FIXED O/H EXPENSE ($) INVENTORY ($) NET ACCTS RECV ($) CASH ON HAND ($)