Excel Project A. Expand the cash flow budget you created in Problem 12.4 to include a row for expected cash outflows equal to 77% of the current month's sales. B. Also add a row to calculate the amount of cash that needs to be borrowed in order to maintain a minimum cash balance of $50,000 at the end of each month. C. Add another row to show the cash inflow from borrowing. D. Add another row to show the cumulative amount borrowed. E. Add another row to show the amount of the loan that can be repaid, being sure to maintain a minimum ending balance of $50,000 each month. F. Add appropriate data validation controls to ensure spreadsheet accuracy.

XYZ Company, Quarterly P&L Forecast Sales Sales Returns & Allowances Net Sales Cost of Sales Gross Profit Marketing & Promotion General & Administration Operating Income Before Taxes Provision for Income Taxes Net Operating Income Return on Sales Assumptions Base Quarter Sales Quarterly Sales Growth Rate Sale Returns & Allowance Rate Cost of Sales Rate Marketing & Promotion General & Administration Base Quarter Actual Quarter 1 Forecast Quarter 2 Forecast $100,000 $110,000 $121,000 2,000 2,200 2,420 $98,000 $107,800 $118,580 70,000 77,000 84,700 $28,000 $30,800 $33,880 22,000 22,000 22,000 9,000 9,000 9,000 $(3,000) $(200) $2,880 (1,020) (68) 979 $(1,980) $(132) $1,901 -2.0% $100,000 10.0% 2.0% 70.0% $22,000 $9,000 -0.1% 1.6% XYZ Company, Quarterly P&L Forecast Sales Sales Returns & Allowances Net Sales Cost of Sales Gross Profit Marketing & Promotion General & Administration Operating Income Before Taxes Provision for Income Taxes Net Operating Income Return on Sales Assumptions Base Quarter Sales Quarterly Sales Growth Rate Sale Returns & Allowance Rate Cost of Sales Rate Marketing & Promotion General & Administration Base Quarter Actual Quarter 1 Forecast Quarter 2 Forecast $100,000 $110,000 $121,000 2,000 2,200 2,420 $98,000 $107,800 $118,580 70,000 77,000 84,700 $28,000 $30,800 $33,880 22,000 22,000 22,000 9,000 9,000 9,000 $(3,000) $(200) $2,880 (1,020) (68) 979 $(1,980) $(132) $1,901 -2.0% $100,000 10.0% 2.0% 70.0% $22,000 $9,000 -0.1% 1.6% 100 XYZ Company, Quarterly P&L Forecast Sales Sales Returns & Allowances Net Sales Cost of Sales Gross Profit Marketing & Promotion General & Administration Operating Income Before Taxes Provision for Income Taxes Net Operating Income Return on Sales Assumptions Base Quarter Sales Quarterly Sales Growth Rate Sale Returns & Allowance Rate Cost of Sales Rate Marketing & Promotion General & Administration Base Quarter Actual Quarter 1 Forecast Quarter 2 Forecast $100,000 $110,000 $121,000 2,000 2,200 2,420 $98,000 $107,800 $118,580 70,000 77,000 84,700 $28,000 $30,800 $33,880 22,000 22,000 22,000 9,000 9,000 9,000 $(3,000) $(200) $2,880 (1,020) (68) 979 $(1,980) $(132) $1,901 -2.0% $100,000 10.0% 2.0% 70.0% $22,000 $9,000 -0.1% 1.6% Use the arrows to the left to change the sales growth rate 100 XYZ Company, Quarterly P&L Forecast Sales Sales Returns & Allowances Net Sales Cost of Sales Gross Profit Marketing & Promotion General & Administration Operating Income Before Taxes Provision for Income Taxes Net Operating Income Return on Sales Assumptions Base Quarter Sales Quarterly Sales Growth Rate Sale Returns & Allowance Rate Cost of Sales Rate Marketing & Promotion General & Administration Base Quarter Actual Quarter 1 Forecast Quarter 2 Forecast $100,000 $110,000 $121,000 2,000 2,200 2,420 $98,000 $107,800 $118,580 70,000 77,000 84,700 $28,000 $30,800 $33,880 22,000 22,000 22,000 9,000 9,000 9,000 $(3,000) $(200) $2,880 (1,020) (68) 979 $(1,980) $(132) $1,901 -2.0% $100,000 10.0% 2.0% 70.0% $22,000 $9,000 -0.1% 1.6% Use the arrows to the left to change the sales growth rate 100 XYZ Company, Quarterly P&L Forecast Sales Sales Returns & Allowances Net Sales Cost of Sales Gross Profit Marketing & Promotion General & Administration Operating Income Before Taxes Provision for Income Taxes Net Operating Income Return on Sales Assumptions Base Quarter Sales Quarterly Sales Growth Rate Sale Returns & Allowance Rate Cost of Sales Rate Marketing & Promotion General & Administration Base Quarter Actual Quarter 1 Forecast Quarter 2 Forecast $$$$$$$$$22,000 22,000 22,000 9,000 9,000 9,000 $(31,000) $(31,000) $(31,000) (10,540) (10,540) (10,540) $(20,460) $(20,460) $(20,460) #DIV/0! $100.0% 2.0% 70.0% $22,000 $9,000 #DIV/0! #DIV/0! Use the arrows or drag the center bar to adjust base quarter sales Use the arrows to the left to change the sales growth rate 1000 100 20 XYZ Company, Quarterly P&L Forecast Sales Sales Returns & Allowances Net Sales Cost of Sales Gross Profit Marketing & Promotion General & Administration Operating Income Before Taxes Provision for Income Taxes Net Operating Income Return on Sales Assumptions Base Quarter Sales Quarterly Sales Growth Rate Sale Returns & Allowance Rate Cost of Sales Rate Marketing & Promotion General & Administration Base Quarter Actual Quarter 1 Forecast Quarter 2 Forecast $80,000 $144,000 $259,200 1,600 2,880 5,184 $78,400 $141,120 $254,016 56,000 100,800 181,440 $22,400 $40,320 $72,576 22,000 22,000 22,000 9,000 9,000 9,000 $(8,600) $9,320 $41,576 (2,924) 3,169 14,136 $(5,676) $6,151 $27,440 -7.1% $80,000 80.0% 2.0% 70.0% $22,000 $9,000 4.3% 10.6% Use the arrows or drag the center bar to adjust base quarter sales Use the arrows to the left to change the sales growth rate 800 100 60 XYZ Company, Quarterly P&L Forecast Sales Sales Returns & Allowances Net Sales Cost of Sales Gross Profit Marketing & Promotion General & Administration Operating Income Before Taxes Provision for Income Taxes Net Operating Income Return on Sales Assumptions Base Quarter Sales Quarterly Sales Growth Rate Sale Returns & Allowance Rate Cost of Sales Rate Marketing & Promotion General & Administration Base Quarter Actual Quarter 1 Forecast Quarter 2 Forecast $100,000 $110,000 $121,000 2,000 2,200 2,420 $98,000 $107,800 $118,580 70,000 77,000 84,700 $28,000 $30,800 $33,880 22,000 22,000 22,000 9,000 9,000 9,000 $(3,000) $(200) $2,880 (1,020) (68) 979 $(1,980) $(132) $1,901 -2.0% $100,000 10.0% 2.0% 70.0% $22,000 $9,000 -0.1% 1.6% Use the arrows or drag the center bar to adjust base quarter sales Use the arrows to the left to change the sales growth rate 1000 100