Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Need help with E, seems no matter what I do I cannot get it correct. General and administrative salaries are approximately $.36,000 a month. Lease
Need help with E, seems no matter what I do I cannot get it correct.
General and administrative salaries are approximately $.36,000 a month. Lease payments under long-term leases are $12,000 a month. Depreciation charges are $48,000 a month Miscellaneous expenses are $5,400 a month. Income tax payments of $70,000 are due in September and December. A progress payment of $185,000 on a new design studio must October. Cash on band on July 1 will be $132,000, and a minimum cash balance of 395,000 should be maintained throughout the cash budget period The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round-intermediate calculations, Round your answers to the nearest dollar. If your answer is zero, enter 0% X Download secondsheet.Cash. Budaetingfee.xlsx 2. Prepare a monthly cash budget for the last 6 months of 2021. All payments and expenses should be entered as positive numbers. Net cash losses, negative cash balance, nego cumulative cash, and cumulative loans outstanding any should be indicated by a minus sign July August September October November December Collections and purchases worksheet Sales (grass) 300000 450000 $ 600000 300000 300000 75000 Collections Dunno month of sale 30000 45000 60000 30000 30000 15 7500 Dung ist month after sale 112500 225000 337500 g 450000 225000 225000 Ouring and months after sale 22500 22500 45000 67500 90000 45000 Total collections 165000 292500 W 442500 547500 345000 277500 Purchases Labor and raw materials 105000 6900000 325000 180000 120000 75000 Payments for labor and raw materiais 1 75000 105000 690000 225000 $ 130000 120000 SOO CO $ $ 547500 S 277500 $ Cash gain or loss for month Collections Payments for labor and raw materials General and administrative salaries Lease payments Miscellaneous expenses Income tax payments Design studio payment Total payments Net cash gain (s) during month 165000 75000 $ 36000 $ 12000 5400 O og 128400 $ 36600 5 292500 $ 105000 $ 36000 12000 5400 0 442500 690000 36000 12000 5400 70000 225000 5 36000 12000 GGGGGGGGG 5400 SS & SS S SS 345000 150000 36000 12000 5400 og og 233400 111600 120000 36000 12000 5400 70000 OM O O $ 158400 134100 5 813400 $ -3709005 185000 463400 84100 $ 243400 34100 $ 3 5 C $ 132000 160600 95000 Loan requirement or cash surplus Cosh at start of month Cumulative cash Target cash balance Cumulative surplus cash or loans outstanding to maintain $95,000 target cash balance 163600 302700 s 95000 $ 302700 -68200 95000 68200 15900 95000 15900 127500 95000 127500 161600 S GIG 95000 73600 207700 16200 -79100 32500 66600 b. Prepare monthly estimates of the required financing or conde-that is the amount of money Bowers will need to borrow or will have avalable to invest. Required financing, if any should be indicated by a minus sign Required hinancing or excess funds e. Il Bowers customers began to pay late, collections would slow down, thus increasing the required loan amount of sales declined, this also would have an effect on the required loan. Doa Sensitivity analysis that shows the effects of these two factors on the maximum loan requirement. Enter your answers as positive numbers. To complete the sensitivity analysis, follow these steps in excel Ensure that cell A60 is a reference to cell 056 -856") Select/highlight cells A60 through H69 (A60:169) From the top nbbon, select Data Forecast > What if Analysis > Data Table Forrow input cell dick on cell 85 or enter $65. For column input cell dick on cell 34 or manually enter 0614 ook "OK" Collections in 2nd month 0% Change in sales -100% 30% 45 10 75% 00% . 1 X 3 3 $ . 1 5 1 1 1 50" 54 04 254 1 1 5 1 163200 5 1 1 1 $ $ 5 5 $ + 5 $ 759 100% 3 5 1 1 General and administrative salaries are approximately $.36,000 a month. Lease payments under long-term leases are $12,000 a month. Depreciation charges are $48,000 a month Miscellaneous expenses are $5,400 a month. Income tax payments of $70,000 are due in September and December. A progress payment of $185,000 on a new design studio must October. Cash on band on July 1 will be $132,000, and a minimum cash balance of 395,000 should be maintained throughout the cash budget period The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round-intermediate calculations, Round your answers to the nearest dollar. If your answer is zero, enter 0% X Download secondsheet.Cash. Budaetingfee.xlsx 2. Prepare a monthly cash budget for the last 6 months of 2021. All payments and expenses should be entered as positive numbers. Net cash losses, negative cash balance, nego cumulative cash, and cumulative loans outstanding any should be indicated by a minus sign July August September October November December Collections and purchases worksheet Sales (grass) 300000 450000 $ 600000 300000 300000 75000 Collections Dunno month of sale 30000 45000 60000 30000 30000 15 7500 Dung ist month after sale 112500 225000 337500 g 450000 225000 225000 Ouring and months after sale 22500 22500 45000 67500 90000 45000 Total collections 165000 292500 W 442500 547500 345000 277500 Purchases Labor and raw materials 105000 6900000 325000 180000 120000 75000 Payments for labor and raw materiais 1 75000 105000 690000 225000 $ 130000 120000 SOO CO $ $ 547500 S 277500 $ Cash gain or loss for month Collections Payments for labor and raw materials General and administrative salaries Lease payments Miscellaneous expenses Income tax payments Design studio payment Total payments Net cash gain (s) during month 165000 75000 $ 36000 $ 12000 5400 O og 128400 $ 36600 5 292500 $ 105000 $ 36000 12000 5400 0 442500 690000 36000 12000 5400 70000 225000 5 36000 12000 GGGGGGGGG 5400 SS & SS S SS 345000 150000 36000 12000 5400 og og 233400 111600 120000 36000 12000 5400 70000 OM O O $ 158400 134100 5 813400 $ -3709005 185000 463400 84100 $ 243400 34100 $ 3 5 C $ 132000 160600 95000 Loan requirement or cash surplus Cosh at start of month Cumulative cash Target cash balance Cumulative surplus cash or loans outstanding to maintain $95,000 target cash balance 163600 302700 s 95000 $ 302700 -68200 95000 68200 15900 95000 15900 127500 95000 127500 161600 S GIG 95000 73600 207700 16200 -79100 32500 66600 b. Prepare monthly estimates of the required financing or conde-that is the amount of money Bowers will need to borrow or will have avalable to invest. Required financing, if any should be indicated by a minus sign Required hinancing or excess funds e. Il Bowers customers began to pay late, collections would slow down, thus increasing the required loan amount of sales declined, this also would have an effect on the required loan. Doa Sensitivity analysis that shows the effects of these two factors on the maximum loan requirement. Enter your answers as positive numbers. To complete the sensitivity analysis, follow these steps in excel Ensure that cell A60 is a reference to cell 056 -856") Select/highlight cells A60 through H69 (A60:169) From the top nbbon, select Data Forecast > What if Analysis > Data Table Forrow input cell dick on cell 85 or enter $65. For column input cell dick on cell 34 or manually enter 0614 ook "OK" Collections in 2nd month 0% Change in sales -100% 30% 45 10 75% 00% . 1 X 3 3 $ . 1 5 1 1 1 50" 54 04 254 1 1 5 1 163200 5 1 1 1 $ $ 5 5 $ + 5 $ 759 100% 3 5 1 1 Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started