this is trh question
please use these images of the worksheeg to solve this problem please use excel or soemthing
please use the E colum. thank you.
M UU SAVII ll again. TICKLERS (optional) Worksheet.Ranger wants to have each budget on the MASTER2 worksheet show not only monthly figures but also totals for the quarter. Use column E to present totals for all budget lines (except in the budgeted income statement and balance sheet). Consult your textbook for proper treatment of beginning and ending balances in the production, purchases, and cash budgets. Preview the printout to make sure that the worksheet including the totals will print out neatly, and then print the worksheet. Save the completed file as MASTERT Chart. Using the MASTER2 file, create a 3-D area chart to show the monthly sales volume in units, from May through October. No Chart Data Table is needed; use data from the worksheet. Enter your name somewhere on the chart. Save the file again as MASTER2. Print the chart. i s Spelling Thesaurus Smart Translate Lookup Insights Language Show/Hide Comment Show All Comments Show Ink New Delete Previous Next Comment Protect Protect Sheet Workbook Wc Proofing Comments con tonowe, complemento G106 CA C DIE MASTER Master Budget Data Section CAN- 7 Actual and Budgeted Unit Sales May June July August September 13 October 10 1,400 1.000 1.600 1.400 1.500 1.200 14 16 17 15 Balance Sheet June 30 Cash Accounts receivable Merchandise inventory (640 units) Fixed assets (net) 20 Total assets $12,480 239,120 70,400 130.000 $452 000 19 Fe 22 Accounts payable (merchandise) Owner's equity Total liabilities & equity $136,400 315,600 $452 000 24 $196 $110 40% 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 20% 50% 30% 25% of sales $40,000 $2.000 Answer Section Sales Budget 43 Units 44 Selling Price 45 Dollars Worksheet what if Analysis 4 Ready July August 1,600 1,400 $196 196 $313.600 274.400 What If Analysis 5 Chart September 1,500 196 294.000 Proofing Insights Language Comments G106 39 40 Answer Section 41 42 Sales Budget 43 Units 44 Selling Price 45 Dollars July 1,600 $196 $313,600 August 1.400 196 274,400 September 1,500 196 294,000 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 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 $167,200 $158.400 $151,800 57 58 July $12,480 August $8,000 September $8,000 82,320 98,000 62,720 58,800 156,800 54,880 66 94,080 137,200 58.800 $298,080 $255,520 $278.480 69 Cash Budget 59 Cash balance, beginning 60 Cash receipts 61 Collections from customers: From May sales From June sales From July sales From August sales From September sales 67 Total cash available 68 Cash disbursements 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 $136,400 78,400 40,000 $167,200 68,600 40,000 109 $275.909 $158,400 73,500 40,000 191 $272 091 $25,989 8.000 $17.989 $254 800 $720 8.000 ($7.280) 8.000 ($5.429) $7.280 $0 $5,429 0 $5,429 $8.000 $7 280 $8,000 (12.709) ($12.709) $13 280 82 Worksheet What If Analysis 4 What If Analysis 5 Chart Ready $5,429 0 $7,280 0 $7,280 $8,000 $5,429 $8,000 $0 (12,709) ($12,709) $13,280 84 78 Borrowing 79 Repayment 80 Total effects of financing 81 Cash balance, ending 82 83 Forecasted Income Statement 85 For Quarter Ended September 30 86 Sales 87 Cost of goods sold 88 Gross profit 89 Expenses 90 Variable expenses 91 Fixed expenses 92 Depreciation expense Interest expense 94 Total expenses 95 Net income 96 $882,000 495,000 $387,000 $220,500 120,000 6,000 93 300 $346,800 $40 200 97 Forecasted Balance Sheet as of September 30 100 Assets 101 Cash 102 Accounts receivable 103 Merchandise inventory 104 Fixed assets (net) 105 Total assets 1061 107 Liabilities & equity 108 Accounts payable 109 Loans payable 110 Owner's equity 111 Total liabilities & equity 112 113 $13,280 317,520 52,800 124,000 $507,600 $151,800 355,800 $507,600 M UU SAVII ll again. TICKLERS (optional) Worksheet.Ranger wants to have each budget on the MASTER2 worksheet show not only monthly figures but also totals for the quarter. Use column E to present totals for all budget lines (except in the budgeted income statement and balance sheet). Consult your textbook for proper treatment of beginning and ending balances in the production, purchases, and cash budgets. Preview the printout to make sure that the worksheet including the totals will print out neatly, and then print the worksheet. Save the completed file as MASTERT Chart. Using the MASTER2 file, create a 3-D area chart to show the monthly sales volume in units, from May through October. No Chart Data Table is needed; use data from the worksheet. Enter your name somewhere on the chart. Save the file again as MASTER2. Print the chart. i s Spelling Thesaurus Smart Translate Lookup Insights Language Show/Hide Comment Show All Comments Show Ink New Delete Previous Next Comment Protect Protect Sheet Workbook Wc Proofing Comments con tonowe, complemento G106 CA C DIE MASTER Master Budget Data Section CAN- 7 Actual and Budgeted Unit Sales May June July August September 13 October 10 1,400 1.000 1.600 1.400 1.500 1.200 14 16 17 15 Balance Sheet June 30 Cash Accounts receivable Merchandise inventory (640 units) Fixed assets (net) 20 Total assets $12,480 239,120 70,400 130.000 $452 000 19 Fe 22 Accounts payable (merchandise) Owner's equity Total liabilities & equity $136,400 315,600 $452 000 24 $196 $110 40% 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 20% 50% 30% 25% of sales $40,000 $2.000 Answer Section Sales Budget 43 Units 44 Selling Price 45 Dollars Worksheet what if Analysis 4 Ready July August 1,600 1,400 $196 196 $313.600 274.400 What If Analysis 5 Chart September 1,500 196 294.000 Proofing Insights Language Comments G106 39 40 Answer Section 41 42 Sales Budget 43 Units 44 Selling Price 45 Dollars July 1,600 $196 $313,600 August 1.400 196 274,400 September 1,500 196 294,000 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 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 $167,200 $158.400 $151,800 57 58 July $12,480 August $8,000 September $8,000 82,320 98,000 62,720 58,800 156,800 54,880 66 94,080 137,200 58.800 $298,080 $255,520 $278.480 69 Cash Budget 59 Cash balance, beginning 60 Cash receipts 61 Collections from customers: From May sales From June sales From July sales From August sales From September sales 67 Total cash available 68 Cash disbursements 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 $136,400 78,400 40,000 $167,200 68,600 40,000 109 $275.909 $158,400 73,500 40,000 191 $272 091 $25,989 8.000 $17.989 $254 800 $720 8.000 ($7.280) 8.000 ($5.429) $7.280 $0 $5,429 0 $5,429 $8.000 $7 280 $8,000 (12.709) ($12.709) $13 280 82 Worksheet What If Analysis 4 What If Analysis 5 Chart Ready $5,429 0 $7,280 0 $7,280 $8,000 $5,429 $8,000 $0 (12,709) ($12,709) $13,280 84 78 Borrowing 79 Repayment 80 Total effects of financing 81 Cash balance, ending 82 83 Forecasted Income Statement 85 For Quarter Ended September 30 86 Sales 87 Cost of goods sold 88 Gross profit 89 Expenses 90 Variable expenses 91 Fixed expenses 92 Depreciation expense Interest expense 94 Total expenses 95 Net income 96 $882,000 495,000 $387,000 $220,500 120,000 6,000 93 300 $346,800 $40 200 97 Forecasted Balance Sheet as of September 30 100 Assets 101 Cash 102 Accounts receivable 103 Merchandise inventory 104 Fixed assets (net) 105 Total assets 1061 107 Liabilities & equity 108 Accounts payable 109 Loans payable 110 Owner's equity 111 Total liabilities & equity 112 113 $13,280 317,520 52,800 124,000 $507,600 $151,800 355,800 $507,600
