Question

1. November deficiency, $8.500 IN DE PLATE PR 21-4A Cash budget Obj5 The controller of Bridgeport Housewares Inc instructs you to prepare a monthly is baidot for the next three months. You are presented with the following budget information September October November $250.000 Sal $300.000 €315.000 ... . Manufacturing CONSE 150.000 TU UCU 185.000 Selling and administrative expenses 42.000 48,000 51000 Capital expenditures 200,000 The company experts to sell about 10% of its merchandise for cash. Or les on account 709 are expected to be collected in the month following the sale and the remainder the flowite muth sock th following sale) D iation, insurance and property tax expense CD- resent SSH of the estimated monthly manufacturing cas. The annual insurance premium as paid in January, the unnel T a xes are paid in December of the remainder of the manufacturing costs, 0% are expected to be paid in the month in which they are incurred and the balance in the following month. Current assets as September I include exsh of $40,XID, rurketable securities of $75,000 and accents receivable of $300.000 (560.000 from July sales and $2.10.000 from August Sales Sales on account for July and August were $200.000 and $240.000, respectively. Current hilities as of September 1 include 10. 00 of ac ts alle kurred in August for manufacturing costs. All selling and administrative expenses are paid in cash in the period they are incurred An estimated income tax payment of $55.000 will be made in October Bridgeport's regular quarterly dividend of $25.00 I expected to be declared in Octor and paid in November Management ceniono maintain a minimum cash balance of $50,000 Instructions 1. Prepare a monthly cash xadget and supporting schedules for September October and November On the basis of the cash budget prepared in part (1) was recommendation should he made to the extroller

I need to do a cash budget and I need help. Is it possible to have support? This is homework for my accounting class

I have to do a Cash Budget, Schedule of Cash Collections from Sales, and Schedule of Payments for Manufacturing Costs.

Also, I have to demonstrate how I did the calculations.

Please help me!

Answer 1

Answer is given below

I have also shared the Screen shot of Excel to understand the formulas so that you can demonstrate.

May be you need to do once in excel yourselves to understand it better

Cash Budget September October November $40,000 $131,000 $163,500 $253,000 $259,500 $288,000 $293,000 $390,500 $451,500 Total $40,000 $800,500 $840,500 Opening Balance Cash Collections from Customers Total Cash available Cash disbursements for Manufacturing costs Selling and administration expense Income Tax Capital Expenditure Quarterly Dividend Total Disbursements Closing balance $120,000 $42,000 $124,000 $48,000 $55,000 $134,000 $51,000 $200,000 $25,000 $410,000 $41,500 $378,000 $141,000 $55,000 $200,000 $25,000 $799,000 $41,500 $162,000 $131,000 $227,000 $163,500 For the month of November there is $ 8,500 Deficiency to maintain required cash balance of $ 50,000 Recommendation Short term borrowing can be obtained from bank to maintain the required minimum balance of $ 50,000 Working Notes Total Sales Cash Sales Credit Sales September October November $250,000 $300,000 $315,000 $25,000 $30,000 $31,500 $225,000 $270,000 $283,500 Total $865,000 $86,500 $778,500 Cash collections September October November $250,000 $300,000 $315,000 Total $865,000 Total Sales $25,000 $30,000 $31,500 $86,500 Cash Sales Cash collections July Sales August Sales September Sales October Sales Total Cash Collections $60,000 $168,000 $72,000 $157,500 $67,500 $189,000 $288,000 $60,000 $240,000 $225,000 $189,000 $800,500 $253,000 $259,500 Schedule of Payments for Manufacturing Cost Total cost Less: Deprecation, Insurance and Property tax Net Cash Manufacturing expenses September October November $150,000 $180,000 $185,000 $50,000 $50,000 $50,000 $100,000 $130,000 $135,000 Total $515,000 $150,000 $365,000 $40,000 $80,000 August Payment September payment October Payment November Payment Total Cash payments for Manufacturing cost $20,000 $104,000 $26,000 $108,000 $134,000 $40,000 $100,000 $130,000 $108,000 $378,000 $120,000 $124,000

Lla - 0 X Home Insert Cut Cash Budget-Comprehensive - Microsoft Excel Page Layout Formulas Data Review View Calibri 11 - A^ A = = = * Wrap Text General General B I U-3 - A Merge & Center Conditional Format Cell Formatting as Table - Styles Font Alignment Number Styles A B D AutoSum" 27 A Paste Insert Delete Format Ea Copy Format Painter Clipboard 2 Clear Sort & Find & Filter - Select Editing Cells Cash Budget September 40000 =F42 =SUM(F8:F9) October =F18 =G42 =SUM(G8:59) November =G18 =H42 =SUM(H8:19) Total =F8 =SUM(F9:19) =18+19 Opening Balance Cash Collections from Customers Total Cash available Cash disbursements for Manufacturing costs Selling and administration expense Income Tax Capital Expenditure Quarterly Dividend Total Disbursements Closing balance =F55 42000 =G55 48000 55000 =H55 51000 200000 25000 =SUM(H12:H16) =H10-H17 =SUM(F12:H12) =SUM(F13:H13) =SUM(F14:H14) =SUM(F15:H15) =SUM(F16:H16) =SUM(F17:H17) =110-117 =SUM(F12:F16) =F10-F17 =SUM(G12:16) =G10-G17 For the month of November there is $ 8,500 Deficiency to maintain required cash balance of $50 Recommendation Short term borrowing can be obtained from bank to maintain the required minimum balance of Working Notes Total Sales Cash Sales Home WorkCash Budget Harrison September 250000 =F28*10% and D Budget LIFO October 300000 =G28*10% November 315000 =H28*10% Total =SUM(F28:H28) =128*10% H Ruler pet food Hillyard Silk Tie Choclate1 JetS N Ready OU 100% 4 ENG ^ 0x IN 0 19:46 07-11-2019 Type here to search |

Lla Cash Budget-Comprehensive - Microsoft Excel - 0 X Home Insert View Cut Wrap Text General Page Layout Formulas Data Review Calibri 511 · A' ^ === V B I U-3 - A Font General 1 0 ZZZZZERA DAF 3 AutoSum" AT A Ea Copy Paste Format Painter Clipboard Merge & Center Insert Delete Format Conditional Format Formatting as Table Styles Cell Styles 2 Clear Sort & Find & Filter - Select Editing Alignment Number Cells EZ D F H Cash Sales Credit Sales =F28*10% =F28-F29 =G28*10% =G28-G29 =H28*10% =H28-H29 =128*10% =128-129 Cash collections September 250000 October 300000 November 315000 Total =SUM(F34:434) Total Sales =F34*10% =G34*10% =H34*10% =SUM(F36:H36) Cash Sales Cash collections July Sales August Sales September Sales October Sales Total Cash Collections 60000 =240000*70% =240000*30% =F30*70% =F30-G40 =G30*70% =SUM(H36:H41) =SUM(F38:H38) =SUM(F39:H39) =SUM(F40:H40) =SUM(F41:H41) =SUM(136:141) =SUM(F36:F41) SUM(G36:G41) Schedule of Payments for Manufacturing Cost Total cost Less: Deprecation, Insurance and Property tax Net Cash Manufacturing expenses September 150000 50000 =F47-F48 October 180000 50000 =G47-G48 November 185000 50000 =H47-H48 Total =SUM(F47:H47) =SUM(F48:H48) =SUM(F49:H49) August Payment September payment October Payment Cash Budget Harrison 40000 =F$49*80% =F$49-F52 =G$49*80% =SUM(F51:H51) =SUM(F52:H52) =SUM(F53:H53) =G$49-G53 Home Work Ruler pet foodHillyard Silk Tie Choclatel Jet S and D Budget LIFO 40 Ready BO 6 100% ENG IN - 19:46 07-11-2019 Type here to search |

- 0 X Cut Cash Budget-Comprehensive - Microsoft Excel Lla Home Insert Page Layout Formulas Data Review View Calibri 11 - A^ A = = = * Wrap Text General General Ea Copy Paste B I U-3 - A Merge & Center Conditional Format Format Painter Cell Formatting as Table - Styles Clipboard Font Alignment Number Styles E7 of A B D AutoSum" 27 A Insert Delete Format 2 Clear Sort & Find & Filter - Select Editing Cells Schedule of Payments for Manufacturing Cost Total cost Less: Deprecation, Insurance and Property tax Net Cash Manufacturing expenses September 150000 50000 =F47-F48 October 180000 50000 =G47-648 November 185000 50000 =H47-H48 Total =SUM(F47:H47) =SUM(F48:H48) =SUM(F49:H49) 40000 =F$49*80% August Payment September payment October Payment November Payment Total Cash payments for Manufacturing cost =F$49-F52 =G$49*80% =G$49-653 =H$49*80% =SUM(H51:H54) =SUM(F51:H51) =SUM(F52:H52) =SUM(F53:H53) =SUM(F54:H54) =SUM(F55:H55) =SUM(F51:F54) =SUM(G51:G54) Home Work Cash Budget Harrison Ruler pet food Hillyard Silk Tie Choclatel Jet S and D Budget LIFO 1 Ready DO 100% 4 ENG U 19:47 Type here to search IN 07-11-2019