How do I solve this cash budget with the formulas I have to use. This is for a cash flow budget project for managerial accounting. Some of the numbers are from the other budgets.

I need major help with the Supporting Schedules. Formulas are needed. This is all the information I have. There is no more to clarify.

Resources Presenter Info Step 17 of 40: Beginning Inventory Slide ID: 4512 You are the ter m ini Step 17 of 40: Beginning Inventory January February D March E April F October November December May OUTLINE 1 Wally's Widgets 5 Less Beginning Inventory 6 Required Purchases in Units 7 Required Purchases in Dollars 8 Budgeted Sales in Dollars 9 Sales Commissions in Dollars 1 Search Your turn: The Beginning Inventory formula is similar in nature to the Ending Inventory formula, but is a percentage of current month sales (as opposed to the following month's sales as you used earlier). 14. Step 11 of 40: Table Feature 15. Step 12 of 40: Create Series of months 11 Collection Ratio January February March April May J2 October November December 12 January 13 February Inputs Supporting Schedules Operating Budget Cash Budget E F G H I 1 Wally's Widgets January February March April May June July August 5 Less Beginning Inventory 60001 6 Required Purchases in Units 7 Required Purchases in Dollars 2 If you calculate a number outside of Excel, such as with your calculator and 8 Budgeted Sales in Dollars then type it into the spreadsheet for this exercise, the cell turn red. You will not 9 Sales Commissions in Dollars get full credit for any such cells that contain static values instead of formulas. 16. Step 13 of 40: First Month's Sales in Units 17. Step 14 of 40: Projected Increase in Sales 10 January February March April May June July August Se 18. Step 15 of 40: Ending Inventory 11 Collection Ratio 12 January 13 February Inputs Supporting Schedules Operating Budget Cash Budget 19. Step 16 of 40: Excel's AutoSum Command Students Excel www.studentsexcel.com Need help? Email: support@studentsexcel.com 20. Step 17 of 40: Beginning Inventory 20/43 00:11 / 00:45 10,000 12.00 50% Budget Assumptions First Month's Sales Sales Price Gross Margin 1 Commission 3 Sales Growth Ending Inventory 10 Minimum Cash Balance Interest Rate 10% 250 60% 20,000 12% 2. 3 Collections Payments 25% 60% 14 Month 1 15 Month 2 16 Month 3 50% 40% 25% 18 Operating Expenses 19 Payroll 20 Utilities 21 Other Per Month $ 192,000! $84,000 $234,000 Resources Presenter Info Step 18 of 40: Required Purchases in Units Slide ID: 4513 Step 18 of 40: Required Purchases in Units and help? Cruil: suppend Cl.com OUTLINE Search 18. Step 15 of 40: Ending Inventory C D E K L M 1 Wally's Widgets January February March April May October November December 6 Required Purchases in Units 7 Required Purchases in Dollar 1 8 Budgeted Sales in Dollars Your turn:Use an Excel formula to compute the number of inventory units 9 Sales Commissions in Dollars Wally's Widgets will need to purchase each month to maintain the desired ending inventory balance 11 Collection Ratio 12 January 13 February 14 March Inputs Supporting Schedules Operating Budget Cash Budget DE F March April May June Order of Operations: = 100*0.03 100.21 Excel calculates formulas from left to 100+7*0.03 right, and then in this order: =(100+7)*0.03 Parenthesis Exponents Click this button or press Shift-F11 Enter the formulas shown to Division to insert a new worksheet. Assign get a sense of Excel's order Multiplication the name Order of Operations to this worksheet. of operations. As you can Addition see, parentheses alter the Subtraction order of operations. 19. Step 16 of 40: Excel's AutoSum Command 20. Step 17 of 40: Beginning Inventory 3.211 21. Step 18 of 40: Required Purchases in Units 22. Step 19 of 40: Required Purchases in Dollars Budget Cash Budget (O Inputs Supporting Schedules Order of Operations Operatin ... + 23. Step 20 of 40: Budgeted Sales and Commissions Students Excel www.studentsexcel.com Need help? Email: support@studentsexcel.com 21/43 00:03/04:21 c 24. Step 21 of 40: Collections Percentages m Resources Presenter Info Step 20 of 40: Budgeted Sales and Commissions Slide ID: 4515 Step 20 of 40: Budgeted Sales and Commissions www. A.com andhap Cruail: suppor t .com OUTLINE Search 10 18. Step 15 of 40: Ending Inventory 19. Step 16 of 40: Excel's AutoSum Command C D E F K L M 1 Wally's Widgets January ruary March April May October November December 8 Budgeted Sales in Dollars 9 Sales Commissions in Dollars ? Your turn: Compute the budgeted sales in dollars for 11 Collection Ratio January each month and set the number format to General. 12 January 13 February 14 March 15 April 16 May Inputs Supporting Schedules Order of Operations Operating Budget LM 1 Wally's Widgets January February March April May October November December 9 Sales Commissions in Dollars 10 11 Collection Ratio January F2 December 12 January Your turn: Compute the sales commission for 13 February each month and set the number format to General. 14 March 15 April 16 May 17 June Inputs Supporting Schedules Order of Operations Operating Budget 20. Step 17 of 40: Beginning Inventory 21. Step 18 of 40: Required Purchases in Units 22. Step 19 of 40: Required Purchases in Dollars 23. Step 20 of 40: Budgeted Sales and Commissions Students Excel www.studentsexcel.com Need help? Email: support@studentsexcel.com . 23/ 43 23143 00:10 / 00:21 00:10 /00:21 G O 24. Step 21 of 40: Collections Percentages X 1-Gross_Margin)"sales_Price*B6 July 11.500 7,050 18,550 6,900 11,650 69,900 138,000 13,800 August September October November December 11.750 12.000 12,250 12.500 12.750 7,200 7,350 7 ,500 7,650 7,650 18,950 19,350 19,750 20,150 20,400 7,050 7,200 7,350 7,500 7,650 11,900 12,150 12,400 12,650 12,750 71,400 72,900 74,400 75,900 76,500 141,000 144,000 147,000 150,000 153,000 14,100 14,400 14,700 15,000 15,300 DE 1 Wally's Widgets January February March April May June 2 Budgeted Sales in Units 10,000 10,250 10,500 10,750 11.00 11,250 3 Add: Ending Inventory 6,150 6,300 6,450 6,600 6,750 6,900 4 Total Needs 16,150 16,550 16,950 17,350 17,750 18,150 5 Less Beginning Inventory 6,000 6,150 6,300 6,450 6,600 6,750 6 Required Purchases in Units 10,150 10,400 10,650 10,900 11,150 11,400 7 Required Purchases in Dollars 60,900! 62,400 63,900 65,400 66,900 68,400 8 Budgeted Sales in Dollars 120,000 123,000 126,000 129,000 132,000 135,000 9 Sales Commissions in Dollars 12,000 12,300 12,600 12,900 13,200 13,500 10 11 Collection Ratio January February March April May June 12 January 25% 50% 256 0% 0% 0% 13 February 25% 50% 25% 09 09 14 March 25% 50% 25% 096 15 April 50% 25% 16 May 17 June 096 18 July 19 August 0% 096 20 September 096 096 21 October 0% 096 Inputs Supporting Schedules Operating Budget Cash Budget Ready July 0% OX August September October November December 0% 096 0% 0% OX 09 0% 0% 0% 0% 25% 0% 0% 096 25% OX 0% 0% 0% 0% 25% 8 68 50% 25% 0% 25% 09 999 50% 25% 04 50% 25% 50% 096 25% 25% 50% 09 25% 25% AutoSave Off A cash_flow_budget_v3 - Excel Search Mohammad Ishtiaque Khan MI File Home Insert Page Layout Formulas Data Review View Help Share Comments X Cut I General A P2P 3 E A AutoSum" 49 O $ % 948_ Insert Delete Format Fill Clear lili Ideas Sensitivity Conditional Format as Cell Formatting Table Styles Sort & Filter Find & Select Number Styles Cells Editing Ideas Sensitivity N O P Q June G July 11250 6900 18150 H I J K L M August September October November December 11500 11750 1 2000 12250 12500 12750 7050 7200 7350 7500 7650 7650 18550 18950 19350 19750 20150 20400 Calibri 11 AA A = = = 2 a Wrap Text LG Copy v Paste B I U L A A SE53Merge & Center Format Painter Clipboard Font Alignment B10 : X fi B C D E F 1 Wally's Widgets January February March April May 2 Budgeted Sales in Units 10000 10250 10500 10750 11000 3 Add: Ending Inventory 6150 6300 6450 6600 6750 4 Total Needs 16150 16550 16950 17350 17750 5 Less Beginning Inventory 6000 6 Required Purchases in Units 10150 7 Required Purchases in Dollars 60900 8 Budgeted Sales in Dollars 12000 9 Sales Commissions in Dollars 12000 10 11 Collection Ratio January February March April 12 January 13 February 14 March 15 April 16 May 17 June 18 July 19 August 20 September 21 October 22 November 23 December April May June June July August September October November December 24 January February March April May June July August September October November December 25 Collections 26 January 27 February 28 March 29 April Inputs Supporting Schedules Operating Budget Cash Budget + Donde Resources Presenter Info Step 17 of 40: Beginning Inventory Slide ID: 4512 You are the ter m ini Step 17 of 40: Beginning Inventory January February D March E April F October November December May OUTLINE 1 Wally's Widgets 5 Less Beginning Inventory 6 Required Purchases in Units 7 Required Purchases in Dollars 8 Budgeted Sales in Dollars 9 Sales Commissions in Dollars 1 Search Your turn: The Beginning Inventory formula is similar in nature to the Ending Inventory formula, but is a percentage of current month sales (as opposed to the following month's sales as you used earlier). 14. Step 11 of 40: Table Feature 15. Step 12 of 40: Create Series of months 11 Collection Ratio January February March April May J2 October November December 12 January 13 February Inputs Supporting Schedules Operating Budget Cash Budget E F G H I 1 Wally's Widgets January February March April May June July August 5 Less Beginning Inventory 60001 6 Required Purchases in Units 7 Required Purchases in Dollars 2 If you calculate a number outside of Excel, such as with your calculator and 8 Budgeted Sales in Dollars then type it into the spreadsheet for this exercise, the cell turn red. You will not 9 Sales Commissions in Dollars get full credit for any such cells that contain static values instead of formulas. 16. Step 13 of 40: First Month's Sales in Units 17. Step 14 of 40: Projected Increase in Sales 10 January February March April May June July August Se 18. Step 15 of 40: Ending Inventory 11 Collection Ratio 12 January 13 February Inputs Supporting Schedules Operating Budget Cash Budget 19. Step 16 of 40: Excel's AutoSum Command Students Excel www.studentsexcel.com Need help? Email: support@studentsexcel.com 20. Step 17 of 40: Beginning Inventory 20/43 00:11 / 00:45 10,000 12.00 50% Budget Assumptions First Month's Sales Sales Price Gross Margin 1 Commission 3 Sales Growth Ending Inventory 10 Minimum Cash Balance Interest Rate 10% 250 60% 20,000 12% 2. 3 Collections Payments 25% 60% 14 Month 1 15 Month 2 16 Month 3 50% 40% 25% 18 Operating Expenses 19 Payroll 20 Utilities 21 Other Per Month $ 192,000! $84,000 $234,000 Resources Presenter Info Step 18 of 40: Required Purchases in Units Slide ID: 4513 Step 18 of 40: Required Purchases in Units and help? Cruil: suppend Cl.com OUTLINE Search 18. Step 15 of 40: Ending Inventory C D E K L M 1 Wally's Widgets January February March April May October November December 6 Required Purchases in Units 7 Required Purchases in Dollar 1 8 Budgeted Sales in Dollars Your turn:Use an Excel formula to compute the number of inventory units 9 Sales Commissions in Dollars Wally's Widgets will need to purchase each month to maintain the desired ending inventory balance 11 Collection Ratio 12 January 13 February 14 March Inputs Supporting Schedules Operating Budget Cash Budget DE F March April May June Order of Operations: = 100*0.03 100.21 Excel calculates formulas from left to 100+7*0.03 right, and then in this order: =(100+7)*0.03 Parenthesis Exponents Click this button or press Shift-F11 Enter the formulas shown to Division to insert a new worksheet. Assign get a sense of Excel's order Multiplication the name Order of Operations to this worksheet. of operations. As you can Addition see, parentheses alter the Subtraction order of operations. 19. Step 16 of 40: Excel's AutoSum Command 20. Step 17 of 40: Beginning Inventory 3.211 21. Step 18 of 40: Required Purchases in Units 22. Step 19 of 40: Required Purchases in Dollars Budget Cash Budget (O Inputs Supporting Schedules Order of Operations Operatin ... + 23. Step 20 of 40: Budgeted Sales and Commissions Students Excel www.studentsexcel.com Need help? Email: support@studentsexcel.com 21/43 00:03/04:21 c 24. Step 21 of 40: Collections Percentages m Resources Presenter Info Step 20 of 40: Budgeted Sales and Commissions Slide ID: 4515 Step 20 of 40: Budgeted Sales and Commissions www. A.com andhap Cruail: suppor t .com OUTLINE Search 10 18. Step 15 of 40: Ending Inventory 19. Step 16 of 40: Excel's AutoSum Command C D E F K L M 1 Wally's Widgets January ruary March April May October November December 8 Budgeted Sales in Dollars 9 Sales Commissions in Dollars ? Your turn: Compute the budgeted sales in dollars for 11 Collection Ratio January each month and set the number format to General. 12 January 13 February 14 March 15 April 16 May Inputs Supporting Schedules Order of Operations Operating Budget LM 1 Wally's Widgets January February March April May October November December 9 Sales Commissions in Dollars 10 11 Collection Ratio January F2 December 12 January Your turn: Compute the sales commission for 13 February each month and set the number format to General. 14 March 15 April 16 May 17 June Inputs Supporting Schedules Order of Operations Operating Budget 20. Step 17 of 40: Beginning Inventory 21. Step 18 of 40: Required Purchases in Units 22. Step 19 of 40: Required Purchases in Dollars 23. Step 20 of 40: Budgeted Sales and Commissions Students Excel www.studentsexcel.com Need help? Email: support@studentsexcel.com . 23/ 43 23143 00:10 / 00:21 00:10 /00:21 G O 24. Step 21 of 40: Collections Percentages X 1-Gross_Margin)"sales_Price*B6 July 11.500 7,050 18,550 6,900 11,650 69,900 138,000 13,800 August September October November December 11.750 12.000 12,250 12.500 12.750 7,200 7,350 7 ,500 7,650 7,650 18,950 19,350 19,750 20,150 20,400 7,050 7,200 7,350 7,500 7,650 11,900 12,150 12,400 12,650 12,750 71,400 72,900 74,400 75,900 76,500 141,000 144,000 147,000 150,000 153,000 14,100 14,400 14,700 15,000 15,300 DE 1 Wally's Widgets January February March April May June 2 Budgeted Sales in Units 10,000 10,250 10,500 10,750 11.00 11,250 3 Add: Ending Inventory 6,150 6,300 6,450 6,600 6,750 6,900 4 Total Needs 16,150 16,550 16,950 17,350 17,750 18,150 5 Less Beginning Inventory 6,000 6,150 6,300 6,450 6,600 6,750 6 Required Purchases in Units 10,150 10,400 10,650 10,900 11,150 11,400 7 Required Purchases in Dollars 60,900! 62,400 63,900 65,400 66,900 68,400 8 Budgeted Sales in Dollars 120,000 123,000 126,000 129,000 132,000 135,000 9 Sales Commissions in Dollars 12,000 12,300 12,600 12,900 13,200 13,500 10 11 Collection Ratio January February March April May June 12 January 25% 50% 256 0% 0% 0% 13 February 25% 50% 25% 09 09 14 March 25% 50% 25% 096 15 April 50% 25% 16 May 17 June 096 18 July 19 August 0% 096 20 September 096 096 21 October 0% 096 Inputs Supporting Schedules Operating Budget Cash Budget Ready July 0% OX August September October November December 0% 096 0% 0% OX 09 0% 0% 0% 0% 25% 0% 0% 096 25% OX 0% 0% 0% 0% 25% 8 68 50% 25% 0% 25% 09 999 50% 25% 04 50% 25% 50% 096 25% 25% 50% 09 25% 25% AutoSave Off A cash_flow_budget_v3 - Excel Search Mohammad Ishtiaque Khan MI File Home Insert Page Layout Formulas Data Review View Help Share Comments X Cut I General A P2P 3 E A AutoSum" 49 O $ % 948_ Insert Delete Format Fill Clear lili Ideas Sensitivity Conditional Format as Cell Formatting Table Styles Sort & Filter Find & Select Number Styles Cells Editing Ideas Sensitivity N O P Q June G July 11250 6900 18150 H I J K L M August September October November December 11500 11750 1 2000 12250 12500 12750 7050 7200 7350 7500 7650 7650 18550 18950 19350 19750 20150 20400 Calibri 11 AA A = = = 2 a Wrap Text LG Copy v Paste B I U L A A SE53Merge & Center Format Painter Clipboard Font Alignment B10 : X fi B C D E F 1 Wally's Widgets January February March April May 2 Budgeted Sales in Units 10000 10250 10500 10750 11000 3 Add: Ending Inventory 6150 6300 6450 6600 6750 4 Total Needs 16150 16550 16950 17350 17750 5 Less Beginning Inventory 6000 6 Required Purchases in Units 10150 7 Required Purchases in Dollars 60900 8 Budgeted Sales in Dollars 12000 9 Sales Commissions in Dollars 12000 10 11 Collection Ratio January February March April 12 January 13 February 14 March 15 April 16 May 17 June 18 July 19 August 20 September 21 October 22 November 23 December April May June June July August September October November December 24 January February March April May June July August September October November December 25 Collections 26 January 27 February 28 March 29 April Inputs Supporting Schedules Operating Budget Cash Budget + Donde