Trivella Sporting Enterprises had actual sales and purchases for July and August 2020 as given in the Excel spreadsheet, along with its forecast sales and purchases for the period September 2020 through February 2021.

The firm makes 30% of all sales for cash and collects 35% of its sales in each of the two months following the sale. Other cash inflows are expected to be $22,000 in September and February, $25,000 in November and January, and $37,000 in December. The firm pays cash for 20% of its purchases. It pays for 40% of its purchases in the following month and for 40% of its purchases two months later.

Wages and salaries amount to 15% of the preceding months sales. The firm must pay lease expenses of $30,000 per month. Interest payments of $20,000 are due in November and February. A principal payment of $50,000 is also due in February. The firm expects to pay a cash dividend of $30,000 in November and February. Taxes of $120,000 are due in April. The firm also intends to make a $55,000 cash purchase of fixed assets in October.

Assuming that the firm has a cash balance of $42,000 at the beginning of September and that its desired minimum cash balance is $25,000, prepare a cash budget for September through February using the Excel template.

Answer the following questions by filling in the blanks. Give your answers rounded to the nearest whole number in each case.

(a) What is the Total Cash In for the month of September? $.

(b) How much is paid in October for purchases made in August? $.

(c) What is the Total Cash Out for the month of November? $.

(d) How much are the Net Cash Flows in the month of December? $.

(e) What is the Beginning Cash balance in January? $.

(f) What is the Ending Cash balance in February? $.

(g) What is the Cash Surplus in the month of September? $.

(h) If the firm requests a line of credit from a bank to cover its largest borrowing need over the six months, how large should the line be? $.

(i) What will be the value of the firms Accounts Receivable forecast for the end of the month of February? $.

D | | | | P S 7 Month Sales Parchases September October Nuwmber Theber January February 8 July 110.000 Farind Raier 9 5290.cod 10 September 5:50.000 $270.000 10.00 $260.000 S240.000 set Ce Mexions 11 October The orth 12 November 5150.000 5240.000 5280.000 13 December $110.000 Monther (19) Two scaths alter (359) Orber cash 14 202 Jawy S200.000 15 Pierry $190.000 Total in 16 10 17 For Paren 39% 18 10 da 19 That month 2017 Member (40) 35 20 $22.000 Touthwater 140% 24 21 $25,000 22 23 Percentage of sales for cash Pantofle collected in manth Parcel of sale sullell in 2 months Other receipts (Sep Feb) Other cash receipts (Novi) Other schreeus (he) Parco de la Percentage of purchase paid in 1 month Per of plechpad in 2 months Wars and also Paymes Hugo (Moniliser 15% 24 Lease expenses 25 4UX Le pa 26 19 . Prinu 27 1.000 to Repud per month Divideda 28 29 $50,000 Acquisition of fixed assets ........ Tax pery Total shou 30 Interest payments due i Nowleb Principal payment de (F) Cash divided in lepowFchy lead Cash purchase of sixed assets in October Test Tax payment din Apc) $310.00 31 $35,00U 32 SL20.000 33 Net Cust Mors Reginning to all Cash beluce ar beginning of September $42.000 Editesh DECLUTA cash balance level $25.000 35 38 Cand Surple 37 Cash Surplus Deficit Minimum cash required Surplus/deficit Bank borrowings Cumulative borrowings Short-term investing Size of line of credit (This represents the firm's largest borrowing need over the six months.)