Question

In September 2017 you joined Adams Shoes, Inc. as the assistant to Mark Barrymore, the Budget Director of the company. Towards the end of December 2017 Mark asked you to prepare a cash budget for 2018. As he explained, he was going to use this cash budget to appraise the company’s short-term financing needs for 2018 so the company can make arrangements with its bank, First Federal Bank, to secure the necessary funds. Mark indicated to you that the firm’s Treasurer was planning to meet with First Federal Bank’s loan officer on December 30, 2017 to request a line of credit. To facilitate your task, Mark provided an abundance of valuable information for what he referred to as the “most likely scenario.” Adams Shoes sells its products (dress and casual shoes, sport shoes, work boots, and accessories) in the U.S. and abroad. Upon Mark’s request, the firm’s marketing department has supplied the following sales figures:

2017 November	$ 620,000
December	$ 670,000
2018 January	$ 750,000
February	$ 780,000
March	$ 800,000
April	$ 600,000
May	$ 550,000
June	$ 600,000
July	$ 650,000
August	$ 680,000
September	$ 700,000
October	$ 750,000
November	$ 680,000
December	$ 700,000
2019 January	$ 800,000
February	$ 820,000

The sales for the last two months of 2017 are actual sales; the sales for 2018 and 2019 are estimates. Mark also indicated to you that Adams Shoes gives a two percent discount if payment is made within the month of sale; otherwise, payment in full is due in the month following the month of the sale. For example, if a $2,000 sale is made on January 5, payment will be $1,960 for customers who pay in the month of January but customers who pay in the month of February must pay the full $2,000. Nevertheless, company records show that three percent of the sales are never collected (i.e., three percent of the customers do not pay for their purchases) while the balance is collected as follows: 25 percent of the firm’s customers take the discount, 65 percent pay within the month following the month of the sale, while the remaining 10 percent “stretch the credit” and pay in full two months after the month of the sale. Furthermore, Mark pointed out that production of goods starts two months before the anticipated date of sale. Production is based on the expected (or estimated) sales posted above, meaning that all production expenses are set by contract at the start of the one year forecast period. As a result, Adams Shoes will not be able to adjust its production costs downward during the planning period even if sales turn out to be below the forecasted levels. Variable production costs consist of labor and raw materials. Labor costs are 40 percent of expected sales; 45 percent of the labor costs are paid two months prior to the sale and 55 percent one month before the sale. Raw materials are 35 percent of forecasted sales. Adams Shoes buys the raw materials two months before the sale of the finished goods; however, it pays 60 percent of the raw materials cost one month after their purchase and the remaining 40 percent in the month following the sale of the finished goods. Regarding the firm’s other costs Mark explained that in 2018 Adams Shoes expects its fixed costs to be $25,000 a month, its selling, general and administrative expenses to be $70,000 a month, and its miscellaneous expenses to be $35,000 a month. In addition, 2018 Federal and state income tax payments of $150,000 must be made in March and September. Also, the company plans to buy a $500,000 piece of equipment in November of 2018. Depreciation of the existing fixed assets of Adams Shoes is expected to amount to $100,000 per month in 2018. On the financing side, Adams Shoes has an outstanding bank loan of $1,500,000 with an annual interest rate of five percent; interest for 2018 is scheduled to be paid semiannually in June and December. Adams Shoes also has one million shares of common stock outstanding and in 2018 it expects to pay a $.10 quarterly dividend per share in March, June, September, and December. However, Mark explained that Adams Shoes owns shares of preferred stock in some blue chip companies and expects to collect a quarterly amount of $75,000 of preferred dividends in March, June, September, and December 2018. Finally, Mark explained to you that Adams Shoes requires a minimum cash balance of $200,000 at all times; this amount will be on hand on January 1, 2018. Adams Shoes deposits any surplus funds, in a savings account at First Federal Bank which pays an annual interest rate of one percent; assume that money in this savings account at the first of a month will earn interest for the full month. Nevertheless, if the company needs to borrow funds, First Federal Bank has agreed to provide the funds at a three percent annual interest rate; assume that Adams Shoes will pay interest for the full month on any short-term debt outstanding at the beginning of a month.

Given all the above information, Mark asked you to:

For the above “most likely scenario” construct the 2018 cash budget and financial plan for Adams Shoes. (Note: Explain in detail your calculations for June 2018 for the cash budget and for February 2018 for the financial plan.)

Explain how large of a line of credit Mark would recommend the Treasurer of Adams Shoes requests from First Federal Bank.

All else the same, determine the impact a 5 percent shortfall of actual sales below expected sales will have on your “most likely scenario” findings.

Answer 1

Cash budget	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Total
Receipts from sales	660,813	723,232	754,660	722,650	586,608	547,565	586,123	629,627	658,145	685,548	702,377	667,845	7,925,191
Dividends from Preferred stock			75,000			75,000			75,000			75,000	300,000
Interest from savings					94	92	-	-	-	-	-	-	186
Add: Opening balance	200,000	207,613	203,720	202,155	200,242	200,944	200,200	200,890	200,367	200,195	200,657	200,444
Total funds available	860,813	930,845	1,033,380	924,805	786,944	823,600	786,323	830,517	933,512	885,742	903,034	943,289
Less: Payments
Labor	315,600	284,000	231,000	229,000	249,000	265,400	275,600	289,000	287,400	275,600	298,000	323,600	3,323,200
Rawmaterials	257,600	273,000	235,200	227,500	210,000	213,500	226,800	238,000	252,700	240,800	252,000	263,200	2,890,300
Fixed costs	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	300,000
Selling and admin expenses	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	840,000
Misc expenses	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	420,000
Federal and state income taxes			150,000						150,000				300,000
Equipment											500,000		500,000
Loan interest						37,500						37,500	75,000
Interest on short term borrow		125	25	63	-	-	33	150	218	685	590	1,785	3,673
Dividends paid			100,000			100,000			100,000			100,000	400,000
Total payments	703,200	687,125	846,225	586,563	589,000	746,400	632,433	657,150	920,318	647,085	1,180,590	856,085	9,052,173
Net (Surplus or deficiency)	157,613	243,720	187,155	338,242	197,944	77,200	153,890	173,367	13,195	238,657	(277,556)	87,204
Surplus over 200,000				113,000	(3,000)	(110,000)
Deficiency borrowed	50,000	(40,000)	15,000	(25,000)		13,000	47,000	27,000	187,000	(38,000)	478,000	113,000	827,000
Net cash	207,613	203,720	202,155	200,242	200,944	200,200	200,890	200,367	200,195	200,657	200,444	200,204	200,204

Notes:

In Feb 2018, we have a surplus of 243,000 after considering all payments to be made, out of this the company requires 200,000 as minimum cash balance, the remaining 40,000 is used to repay the amount borrowed in January partly. Similarly in April, we have a surplus of 338K, this can be used to pay off the borrowing in full (50,000-40,000+15,000=25,000), then after keeping 200k as minimum balance, surplus 113K is moved to savings account which will earn the interest.

In june 2018, we have a shortfall of 123K (Min balance 200K - available funds 77K), for this we can take out the amount put in savings and then borrow the shortfall

The line of credit required will be about one million dollars. The total amount to be borrowed during the year is coming to $827,000

Or, if the company goes for financing the new equipment to be purchased in November, the LOC can be around $500,000

The income statement would be as follows:

	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Total
Sales	750,000	780,000	800,000	600,000	550,000	600,000	650,000	680,000	700,000	750,000	680,000	700,000	8,240,000
Less: COGS
Labor	300,000	312,000	320,000	240,000	220,000	240,000	260,000	272,000	280,000	300,000	272,000	280,000	3,296,000
Rawmaterials	262,500	273,000	280,000	210,000	192,500	210,000	227,500	238,000	245,000	262,500	238,000	245,000	2,884,000
Gross Profit	187,500	195,000	200,000	150,000	137,500	150,000	162,500	170,000	175,000	187,500	170,000	175,000	2,060,000
													-
Fixed costs	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	300,000
Selling and admin expenses	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	70,000	840,000
Misc expenses	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	35,000	420,000
Loan interest						37,500						37,500	75,000
Interest on short term borrow		125	25	63	-	-	33	150	218	685	590	1,785	3,673
Depreciation	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	1,200,000
Total expenses	230,000	230,125	230,025	230,063	230,000	267,500	230,033	230,150	230,218	230,685	230,590	269,285	2,838,673
Net Operating income	(42,500)	(35,125)	(30,025)	(80,063)	(92,500)	(117,500)	(67,533)	(60,150)	(55,218)	(43,185)	(60,590)	(94,285)	(778,673)
Add: other income
Dividends from Preferred stock			75,000			75,000			75,000			75,000	300,000
Interest from savings					94	92	-	-	-	-	-	-	186
Net Income	(42,500)	(35,125)	44,975	(80,063)	(92,406)	(42,408)	(67,533)	(60,150)	19,783	(43,185)	(60,590)	(19,285)	(478,487)

Since the amount is a net loss, have not considered the income taxes paid as expense. they can be shown as refund receivable.

Workings:

	Estimated Sales	Collectible amount (97%) excluding the bad debt	From the current month sales (25%)less discount 2%	From the prior month sales (65%)	From the sales made 2 months ago (10%)	Total	Labor Cost (40% of sales)	First payment (45% of sales expected two months later)	First payment (55% of sales expected one month later)	Total	Cost (35% of sales)	Payment (60% one month prior sales)	Payment (40% one month after sales)	Total
2017 November	620,000	601,400					248,000				217000
December	670,000	649,900					268,000				234500	157500
2018 January	750,000	727,500	178,237.50	422,435	60,140	660,812.50	300,000	144,000	171,600	315,600	262500	163800	93800	257600
February	780,000	756,600	185,367.00	472,875	64,990	723,232.00	312,000	108,000	176,000	284,000	273000	168000	105000	273000
March	800,000	776,000	190,120.00	491,790	72,750	754,660.00	320,000	99,000	132,000	231,000	280000	126000	109200	235200
April	600,000	582,000	142,590.00	504,400	75,660	722,650.00	240,000	108,000	121,000	229,000	210000	115500	112000	227500
May	550,000	533,500	130,707.50	378,300	77,600	586,607.50	220,000	117,000	132,000	249,000	192500	126000	84000	210000
June	600,000	582,000	142,590.00	346,775	58,200	547,565.00	240,000	122,400	143,000	265,400	210000	136500	77000	213500
July	650,000	630,500	154,472.50	378,300	53,350	586,122.50	260,000	126,000	149,600	275,600	227500	142800	84000	226800
August	680,000	659,600	161,602.00	409,825	58,200	629,627.00	272,000	135,000	154,000	289,000	238000	147000	91000	238000
September	700,000	679,000	166,355.00	428,740	63,050	658,145.00	280,000	122,400	165,000	287,400	245000	157500	95200	252700
October	750,000	727,500	178,237.50	441,350	65,960	685,547.50	300,000	126,000	149,600	275,600	262500	142800	98000	240800
November	680,000	659,600	161,602.00	472,875	67,900	702,377.00	272,000	144,000	154,000	298,000	238000	147000	105000	252000
December	700,000	679,000	166,355.00	428,740	72,750	667,845.00	280,000	147,600	176,000	323,600	245000	168000	95200	263200
2019 January	800,000	776,000					320,000				280000
February	820,000	795,400					328,000				287000