Create an Account

Already have account?

Forgot Your Password ?

Home / Questions / Complete this spreadsheet by calculating all the gray boxes This is a giant r...

Complete this spreadsheet by calculating all the gray boxes This is a giant retailer that pays no dividends and has no preferred stock. Calculate all ratios and figures in t

Complete this spreadsheet by calculating all the gray boxes.                          
This is a giant retailer that pays no dividends and has no preferred stock.                          
Calculate all ratios and figures in the boxes provided C22:E33 and state whether the ratios are getting better or worse or about the same.                          
                           
                           
                           
INCOME STATEMENTS 2011 2012 2013   Factor CS (Over Sales)   CAGR Industry 2014 Factors 2014E Justification/2014 Factors
          2011 2012 2013 Avg Rates 2011-13 2013      
Sales $3,369.50 $3,493.25 $3,665.05           2.75% 3.50% $3,765.69 $3,848.3 Assume Growth is 5.00%
Costs except depreciation $3,002.70 $3,120.60 $3,289.40   89.11% 89.33% 89.75% 89.40%   90.00% 91.85% 93.87% Use average over three years
Depreciation $104.20 $106.55 $107.10   3.09% 3.05% 2.92% 3.02%   5.00% 3.01% 3.07% Use industry rates
Total operating costs $3,106.90 $3,227.15 $3,396.50   92.21% 92.38% 92.67% 92.42%   95.00%      
EBIT $262.60 $266.10 $268.55             5.00%      
Less interest (INT) $37.85 $43.30 $38.10             1.00%     Find using targets and information below
Earnings before taxes (EBT) $224.75 $222.80 $230.45             4.00%      
Taxes $78.75 $76.35 $80.50   2.34% 2.19% 2.20% 2.24%   35.00%     Use industry rates
Net income for common (NI) $146.00 $146.45 $149.95                    
Shares of common stock 100 100 100                 100.0  
Earnings per share (EPS) $1.46 $1.46 $1.50                    
                           
    2012 2013 2014E Comparisons            
Net operating profit after taxes         Is it getting Better/Worse/About Same            
Net operating working capital                      
Total operating capital                      
FCF = NOPAT Ac€?o A??? op. capital                      
Return on invested capital                      
EPS                      
Return on equity (ROE)                      
Return on assets (ROA)                      
Inventory turnover                      
Days sales outstanding                      
Total liabilities / TA                      
Times interest earned                      
Shares outstanding   100.0 100.0 100.0                  
AFN       -$0.03                  
                           
BALANCE SHEETS 2011 2012 2013   2011 2012 2013 Avg Rates 2011-13 Industry 2014 Factors 2014E Justification/2014 Factors
Assets                          
Cash $85.60 $39.70 $39.20             2.00%     Use 2.50%
Accounts receivable $307.65 $296.35 $292.05             10.00%     Use average over three years
Inventories $379.80 $395.90 $395.15             12.00%     Use average over three years
Other Current Assets $87.60 $90.50 $93.00             2.00%     Use average over three years
Total current assets $860.65 $822.45 $819.40                    
Net plant and equip. $1,324.60 $1,509.05 $1,588.75             42.50%     Use industry rate
Total assets $2,185.25 $2,331.50 $2,408.15                    
                           
Liabilities and equity                          
Accounts payable $497.55 $525.05 $551.85             12.50%     Use industry rate
Accruals $80.35 $81.70 $80.45             3.00%     Use average over three years
Notes payable $5.95 $189.30 $149.70                 $153.47 Find
Total current liab. $583.85 $796.05 $782.00                    
Long-term bonds $827.05 $744.40 $798.25                 $805.72 Find
Total liabilities $1,410.90 $1,540.45 $1,580.25                    
Common stock $139.45 $143.10 $170.15                    
Retained earnings $634.90 $647.95 $657.75                    
Total common equity $774.35 $791.05 $827.90                 $959.19 Find
Total liab. & equity $2,185.25 $2,331.50 $2,408.15                    
                        Inv. Cap.  
Invested Capital                     Targets    
Notes Payable % of IC                     8.00%    
Long Term Bonds% of IC                     42.00%    
Common Stock% of IC                     50.00%    
Int. Rate on Notes Payable 3.00% 3.00% 3.00%               3.00%    
Int. Rate on Long Term Bonds 4.56% 5.05% 4.21%               4.60%  

May 20 2020 View more View Less

Answer (Solved)

question Subscribe To Get Solution

Related Questions