Hello,

I have attached the document with the questions, it named Quiz 5. Each tab has one question.

All questions need to be developed in excel and must contain formulas, except for theory questions.

Also, I have attached a document with chapter16 on which all the questions are based.

Lastly, I have attached the correct solutions for the problems on ch. 16 so that you can have an example of how it is supposed to be answered.

If you have any questions please let me know.

Inventory A/R A/P Sales COGS ITR ART APT Inv Period ACP AP Period OC CC $ 20,848.00 $ 66,720.00 $ 123,356.00 $ 2,215,463.00 $ 1,094,551.00 52.50 times 33.21 times 8.87 times 6.95 days 10.99 days 41.14 days 17.94 days -23.19 days Item Inventory Accounts Receivable Accounts Payable Net Sales Cost of Goods Sold ITR ART APT Inv Period ACP AP Period OC CC Beginning Ending Average $2,000,000 $3,000,000 $2,500,000 $1,600,000 $2,000,000 $1,800,000 $750,000 $1,000,000 $875,000 $11,500,000 $8,200,000 3.28 times 6.39times 9.37times 111.28 days 57.13 days 38.95 days 168.41 days 129.46days Inventory A/R A/P Sales COGS ITR ART APT Inv Period ACP AP Period OC CC $ 10,000.00 $ 9,000.00 $ 4,000.00 $ 100,000.00 $ 50,000.00 5.00times 11.11times 12.50times 73.00days 32.85days 29.20days 105.85 days 76.65days ANALYSIS OF CASH INFLOWS May Given: Sales Cash Sales (15%) Collection after 1 mth (65%) Collection after 2 mths (20%) Total Cash Receipts June July Aug Sep Oct Nov Dec Jan 60,000 100,000 130,000 120,000 100,000 80,000 60,000 40,000 30,000 9,000 15,000 39,000 19,500 65,000 12,000 96,500 18,000 84,500 20,000 122,500 15,000 78,000 26,000 119,000 12,000 65,000 24,000 101,000 9,000 52,000 20,000 81,000 6,000 39,000 16,000 61,000 4,500 26,000 12,000 Jan ANALYSIS OF CASH OUTFLOWS May Given: Labor and Raw materials June July Aug Sep Oct Nov Dec 75,000 90,000 95,000 70,000 60,000 50,000 20,000 20,000 75,000 15,000 5,000 2,000 90,000 15,000 5,000 2,000 95,000 15,000 5,000 2,000 70,000 15,000 5,000 2,000 25,000 60,000 15,000 5,000 2,000 50,000 15,000 5,000 2,000 20,000 15,000 5,000 2,000 25,000 80,000 162,000 72,000 67,000 Payment for Labor &RM Gen and Admin Salaries Lease Payments Miscellaneous Expemses Income Tax Payments Office Suite payment Total Cash Outlays 112,000 117,000 117,000 20,000 EXPECTED CHANGE IN CASH May June Aug Sep Oct Nov Dec 96,500 112,000 (15,500) Cash Receipts Cash Outlays Surplus (Shortage) July 122,500 117,000 5,500 119,000 117,000 2,000 101,000 162,000 (61,000) 81,000 72,000 9,000 61,000 67,000 (6,000) Nov Dec Jan EXPECTED FINANCING (OR EXCESS FUNDS) May Beginning Balance Surplus (Shortage) Total Target Cash Balance Surplus cash(Loan needed) June July Aug Sep 60,000 (15,500) 44,500 40,000 4,500 44,500 5,500 50,000 40,000 10,000 50,000 2,000 52,000 40,000 12,000 Oct 52,000 (9,000) 0 (61,000) 9,000 (6,000) (9,000) 0 (6,000) 40,000 40,000 40,000 (49,000) (40,000) (46,000) Jan Collection within the month of Sale Collection in 1st month after sale Collection in 2nd month after sale 15% 65% 20% 100% ABC Corporation has the following sales collection policy: 20% of the sales are on cash and the remainder on credit. 50% of the credit sales are received in the first month after the sale. 30% of the credit sales are received in the second month after the sale. 15% of the credit sales are received in the third month of after the sale. 5% of the credit sales are bad debts and not received. Suppose the company has the following expected sales: Jan $50,000, Feb $60,000, March $70,000, April $45,000, May $66,000. Determine the cash collections for May. Cash Sales Cash collection after one month Cash collection after 2 months Cash Collection after 3 months Not collected 20.00% 40.00% 24.00% 12.00% 4.00% 100.00% Cash collections for May 20% of Sales of May 40% of Sales of April 24% of Sales of March 12% of Sales of Feb Cash collections for May = $ $ $ $ $ 13,200.00 18,000.00 16,800.00 7,200.00 55,200.00 Dr. Darshana Palkar Chapter 16 Working Capital Management FIN 5130 Dr. Palkar Dr. Darshana Palkar Key Concepts and Skills Understand: o o o o o Financial Management Decisions Goal of Working Capital Management The operating and cash cycles and understand why they are important The different types of short-term financial policy The essentials of short-term financial planning 16-1 Dr. Darshana Palkar Key Concepts and Skills Understand: o o o Cash Management and Target Cash Balance Using Float Receivables Management 16-2 Dr. Darshana Palkar Financial Management Decisions Dr. Darshana Palkar Balance Sheet Model of the Firm Assets Current Assets Fixed Assets Liabilities and Equity Current Liabilities Long-Term Debt Shareholders' Equity 16-4 Dr. Darshana Palkar The Capital Budgeting Decision Current Liabilities Current Assets Long-Term Debt Fixed Assets Capital Budgeting Decision: What long-term investments should the firm choose? Shareholders' Equity 16-5 Dr. Darshana Palkar The Capital Structure Decision Current Liabilities Current Assets Fixed Assets Capital Structure Decision: How should the firm finance the assets? Long-Term Debt Shareholders' Equity 16-6 Dr. Darshana Palkar Working Capital Management Current Assets Fixed Assets 1 Tangible 2 Intangible Current Liabilities Net Working Capital Working Capital Management: How should the firm manage its day-to-day activities? Long-Term Debt Shareholders' Equity 16-7 Dr. Darshana Palkar Financial Management Decisions Management of LongTerm Assets Capital Budgeting Management of LongTerm Capital Capital Structure Management of ShortLiquidity Management/ Term Assets and Working Capital Liabilities (CA-CL=WC) Management 16-8 Dr. Darshana Palkar Goal of Working Capital Management Dr. Darshana Palkar Goal of Working Capital Management o Goal of the firmMaximize shareholder wealth. o Goal of Working Capital ManagementMaximize net operating cash flows (operating cash inflows less operating cash outflows). o Maximize operating margin! 16-10 Dr. Darshana Palkar Working Capital Terminology o Gross working capital - total current assets. o Net working capital - current assets minus current liabilities. o Working capital management - applying investment and financing decisions to current assets 16-11 Dr. Darshana Palkar Investment Decisions applied to Current Assets o o What current assets to own? What should be the level of each current asset? n n n How much cash is needed? How much inventory is needed? Should the company sell goods on credit? What are the credit terms? 16-12 Dr. Darshana Palkar Financing Decision Applied to Current Assets o o How to finance current assets? For most firms, CA exceed CL. Therefore, a part of CA is being financed by long-term sources (debt or equity) n How is financing of CA split between short-term sources (CL) and long-term sources (long-term debt and equity)? 16-13 Dr. Darshana Palkar Significance of Working Capital o o o o o o o In a typical manufacturing firm, current assets exceed onehalf of total assets. Excessive levels can result in a sub-standard return on investment (ROI). Current liabilities are the principal source of external financing for small firms. Requires continuous, day-to-day managerial supervision. Working capital management affects the company's risk, return, and share price. Crucial to achieving long-term objectives of the firm or its failure. Requires immediate action. 16-14 Dr. Darshana Palkar Tracing Cash and NWC Dr. Darshana Palkar Net Working Capital - Review o NWC + Fixed assets = LTD + Equity o NWC = (Cash + CA other than cash) - CL o Cash = LTD + Equity + CL - CA other than cash - Fixed assets 16-16 Dr. Darshana Palkar Cash Inflows and Outflows Revenue Producing Activities Asset Disposal Debt Financing Equity Financing Inflows The Firm Outflows Cost of Revenue Producing Activities Asset Acquisition Debt Retirement Equity Return/ Retirement 16-17 Dr. Darshana Palkar Sources and Uses of Cash Sources of Cash (Cash goes up) n n n n n Increase long-term debt Increase equity Increase current liabilities Decrease current assets Decrease fixed assets Uses of Cash (Cash goes down) n n n n n Decrease long-term debt Decrease equity Decrease current liabilities Increase current assets Increase fixed assets 16-18 Dr. Darshana Palkar Sources and Uses of Cash Sources of Cash (Cash goes up) Decreases in Asset Accounts Increases in Liability Accounts Increases in Equity Accounts Uses of cash (Cash goes down) Increases in Asset Accounts Decreases in Liability Accounts Decreases in Equity Accounts 16-19 Dr. Darshana Palkar Section 16.3 Operating Cycle & Cash Cycle Dr. Darshana Palkar The Operating Cycle o For a typical manufacturing company, operating activities comprise of n o Buying raw materials, paying cash, manufacturing the product, selling the product, collecting cash These activities create patterns of cash inows and cash outows which can be both unsynchronized and uncertain. n n The payment of cash for raw materials does not happen at the same time as the receipt of cash from selling the product. Future sales and costs cannot be precisely predicted. 16-21 Dr. Darshana Palkar Example Day Activity Cash Effect 0 Purchase inventory on credit None 30 Pay for inventory -$1,000 60 Sell inventory on credit None 105 Receive cash from customers +$1,400 16-22 Dr. Darshana Palkar The Operating Cycle o o o o Begins with purchase of inventory Ends with the receipt of cash for products sold We assume purchase of resources on credit We assume sales of products on credit 16-23 Dr. Darshana Palkar Diagram of the Operating Cycle Operating Cycle Average Collection Period Inventory Period Sell Product (Day 60) Purchase Inventory (Day 0) Time Receive Cash Payment 24 (Day 105) 16-24 Dr. Darshana Palkar The Operating Cycle o o Length of time it takes to acquire inventory, sell it, and collect the cash from receivables Operating cycle = Inventory Period + A/R Period n n Inventory Period = time inventory sits on the shelf (that is, time it takes to acquire inventory and sell it) Accounts Receivable Period = time it takes to collect on receivables (that is, the time between sale of inventory and collection of the receivable) 16-25 Dr. Darshana Palkar Example: Operating Cycle o o o Operating cycle = Inventory Period + A/R Period Operating cycle = 60 days + 45 days Operating cycle = 105 days 16-26 Dr. Darshana Palkar Cash Cycle The cash cycle is the time that elapses from the time of cash outlay until the time of cash receipt. Time of Time of Cash Cycle = Cash Receipt Cash Outlay 16-27 Dr. Darshana Palkar The Cash Cycle o o o The time between payment for inventory and receipt from the sale of inventory The cash cycle measures how long we need to finance inventory and receivables Cash Cycle = Operating Cycle - A/P Period n AP period = how long it takes to pay for inventory 16-28 Dr. Darshana Palkar Diagram of the Cash Conversion Cycle Operating Cycle Average Collection Period Inventory Period Sell Product (Day 60) A/P Period Cash Conversion Cycle Cash Outlay (Day 30) Purchase Inventory (Day 0) Time Receive Cash 29 Payment (Day 105) 16-29 Dr. Darshana Palkar Example Cash Cycle o o o o o Cash cycle = Operating Cycle - A/P Period Cash cycle = 105 days - 30 days = 75 days The gap between short-term cash inows and cash outows can be lled either by borrowing or by holding a liquidity reserve in the form of cash or marketable securities. Alternatively, the gap can be shortened by changing the inventory, receivable, and payable periods. These are all managerial options that we discuss in this chapter and subsequent chapters. 16-30 Dr. Darshana Palkar Operating Cycle and Cash Cycle o o o Problem: Only insiders know the average day of cash outlay and cash receipt. Outsiders must approximate this time period with publicly available information. We can approximate the operating cycle and cash cycle with information from the firm's financial statements. 16-31 Dr. Darshana Palkar Formulae for Operating Cycle OC = Inventory Period + A/R Period OC = Days Inventory Held + Average Collection Period A/R Inventory OC = + COGS/365 Sales/365 Inventory * 365 A/R * 365 OC = + COGS Sales Inventory A/R OC = 365 + Sales COGS 1 1 OC = 365 + ITR ART 16-32 Dr. Darshana Palkar Formulae for Cash Cycle CC = Inventory Period + A/R Period A/P Period CC = Days Inventory Held + ACP A/P Period A/R A/P Inventory CC = + COGS/365 Sales/365 COGS/365 Inventory * 365 A/R * 365 A/P * 365 CC = + COGS Sales COGS A/P Inventory A/R CC = 365 + Sales COGS COGS 1 1 1 CC = 365 + ITR ART APT 16-33 Dr. Darshana Palkar Problem 1 Calculate OC and CCC From a firm's financial statements: Inventory : $20,848 A/R : $66,720 A/P : $123,356 Sales : $2,215,463 COGS : $1,094,551 Assume 365 days. 16-34 Dr. Darshana Palkar Problem 2 Item Inventory Accounts Receivable Accounts Payable Net Sales Cost of Goods Sold Beginning $2,000 $1,600 $750 $11,500 $8,200 Ending $3,000 $2,000 $1,000 Average $2,500 $1,800 $875 16-35 Dr. Darshana Palkar Interpreting the cash cycle o o o o As the inventory and receivables periods get longer => the cash cycle increases If the company is able to defer payment of payables => the cash cycle decreases Most rms have a positive cash cycle, and they thus require nancing for inventories and receivables. The longer the cash cycle, the more nancing is required. 16-36 Dr. Darshana Palkar Interpreting the cash cycle o Changes in the rm's cash cycle are often monitored as an early-warning measure. n n A lengthening cycle can indicate that the rm is having trouble moving inventory or collecting on its receivables. Such problems can be masked, at least partially, by an increased payables cycle, so both should be monitored. 16-37 Dr. Darshana Palkar Problem 3 o Suppose your average inventory is $10,000, your average receivables balance is $9,000, and your average payables balance is $4,000. Net sales are $100,000 and cost of goods sold is $50,000. o What are the operating cycle and cash cycle? 16-38 Dr. Darshana Palkar Section 16.2 Using and Financing Operating Current Assets Dr. Darshana Palkar Short-Term Financial Policies o 1. The policy that the firm adopts can be reflected in two ways: Investment in CA n n 2. Flexible - high ratio of CA to Sales Restrictive - low ratio of CA to Sales Financing of CA n n Flexible - less STD and more LTD used Restrictive - more STD and less LTD used 16-40 Dr. Darshana Palkar 1. Investment in CA - Policies Flexible Policy n n n n Large amounts of cash and marketable securities Large amounts of inventory Liberal credit policies (large accounts receivable) Relatively low levels of short-term liabilities High liquidity Restrictive Policy n n n n Low cash and marketable security balances Low inventory levels Little or no credit sales (low accounts receivable) Relatively high levels of short-term liabilities Low liquidity 16-41 Dr. Darshana Palkar 1. Investment in CA - Comparison Emphasis CA vs. CL Flexible Liquidity CA are larger as compared to CL Restrictive Higher Returns CA are less as compared to CL Current Ratio High Low NWC High Low Current Asset Turnover Risk Low High Risk Averse Less Risk Averse 16-42 Dr. Darshana Palkar 1. Investment in CA -Optimal Level o Tradeoff between the costs that rise and the costs that fall with the level of investment n n Carrying Costs Shortage Costs 16-43 Dr. Darshana Palkar 1. Investment in CA-Carrying Costs o Cost of storing larger amounts of current assets o o o o o o o Storage Costs Opportunity cost of owning current assets versus longterm assets that pay higher returns Spoilage Costs Obsolescence Costs Theft Costs Insurance Costs Increase with increase in CA 16-44 Dr. Darshana Palkar 1. Investment in CA-Shortage Costs o o o o o o Trading or Ordering or Requisition costs - Costs of placing an order for more cash or more inventory Stock-out costs - The cost of lost sales, lost customers, disruption of production schedules due to lack of inventory Freight costs Receiving, inspecting, stocking costs Processing costs - salaries and expenses of processing an order Decrease with increase in CA 16-45 Dr. Darshana Palkar Optimal Investment in current assets Cost Optimal amount of CA Total Costs Carrying Costs Shortage costs Level of CA 16-46 Dr. Darshana Palkar Alternative Financing Policies for CA o In the previous section, we looked at the determinants of the level of investment in CA, and thus we focused on the asset side of the Balance Sheet. o Now, we turn to the financing side. 16-47 Dr. Darshana Palkar 2. Financing of CA o Here, we are concerned with the relative amounts of short-term financing and longterm financing. 16-48 Dr. Darshana Palkar Current Assets o Total Current Assets = Permanent Current Assets + Temporary Current Assets o Permanent Working Capital is the base level of total current assets that the management expects to hold for a period longer than one year. o Temporary Current Assets change with seasonal demand and with swings in the business cycle. 16-49 Dr. Darshana Palkar Permanent Working Capital DOLLAR AMOUNT The base level of total current assets that the management expects to hold for a period longer than one year. Permanent current assets TIME 16-50 Dr. Darshana Palkar Temporary Working Capital DOLLAR AMOUNT The amount of current assets that varies with seasonal requirements and swings in business cycle. Temporary current assets Permanent current assets TIME 16-51 Dr. Darshana Palkar Flexible Policy o o o The firm uses a pool of marketable securities as a buffer against changing current asset needs As the need for inventory and other CA begins to rise, the rm sells off marketable securities and uses the cash to purchase whatever is needed. After the demand for inventory declines, the rm reinvests in marketable securities. 16-52 Dr. Darshana Palkar Restrictive Policy o o o The rm could keep relatively little in marketable securities. As the need for inventory and other CA begins to rise, the rm simply borrows the needed cash on a short-term basis. The rm repays the loans as the need for assets cycles back down. 16-53 Dr. Darshana Palkar Choosing the Optimal Policy o The firm keeps a reserve of marketable securities to initially finance seasonal variations in CA needs. Short term borrowing is used when the reserve is exhausted. 16-54 Dr. Darshana Palkar Section 16.4 Cash Budget Dr. Darshana Palkar Cash Budget o Primary tool in short-run financial planning n n o Identify short-term needs and opportunities Identify when short-term financing may be required How it works n n n Identify sales and cash collections Identify various cash outflows Subtract outflows from inflows and determine investing and financing needs 16-56 Dr. Darshana Palkar Problem 4 (16-18) Estimates obtained from the credit and collection department are as follows: Month Sales Labor and Raw Materials May, 2014 $60,000 $75,000 June 100,000 90,000 July 130,000 95,000 August 120,000 70,000 September 100,000 60,000 October 80,000 50,000 November 60,000 20,000 December 40,000 20,000 January, 2015 30,000 NA 16-57 Dr. Darshana Palkar Problem 4 (continued) o Collections will be as follows: n n n o o o o o o o o Collections within the month of sale: 15% Collections in 2nd month following the sale: 65% Collections in 3rd month following the sale: 20% Payments for labor and raw materials are paid during the month following the one in which these costs were incurred. General and Administrative salaries amount to $15,000 a month Lease payments will be $5,000 per month Depreciation charges will be $7,500 a month Miscellaneous expenses will be $2,000 a month Income tax payments of $25,000 due in September and December Progress payments of $80,000 on a new office suite must be paid in Oct Cash on hand on July 1 will amount to $60,000 and a minimum cash balance of $40,000 each month will be maintained throughout the period.16-58 Dr. Darshana Palkar Problem 4 (continued) 1. 2. Prepare a monthly cash budget for the last six months of 2014 Prepare an estimate of the required financing (or excess funds), that is, the amount of money the company will need to borrow (or will have available to invest) - for each month during that period. 16-59 Dr. Darshana Palkar Problem 5 o ABC Corporation has the following sales collection policy: 20% of the sales are on cash and the remainder on credit. 50% of the credit sales are received in the first month after the sale. 30% of the credit sales are received in the second month after the sale. 15% of the credit sales are received in the third month of after the sale. 5% of the credit sales are bad debts and not received. Suppose the company has the following expected sales: Jan $50,000, Feb $60,000, March $70,000, April $45,000, May $66,000. Determine the cash collections for May. 16-60 Dr. Darshana Palkar Sections 16.5 and 16.6 Cash Management and Float Dr. Darshana Palkar Reasons for Holding Cash o o o o John Maynard Keynes, in his great work, \"The General Theory of Employment, Interest, and Money\ ABC Company has a cash cycle of 10.86 days, an operating cycle of 18.9 days, and an average collection period of 6 days. The company reported cost of goods sold of $231,461. What is the company's average balance in Accounts Payable? As of this morning, your firm had a ledger balance of $3,112 with no outstanding deposits or checks. Today, your firm deposited 6 checks in the amount of $119 each and wrote 9 checks in the amount of $873 each. What is the amount of the disbursement float as of the end of the day? On July 15th, you purchased $10,000 worth of goods. The terms of the sale were 1/6, net 57. What is the effective annual rate of interest for the credit period for this sale? Compute the cash cycle based on the following information: Average Collection Period = 63 Accounts Payable Period = 33 Average Age of Inventory = 45 The terms of the sale were 4/10, net 60. What is the effective annual rate of interest? ABC Corporation currently has an inventory turnover of 7.92, a payables turnover of 21.82, and a receivables turnover of 16.6. How many days are in the cash cycle? ABC Corporation currently has an inventory turnover of 19.73, a payables turnover of 8.84, and a receivables turnover of 9.12. How many days are in the operating cycle? As of this morning, your firm had a ledger balance of $4,785 with no outstanding deposits or checks. Today, your firm deposited 7 checks in the amount of $178 each and wrote a check in the amount of $976. What is the amount of the collection float as of the end of the day? ABC Company has annual sales of $400,000 and cost of goods sold of $79,161. The accounts payable period is 34.53 days. What is the average accounts payable balance? ABC Company has annual sales of $449,912 and cost of goods sold of $198,600. The average accounts receivable balance is $102,922. How many days on average does it take the firm to collect its accounts receivable? Assume 365 days. ABC Company has an average collection period of 43 days and factors all of its receivables immediately at a 2.5 percent discount. Assume all accounts are collected in full. What is the firm's effective cost of borrowing? ABC Company writes 119 checks a day for an average amount of $415 each. These checks generally clear the bank in 5 days. In addition, the firm generally receives an average of $129,653 a day in checks that are deposited immediately. Deposited funds are available in 1 days. What is the firm's net float? Compute the Accounts Payable (A/P) period based on the following information: Average A/P balance = $52,661 Annual Cost of Goods Sold = $319,666 Assume 365 days Which one of the following is most indicative of a flexible short-term financial policy? Relatively low level of liquidity Relatively low level of accounts receivable Relatively low level of inventory Relatively high ratio of short-term debt to total debt Relatively high ratio of current assets to total assets Identify which of the following will increase the operating cycle. Choose only one. Decrease in inventory turnover ratio decrease in accounts payable period decrease in average collection period decrease in days' sales in inventory decrease in accounts payable turnover ratio Indicate the effect of the following on the cash cycle: Accounts payable turnover goes up Decrease Increase No change Indicate the effect of the following on the operating cycle: Accounts payable goes up Increase Decrease No change Indicate the effect of the following on the cash cycle: Accounts payable period goes up Decrease No change Increase