Cash Flow Analysis, Rolling Sales Forecasts, and Sensitivity Analysis CompUSA Inc sells computer hardware It also markets related software and software support services The company prepares annual forecasts for sales, which are given below In a typical month, total sales are broken down as follows cash sales, 30 VISA credit card sales, 65 and 5 open account (the companys own charge accounts) For budgeting purposes, assume that cash sales plus bank credit card sales are received in the month of sale bank credit card sales are subject to a 3 processing fee, which is deducted daily at the time of deposit into CompUSAs cash account with the bank Cash receipts from collection of accounts receivable typically occur as follows 20 in the month of sale, 50 in the month following the month of sale, and 27 in the second month following the month of sale The remaining receivables generally turn out to be uncollectible CompUSAs month end inventory requirements for computer hardware units are 30 of the following months estimated sales A one month lead time is required for delivery from the hardware distributor Thus, orders for computer hardware units are generally placed by CompUSA on the 25th of each month to ensure availability in the store on the first day of the month needed These units are purchased on credit, under the following terms n 45, measured from the time the units are delivered to CompUSA Assume that CompUSA takes the maximum amount of time to pay its invoices On average, the purchase price for hardware units runs 60 of selling price CompUSA Inc Forecasted Sales (units and dollars) JanuaryDecember 2022 Number of Units Hardware Sales Software Support Sales Total Revenue January 120 $360,000 $140,000 $500,000 February 130 390,000 160,000 550,000 March 90 270,000 130,000 400,000 April 100 300,000 125,000 425,000 May 110 330,000 150,000 480,000 June 120 360,000 140,000 500,000 July August September October November December Totals 670 $2,010,000 $845,000 $2,855,000 Required Calculate estimated cash receipts for April 2022 (show details) The company is looking at the number of hardware units to order on January 25 Determine the estimated number of units to be ordered Calculate the dollar cost (per unit and total) for these units Cash planning in this line of business is critical to success Management feels that the assumption of selling price per unit ($3,000) is firmat least for the foreseeable future Also, it is comfortable with the 30 rate for end of month inventories It is not so sure, however, about (a) the Cost of Goods Sold (COGS) rate (because of the state of flux in the supplier market) and (b) the level of predicted sales in March 2022 Discussions with marketing and purchasing suggest that three outcomes are possible for each of these two variables, as follows Outcome March Sales COGS Optimistic 100 units 55 Expected 90 units 60 Pessimistic 80 units 65 The preceding outcomes are assumed to be independent, which means that there are nine possible combinations (3 3) You are asked to conduct a sensitivity analysis to determine the range of possible cash outflows for April 10, under different combinations of the above Assume, for simplicity, that sales volume for April is fixed Complete the following table Scenario March Sales (units) COGS Cash Payment April 10 1 100 55 $ 2 100 60 3 100 65 4 90 55 5 90 60 6 90 65 7 80 55 8 80 60 9 80 65 Maximum Minimum Range As part of the annual budget process, CompUSA prepares a cash budget by month for the entire year Explain why a company such as CompUSA would prepare monthly cash flow budgets for the entire year Explain the role of sensitivity analysis in the monthly planning process Management is thinking about adopting rolling forecasts that would cover five quarters (15 months) As a starting point, use the Forecast Sheet function in Excel to create the forecast for hardware sales for the first three months of 2023 Create a 95 confidence interval and comment on the results

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 25, 2024

Cash Flow Analysis, Rolling Sales Forecasts, and Sensitivity Analysis CompUSA Inc. sells computer hardware. It also markets related software and software-support services. The company prepares

Cash Flow Analysis, Rolling Sales Forecasts, and Sensitivity Analysis CompUSA Inc. sells computer hardware. It also markets related software and software-support services. The company prepares annual forecasts for sales, which are given below.

In a typical month, total sales are broken down as follows: cash sales, 30%; VISA credit card sales, 65%; and 5% open account (the companys own charge accounts). For budgeting purposes, assume that cash sales plus bank credit card sales are received in the month of sale; bank credit card sales are subject to a 3% processing fee, which is deducted daily at the time of deposit into CompUSAs cash account with the bank. Cash receipts from collection of accounts receivable typically occur as follows: 20% in the month of sale, 50% in the month following the month of sale, and 27% in the second month following the month of sale. The remaining receivables generally turn out to be uncollectible.

CompUSAs month-end inventory requirements for computer hardware units are 30% of the following months estimated sales. A one-month lead time is required for delivery from the hardware distributor. Thus, orders for computer hardware units are generally placed by CompUSA on the 25th of each month to ensure availability in the store on the first day of the month needed. These units are purchased on credit, under the following terms: n/45, measured from the time the units are delivered to CompUSA. Assume that CompUSA takes the maximum amount of time to pay its invoices. On average, the purchase price for hardware units runs 60% of selling price.

CompUSA Inc. Forecasted Sales (units and dollars) JanuaryDecember 2022
	Number of Units	Hardware Sales	Software/Support Sales	Total Revenue
January	120	$360,000	$140,000	$500,000
February	130	390,000	160,000	550,000
March	90	270,000	130,000	400,000
April	100	300,000	125,000	425,000
May	110	330,000	150,000	480,000
June	120	360,000	140,000	500,000
July
August
September
October
November
December
Totals	670	$2,010,000	$845,000	$2,855,000

Required

Calculate estimated cash receipts for April 2022 (show details).
The company is looking at the number of hardware units to order on January 25.
1. Determine the estimated number of units to be ordered.
2. Calculate the dollar cost (per unit and total) for these units.

Cash planning in this line of business is critical to success. Management feels that the assumption of selling price per unit ($3,000) is firmat least for the foreseeable future. Also, it is comfortable with the 30% rate for end-of-month inventories. It is not so sure, however, about (a) the Cost of Goods Sold (COGS) rate (because of the state of flux in the supplier market) and (b) the level of predicted sales in March 2022. Discussions with marketing and purchasing suggest that three outcomes are possible for each of these two variables, as follows:

Outcome	March Sales	COGS%
Optimistic	100 units	55%
Expected	90 units	60
Pessimistic	80 units	65

The preceding outcomes are assumed to be independent, which means that there are nine possible combinations (3 3). You are asked to conduct a sensitivity analysis to determine the range of possible cash outflows for April 10, under different combinations of the above. Assume, for simplicity, that sales volume for April is fixed. Complete the following table:

Scenario	March Sales (units)	COGS %	Cash Payment April 10
1	100	55%	$
2	100	60
3	100	65
4	90	55
5	90	60
6	90	65
7	80	55
8	80	60
9	80	65
	Maximum = ? Minimum = ? Range = ?

As part of the annual budget process, CompUSA prepares a cash budget by month for the entire year. Explain why a company such as CompUSA would prepare monthly cash flow budgets for the entire year. Explain the role of sensitivity analysis in the monthly planning process.
Management is thinking about adopting rolling forecasts that would cover five quarters (15 months). As a starting point, use the Forecast Sheet function in Excel to create the forecast for hardware sales for the first three months of 2023. Create a 95% confidence interval and comment on the results.