“Financial Analysis and Cash Flow Projection for XYZ Company: A Case Study”

QUESTION

XYZ Company Case: (Please Provide screenshots for Spreadsheet, Spreadsheet Formulas, and screenshots for influence chart.)

The XYZ Company makes widgets and sells to a market that is just about to expand after a period of stability. As the year starts, the widgets are manufactured at a cost of $0.75 and sold at a market price of $1.00. In addition, the firm has 1,000 widgets in finished goods inventory and a cash account of $875 at the beginning of January. During January, sales amounted to 1,000 units, which is where they have been in the recent past. Profitability looks good in January. The 1,000 units of sales provide profits for the month of $250. This amount goes right into the cash account, increasing it to $1,125. In February, the sales level rises to 1,500 units. For the next several months, it looks like demand will rise by 500 each month, providing a very promising profit outlook. The XYZ Company keeps an inventory of finished goods on hand. This practice allows it to meet customer demand promptly, without having to worry about delays in the factory. The specific policy is always to hold inventory equal to the previous month’s sales level. Thus, the 1,000 units on hand at the start of January are just the right amount to support January demand. When demand rises in February, there is a need to produce for stock as well as for meeting demand, because the policy requires that inventory must rise to 1,500 by March. February production is therefore 2,000 units, providing enough widgets to both meet demand in February and raise inventory to 1,500 by the end of the month. Your first task is to trace the performance of the XYZ Company on a monthly basis, as demand continues to increase at the rate of 500 units per month. Assume that all revenues are collected in the same month when sales are made, all costs are paid in the same month when production occurs, and profit is equal to the difference between revenues and costs. The cost of producing items for inventory is included in the calculation of monthly profit. Trace profits, inventory, and cash position on a monthly basis, through the month of June. This analysis will give us an initial perspective on the financial health of the XYZ Company. Does the company seem to be successful? In reality, the XYZ Company behaves like many other firms: it pays its bills promptly, but it collects cash from its customers a little less promptly. In fact, it takes a full month to collect the revenues generated by sales. This means that the firm has receivables every month, which are collected during the following month. XYZ Company starts the year with receivables of $1,000, in addition to inventory worth $750 and a cash account worth $875. (Therefore, its total assets come to $2,625 at the start of the year.) A month later, receivables remain at $1,000, inventory value remains at $750, and cash increases to $1,125 (reflecting receivables of $1,000 collected, less production expenses of $750). When February sales climb to 1,500 units, XYZ Company produces 2,000 widgets. Of this amount, 1,500 units are produced to meet demand and 500 units are produced to augment inventory. This means that a production bill of $1,500 is paid in February. During February, the January receivables of $1,000 are collected, and at the end of February, there are receivables of $1,500, reflecting sales made on account during the month. For accounting purposes, XYZ Company calculates its net income by recognizing sales (even though it has not yet collected the corresponding revenues) and by recognizing the cost of producing the items sold. The cost of producing items for inventory does not enter its calculation of net income. In January, net income is therefore calculated as $250, representing the difference between the revenue from January sales of $1,000 and the cost of producing those 1,000 units, or $750. Refine your initial analysis to trace the performance of the XYZ Company again with demand increasing at the rate of 500 units per month. Assume that all revenues are collected in the month following the month when sales occur, but that all costs are paid in the same month when they occur. Trace net income, receivables, inventory, and cash on a monthly basis, through the month of June. This will give us another perspective on the financial health of the XYZ Company. What financial difficulty does the model portray?

1. Read the XYZ Cash Flow case provided above (Please Provide screenshots for Spreadsheet, Spreadsheet Formulas, and Screenshots for influence chart.).

(a) Focus on the realistic case where XYZ pays its bills promptly, but it collects cash from its customers in the following month after sales. Help to Draw an influence diagram to help analyze the case.

(b) Design a spreadsheet (please provide spreadsheet screenshots and formulas) that will allow an analyst to predict the month in which the cash balance falls below zero, signaling a need to borrow money.

Please provide screenshots of Spreadsheet, Spreadsheet Formulas, and screenshots for the Influence Chart. Please provide explanations for (a) and (b).

ANSWER