I've done the most part

I've done all the calculation but I'm having trouble figuring out the question bellow

3.

a. Is Quest Industries expecting to earn a profit during the next quarter? If so, how much profit? I had

Yes, 20,052

correct me if I'm wrong

b. Does the company need to borrow cash during the quarter? Can it make any

repayments? Explain. (carefully review rows 74 through 80). I had

Yes, we need to borrow 5240 July and 2739 August

4.

Suppose the company has to revise its estimates because of a downturn in the economy. Unit sales for July, August, and September will be half (50%) of the original estimates. Revise the estimates in the cells for those three months (B10-B12). After this is done, check your forecasted balance sheet. It should still balance! What effect will this new state of affairs have on net income, borrowing, and debt ratio (liabilities/assets)?

WHAT-IF ANALYSIS:

What are the new values after the changes for:

Net Income?

Borrowing?

Debt ratio?

LEARNING OBJECTIVES * Develop a master budget including a balance sheet, income statement, and cash budget. * Interpret the budgets and the differences between profit and cash flow. Contrast the effects of changes in projected sales on the budgets. * Alter the worksheet by adding a column to show quarterly totals. Problem Data: see Worksheet tab REQUIRED: 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 1. You have been asked to prepare a master budget for the upcoming quarter (July, August, and September). The components of this budget are a monthly sales budget, a monthly purchases budget, a monthly cash budget, a forecasted income statement for the quarter, and a forecasted September 30 balance sheet. The worksheet formulas are in the detailed word file. The worksheet tab below has data and an Answer Section for you to use in preparing your budget. Quest Industries desires to maintain a minimum cash balance of $8,000 at the end of each month. If this goal cannot be met, the company borrows the exact amount needed to reach its goal. If the company has a cash balance greater than $8,000 and also has loans payable outstanding, the amount in excess of $8,000 is paid to the bank. Annual interest of 18% is paid on a monthly basis on the outstanding balance. 2. Input the correct formulas in the Answer Section of the worksheet. Check to make sure that your balance sheet balances. Enter your name in cell A1. Check figures: Excess (deficit) of cash over needs (cell B76), ($5,240); forecasted net income (cell D95), $20,052; total assets (cell D105), $321,152. 3. Review the completed master budget and answer the following questions: a. Is Quest Industries expecting to earn a profit during the next quarter? If so, how much profit? b. Does the company need to borrow cash during the quarter? Can it make any repayments? Explain. (carefully review rows 74 through 80). MASTER Master Budget 1,500 1,000 1,600 1,400 1,500 1,200 $12,300 122,500 35,200 130,000 $300,000 2 3 4 5 Data Section 6 7 Actual and Budgeted Unit Sales 8 May 9 June 10 July 11 August 12 September 13 October 14 15 Balance Sheet, June 30, 2009 16 Cash 17 Accounts receivable 18 Merchandise inventory (640 units) 19 Fixed assets (net) 20 Total assets 21 22 Accounts payable (merchandise) 23 Owner's equity 24 Total liabilities & equity 25 26 Other Data 27 Average selling price 28 Average purchase cost per unit 29 Desired ending inventory 30 (% of next month's unit sales) 31 Collections from customers: 32 Collected in month of sale 33 Collected in month after sale 34 Collected two months after sale 35 Projected cash payments: 36 Variable expenses 37 Fixed expenses (per month) 38 Depreciation per month 39 $74,800 225,200 $300,000 $98 $55 40% 20% 50% 30% 25% of sales $20,000 $1,000 July 1,600 August 1,400 September 1,500 $156,800 137,200 147,000 July 560 1,600 2,160 640 1,520 August 600 1,400 2,000 560 1,440 September 480 1,500 1,980 600 1,380 $83,600 $79,200 $75,900 July $12,300 August $8,000 September $8,000 40 Answer Section 41 42 Sales Budget 43 Units 44 45 Dollars 46 47 48 Unit Purchases Budget 49 Desired ending inventory 50 Current month's unit sales 51 Total units needed 52 Beginning inventory 53 Purchases (units) 54 55 Purchases (dollars) 56 57 58 Cash Budget 59 Cash balance, beginning 60 Cash receipts: 61 Collections from customers: 62 From May sales 63 From June sales 64 From July sales 65 From August sales 66 From September sales 67 Total cash available 68 Cash disbursements: 69 Merchandise 70 Variable expenses 71 Fixed expenses 72 Interest paid 73 Total disbursements 74 Cash balance before financing 75 Less: Desired ending balance 76 Excess (deficit) of cash over needs 77 Financing 78 Borrowing 79 Repayment 80 Total effects of financing 81 Cash balance, ending 82 83 44,100 49,000 31,360 29,400 78,400 27,440 47,040 68,600 29,400 $153,040 $136,760 $143,240 $74,800 39,200 20,000 0 $134,000 $2,760 8,000 ($5,240) $83,600 34,300 20,000 79 $137,979 $5,261 8,000 ($2,739) $79,200 36,750 20,000 120 $136,070 $16,970 8,000 $8,970 $5,240 0 $5,240 $8,000 $2,739 0 $2,739 $8,000 $0 (7,979) ($7,979) $8,992 $441,000 247,500 $193,500 $110,250 60,000 3,000 198 $173,448 $20,052 83 84 Forecasted Income Statement 85 For Quarter Ended September 30, 2009 86 Sales 87 Cost of goods sold 88 Gross profit 89 Expenses: 90 Variable expenses 91 Fixed expenses 92 Depreciation expense 93 Interest expense 94 Total expenses 95 Net income 96 97 98 Forecasted Balance Sheet 99 September 30, 2009 100 Assets: 101 Cash 102 Accounts receivable 103 Merchandise inventory 104 Fixed assets (net) 105 Total assets 106 107 Liabilities & equity: 108 Accounts payable Loans payable 110 Owner's equity 111 Total liabilities & equity 112 113 114 Required Part 3 questions. $8,992 158,760 26,400 127,000 $321,152 109 $75,900 0 245,252 $321,152 LEARNING OBJECTIVES * Develop a master budget including a balance sheet, income statement, and cash budget. * Interpret the budgets and the differences between profit and cash flow. Contrast the effects of changes in projected sales on the budgets. * Alter the worksheet by adding a column to show quarterly totals. Problem Data: see Worksheet tab REQUIRED: 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 1. You have been asked to prepare a master budget for the upcoming quarter (July, August, and September). The components of this budget are a monthly sales budget, a monthly purchases budget, a monthly cash budget, a forecasted income statement for the quarter, and a forecasted September 30 balance sheet. The worksheet formulas are in the detailed word file. The worksheet tab below has data and an Answer Section for you to use in preparing your budget. Quest Industries desires to maintain a minimum cash balance of $8,000 at the end of each month. If this goal cannot be met, the company borrows the exact amount needed to reach its goal. If the company has a cash balance greater than $8,000 and also has loans payable outstanding, the amount in excess of $8,000 is paid to the bank. Annual interest of 18% is paid on a monthly basis on the outstanding balance. 2. Input the correct formulas in the Answer Section of the worksheet. Check to make sure that your balance sheet balances. Enter your name in cell A1. Check figures: Excess (deficit) of cash over needs (cell B76), ($5,240); forecasted net income (cell D95), $20,052; total assets (cell D105), $321,152. 3. Review the completed master budget and answer the following questions: a. Is Quest Industries expecting to earn a profit during the next quarter? If so, how much profit? b. Does the company need to borrow cash during the quarter? Can it make any repayments? Explain. (carefully review rows 74 through 80). MASTER Master Budget 1,500 1,000 1,600 1,400 1,500 1,200 $12,300 122,500 35,200 130,000 $300,000 2 3 4 5 Data Section 6 7 Actual and Budgeted Unit Sales 8 May 9 June 10 July 11 August 12 September 13 October 14 15 Balance Sheet, June 30, 2009 16 Cash 17 Accounts receivable 18 Merchandise inventory (640 units) 19 Fixed assets (net) 20 Total assets 21 22 Accounts payable (merchandise) 23 Owner's equity 24 Total liabilities & equity 25 26 Other Data 27 Average selling price 28 Average purchase cost per unit 29 Desired ending inventory 30 (% of next month's unit sales) 31 Collections from customers: 32 Collected in month of sale 33 Collected in month after sale 34 Collected two months after sale 35 Projected cash payments: 36 Variable expenses 37 Fixed expenses (per month) 38 Depreciation per month 39 $74,800 225,200 $300,000 $98 $55 40% 20% 50% 30% 25% of sales $20,000 $1,000 July 1,600 August 1,400 September 1,500 $156,800 137,200 147,000 July 560 1,600 2,160 640 1,520 August 600 1,400 2,000 560 1,440 September 480 1,500 1,980 600 1,380 $83,600 $79,200 $75,900 July $12,300 August $8,000 September $8,000 40 Answer Section 41 42 Sales Budget 43 Units 44 45 Dollars 46 47 48 Unit Purchases Budget 49 Desired ending inventory 50 Current month's unit sales 51 Total units needed 52 Beginning inventory 53 Purchases (units) 54 55 Purchases (dollars) 56 57 58 Cash Budget 59 Cash balance, beginning 60 Cash receipts: 61 Collections from customers: 62 From May sales 63 From June sales 64 From July sales 65 From August sales 66 From September sales 67 Total cash available 68 Cash disbursements: 69 Merchandise 70 Variable expenses 71 Fixed expenses 72 Interest paid 73 Total disbursements 74 Cash balance before financing 75 Less: Desired ending balance 76 Excess (deficit) of cash over needs 77 Financing 78 Borrowing 79 Repayment 80 Total effects of financing 81 Cash balance, ending 82 83 44,100 49,000 31,360 29,400 78,400 27,440 47,040 68,600 29,400 $153,040 $136,760 $143,240 $74,800 39,200 20,000 0 $134,000 $2,760 8,000 ($5,240) $83,600 34,300 20,000 79 $137,979 $5,261 8,000 ($2,739) $79,200 36,750 20,000 120 $136,070 $16,970 8,000 $8,970 $5,240 0 $5,240 $8,000 $2,739 0 $2,739 $8,000 $0 (7,979) ($7,979) $8,992 $441,000 247,500 $193,500 $110,250 60,000 3,000 198 $173,448 $20,052 83 84 Forecasted Income Statement 85 For Quarter Ended September 30, 2009 86 Sales 87 Cost of goods sold 88 Gross profit 89 Expenses: 90 Variable expenses 91 Fixed expenses 92 Depreciation expense 93 Interest expense 94 Total expenses 95 Net income 96 97 98 Forecasted Balance Sheet 99 September 30, 2009 100 Assets: 101 Cash 102 Accounts receivable 103 Merchandise inventory 104 Fixed assets (net) 105 Total assets 106 107 Liabilities & equity: 108 Accounts payable Loans payable 110 Owner's equity 111 Total liabilities & equity 112 113 114 Required Part 3 questions. $8,992 158,760 26,400 127,000 $321,152 109 $75,900 0 245,252 $321,152