1. Do all work in Excel. Do not submit Word files or *.pdf files.

Details: Complete the following problems from chapter 4 in the textbook:

Follow these instructions for completing and submitting your assignment:

1. Do all work in Excel. Do not submit Word files or *.pdf files.

2. Submit a single spreadsheet file for this assignment. Do not submit multiple files.

3. Place each problem on a separate spreadsheet tab.

4. Label all inputs and outputs and highlight your final answer.

5. Follow the directions in “Guidelines for Developing Spreadsheets.”

P4–5 Classifying inflows and outflows of cash Classify each of the following items as an

inflow (I) or an outflow (O) of cash, or as neither (N).

Item ($) Change Item ($)

Cash +100 Accounts receivable −700

Accounts payable −1,000 Net profits +600

Notes payable +500 Depreciation +100

Long-term debt −2,000 Repurchase of stock +600

Inventory +200 Cash dividends +800

Fixed assets +400 Sale of stock +1,000

P4–6 Finding operating and free cash flows Consider the following balance sheets and

selected data from the income statement of Keith Corporation.

December 31

Assets 2015 2014

Cash $ 1,500 $ 1,000

Marketable securities 1,800 1,200

Accounts receivable 2,000 1,800

Inventories 2,900 2,800

Total current assets $ 8,200 $ 6,800

Gross fixed assets $29,500 28,100

Less: Accumulated depreciation 14,700 13,100

Net fixed assets $14,800 $15,000

Total assets $23,000 $21,800

Liabilities and stockholders’ equity

Accounts payable $ 1,600 $ 1,500

Notes payable 2,800 2,200

Accruals 200 300

Total current liabilities $ 4,600 $ 4,000

Long-term debt 5,000 5,000

Total liabilities $ 9,600 $ 9,000

Common stock $10,000 $10,000

Retained earnings 3,400 2,800

Total stockholders’ equity $13,400 $12,800

Total liabilities and stockholders’ equity $23,000 $21,800

Keith Corporation Income Statement Data (2015)

Depreciation expense $1,600 Earnings before interest and taxes (EBIT) 2,700 Interest expense 367 Net profits after taxes 1,400 Tax rate 40%

a. Calculate the firm’s net operating profit after taxes (NOPAT) for the year ended

December 31, 2015, using Equation 4.1.

b. Calculate the firm’s operating cash flow (OCF) for the year ended December 31,

2015, using Equation 4.3.

c. Calculate the firm’s free cash flow (FCF) for the year ended December 31, 2015,

using Equation 4.4.

d. Interpret, compare, and contrast your cash flow estimates in parts b and c.

P4–9 Cash budget: Basic Grenoble Enterprises had sales of $50,000 in March and

$60,000 in April. Forecast sales for May, June, and July are $70,000, $80,000, and

$100,000, respectively. The firm has a cash balance of $5,000 on May 1 and wishes

to maintain a minimum cash balance of $5,000. Given the following data, prepare

and interpret a cash budget for the months of May, June, and July.

(1) The firm makes 20% of sales for cash, 60% are collected in the next month,

and the remaining 20% are collected in the second month following sale.

(2) The firm receives other income of $2,000 per month.

(3) The firm’s actual or expected purchases, all made for cash, are $50,000,

$70,000, and $80,000 for the months of May through July, respectively.

(4) Rent is $3,000 per month.

(5) Wages and salaries are 10% of the previous month’s sales.

(6) Cash dividends of $3,000 will be paid in June.

(7) Payment of principal and interest of $4,000 is due in June.

(8) A cash purchase of equipment costing $6,000 is scheduled in July.

(9) Taxes of $6,000 are due in June.

P4–15 Pro forma income statement The marketing department of Metroline Manufacturing

estimates that its sales in 2016 will be $1.5 million. Interest expense is expected

to remain unchanged at $35,000, and the firm plans to pay $70,000 in cash dividends

during 2016. Metroline Manufacturing’s income statement for the year ended

December 31, 2015, and a breakdown of the firm’s cost of goods sold and operating

expenses into their fixed and variable components are given below.

a. Use the percent-of-sales method to prepare a pro forma income statement for the

year ended December 31, 2016.

b. Use fixed and variable cost data to develop a pro forma income statement for the

year ended December 31, 2016.

c. Compare and contrast the statements developed in parts a and b. Which statement

Metroline Manufacturing Breakdown of Costs and Expenses into Fixed and Variable Components for the Year Ended December 31, 2015

Cost of goods sold Fixed cost $210,000 Variable cost 700,000 Total costs $910,000 Operating expenses Fixed expenses $ 36,000 Variable expenses 84,000 Total expenses $120,000

probably provides the better estimate of 2016 income? Explain why.

Metroline Manufacturing Income Statement for the Year Ended December 31, 2015

Sales revenue $1,400,000 Less: Cost of goods sold 910,000 Gross profits $ 490,000 Less: Operating expenses 120,000 Operating profits $ 370,000 Less: Interest expense 35,000 Net profits before taxes $ 335,000 Less: Taxes (rate = 40%) 134,000 Net profits after taxes $ 201,000 Less: Cash dividends 66,000 To retained earnings $ 135,000

P4–18 Pro forma balance sheet Peabody & Peabody has 2015 sales of $10 million. It

wishes to analyze expected performance and financing needs for 2017, which is

2 years ahead. Given the following information, respond to parts a and b.

(1) The percents of sales for items that vary directly with sales are as follows:

Accounts receivable, 12%

Inventory, 18%

Accounts payable, 14%

Net profit margin, 3%

(2) Marketable securities and other current liabilities are expected to remain

unchanged.

(3) A minimum cash balance of $480,000 is desired.

(4) A new machine costing $650,000 will be acquired in 2016, and equipment

costing $850,000 will be purchased in 2017. Total depreciation in 2016 is

forecast as $290,000, and in 2017 $390,000 of depreciation will be taken.

(5) Accruals are expected to rise to $500,000 by the end of 2017.

(6) No sale or retirement of long-term debt is expected.

(7) No sale or repurchase of common stock is expected.

(8) The dividend payout of 50% of net profits is expected to continue.

(9) Sales are expected to be $11 million in 2016 and $12 million in 2017.

(10) The December 31, 2015, balance sheet follows.

Peabody & Peabody Balance Sheet December 31, 2015 ($000)
Assets Cash $ 400 Marketable securities 200 Accounts receivable 1,200 Inventories 1,800 Total current assets $3,600 Net fixed assets 4,000 Total assets $7,600 Total liabilities and stockholders’ equity	Liabilities and stockholders’ equity Accounts payable $1,400 Accruals 400 Other current liabilities 80 Total current liabilities $1,880 Long-term debt 2,000 Total liabilities 3,880 Common equity 3,720 $7,600

a. Prepare a pro forma balance sheet dated December 31, 2017.

b. Discuss the financing changes suggested by the statement prepared in part a.