Question

Assignment:

The Scent Trail Inc., a manufacturer of high-performance motorcycle parts, has estimated the following sales for September 2020 to March 2021:

Actual Sales		Estimated Sales Sept 2020 to March 2021
July 2020	August.	Sept	Oct	Nov	Dec	Jan 2021	Feb	March
$375,000	$266,667	$200,000	$350,000	$450,000	$350,000	$250,000	$200,000	$300,000

15% of sales are for cash, 85% of sales are on credit. The firm collects 55% of the credit sales during the first month after the sale and the remainder during the following second month. Inventory purchases constitute 72% of the next month’s sales. All inventory purchases are made on credit. The company pays for 35% of inventory purchases during the first month after the purchase was made and for the remainder in the next month. Monthly wages, taxes and other expenses are expected to be 32% of the current month’s sales. A major capital expenditure of $60,000 is expected in October. Installments of amortized long-term debt are expected to be $92,000 every quarter due in January, April, July, and October (write a formula for these payments using dates as inputs as we did in class). The company needs a minimum cash balance of $25,000. The cash balance at the end of January 2020 was $30,000.

1. Using the simple cash budget example from Chapter #4 in the textbook or from the class, help the financial staff of the Scent Trail Inc. to prepare their cash budget for September 2020 through February 2021. Assume that the firm uses any cash surplus above the required minimum cash balance to pay off its short-term borrowing monthly and that the annual interest on its short-term borrowing is 12%. Construct a budget title that indicates the time period for the budget. The title should be linked to the dates in the budget and should change whenever the dates change.
2. Assume that the Scent Trail Inc. has some flexibility in scheduling the major capital expenditure of $65,000 expected in October. Specifically, it can postpone the expenditure until November or December, but no later than that. Use the Scenario Manager to see which of the three months Scent Trail Inc. should make this expenditure in order to minimize the maximum borrowing and short-term interest. Create a scenario summary using the example we did in class (or in Chapter 4 of your text). Make sure to name the cells that are inputs to the scenario manager. Please answer the question: What month would be the best for scheduling the capital expenditure?

Answer 1

		sept	oct	nov	dec	jan	feb
opening cash		775166.6	756999.9	483000	274000	152000	30000
cash sales		200000*15%	350000*15%	450000*15%	350000*15%	250000*15%	200000*15%
	=	30000	52500	67500	52500	37500	30000
credit sales
55% in next month of sale		266667*55%	200000*55%	350000*55%	450000*55%	350000*55%	250000*55%
	=	146666.85	110000	192500	247500	192500	137500
credit sales
30% in second month of sale		375000*30%	266667*30%	200000*30%	350000*30%	450000*30%	350000*55%
		112500	80000.1	60000	105000	135000	105000
less;
inventory purchased(wn-1)		243333.55	252500	385000	415000	315000	232500
other expenses		200000*35%	350000*35%	450000*35%	350000*35%	250000*35%	200000*35%
(32% of current sales)		64000	112000	144000	112000	80000	64000
capital expenditure		0	60000	0	0	0	0
installment		0	92000	0	0	92000	0
closing cash		756999.9	483000	274000	152000	30000	6000
workings
1		july	aug	sept	oct	nov	dec	jan	feb
purchase of inventory		266667*72%	200000*72%	350000*72%	450000*72%	350000*72%	250000*72%	200000*72%	300000*72%
(72% of next month sales)
payment of inventory
35% in next month				200000*35%	350000*35%	450000*35%	350000*35%	250000*35%	200000*35%
				70000	122500	157500	122500	87500	70000
65& in 2nd month				266667*65%	200000*65%	350000*65%	450000*65%	350000*65%	250000*65%
				173333.55	130000	227500	292500	227500	162500
total payment				243333.55	252500	385000	415000	315000	232500