1. Refer to the xyz company case. From thee corresponding exercise in chapter 3

EXERCISES 1. Refer to the XYZ Company case. From the correspond- ing exercise in Chapter 3, review the design of a spreadsheet that will allow an analyst to predict monthly cash needs and profitability for the first five months of the year. a. In what month does the cash balance fall below zero, signaling a need to borrow money? b. What is the profit, as a percentage of sales, in April? c. Suppose the monthly increment in sales is 600- instead of 500, as in the base case. How does this change affect the answers in (a) and (b)? Construct a table to examine the month in which the cash balance disappears as a function of the monthly increment in sales. d. Suppose the monthly increment in sales is 300-instead of 500, as in the base case. How does this change affect the answers in (a) and (b)? Construct a graph showing the profit- ability percentage in (b) as a function of the monthly increment in sales. e. Starting with the base case, suppose that unit cost runs higher than originally thought. What level of unit cost will lead the firm to run out of cash by the end of March? THE XYZ COMPANY* The XYZ Company makes widgets and sells to a market customers a little less promptly. In fact, it takes a full month that is just about to expand after a period of stability. As the to collect the revenues generated by sales. This means that year starts, the widgets are manufactured at a cost of $0.75 the firm has receivables every month, which are collected and sold at a market price of $1.00. In addition, the firm has during the following month. 1,000 widgets in finished goods inventory and a cash account XYZ Company actually starts the year with receivables of $875 at the beginning of January. During January, sales of $1,000, in addition to inventory worth $750 and a cash amount to 1,000 units, which is where they have been in the account worth $875. (Therefore, its total assets come to recent past. $2,625 at the start of the year.) A month later, receivables Profitability looks good in January. The 1,000 units of remain at $1,000, inventory value remains at $750, and cash sales provide profits for the month of $250. This amount increases to $1,125 (reflecting receivables of $1,000 col- goes right into the cash account, increasing it to $1,125. lected, less production expenses of $750). In February, the sales level rises to 1,500 units. For the When February sales climb to 1,500 units, XYZ Com- next several months, it looks like demand will rise by 500 pany produces 2,000 widgets. Of this amount, 1,500 units are each month, providing a very promising profit outlook. produced to meet demand and 500 units are produced to The XYZ Company keeps an inventory of finished goods augment inventory. This means that a production bill of on hand. This practice allows it to meet customer demand $1,500 is paid in February. During February, the January promptly, without having to worry about delays in the fac- receivables of $1,000 are collected, and at the end of Feb- tory. The specific policy is always to hold inventory equal to ruary, there are receivables of $1,500, reflecting sales made the previous month's sales level. Thus, the 1,000 units on hand on account during the month. at the start of January are just the right amount to support For accounting purposes, XYZ Company calculates its January demand. When demand rises in February, there is a net income by recognizing sales (even though it has not yet need to produce for stock as well as for meeting demand, collected the corresponding revenues) and by recognizing because the policy requires that inventory must rise to 1,500 the cost of producing the items sold. The cost of producing by March. February production is therefore 2,000 units, items for inventory does not enter into its calculation of net providing enough widgets to both meet demand in February income. In January, net income is therefore calculated as and raise inventory to 1.500 by the end of the month. $250, representing the difference between the revenue from Your first task is to trace the performance of the XYZ January sales of $1,000 and the cost of producing those 1,000 Company on a monthly basis, as demand continues to units, or $750. increase at the rate of 500 units per month. Assume that Refine your initial analysis to trace the performance of all revenues are collected in the same month when sales are the XYZ Company, again with demand increasing at the made, all costs are paid in the same month when production rate of 500 units per month. Assume that all revenues are occurs, and profit is equal to the difference between reven- collected in the month following the month when sales ues and costs. The cost of producing items for inventory is occur, but that all costs are paid in the same month when included in the calculation of monthly profit. Trace profits, they occur. Trace net income, receivables, inventory, and inventory, and cash position on a monthly basis, through the cash on a monthly basis, through the month of June. This will month of June. This analysis will give us an initial perspec- give us another perspective on the financial health of the tive on the financial health of the XYZ Company. Does the XYZ Company. What financial difficulty does the model company seem to be successful? portray? In reality, the XYZ Company behaves like many other firms: it pays its bills promptly, but it collects cash from its "Adapted from a homework exercise developed by Clyde Stickney