Question

Aply Yours 327 EA10-A2 Complete Budgets and a CVP Analysis for Gary's Housewares In this exercise, you will create budgets and perform CVP analyses for Gary's Housewares. You will first create a cash budget, adjusting settings to allow for efficient data examination. You will then create a purchases budget and adjust print settings. Lastly, you will perform CVP analyses using Goal Seek and Solver 1. Open EA10-A2-Budget from your Chapter 10 folder and save it as: EA10-A2-Budget-[YourName] 2. On the Cash Budget tab, enter 47200 as the beginning cash balance for the first quarter. 3. Enter the data for the four quarters: Quarter 14 Quarter 3 Quarter N2 Quarter #1 Cash Receipts from In-Store S118,400 $156,000 S126,400 5172,000 Customer Sales Cash Receipts from Online $19.600 $33,200 $21,200 $24.600 Sales Cash Receipts from $8,000 $4,230 $3,800 $9,700 Services Rendered Reduction in Notes 5960 $1,550 $1,100 $1,400 Recevable interest Received 5730 5850 $420 5650 Sale of Automobile SO $2,350 50 Cash Disbursements for $98,300 $127,300 595,300 $107,400 Merchandise Purchases Cash Disbursements for $73,600 $71,400 569,100 $81,300 Operating Expenses Loan Payments Principal $2,600 $3,900 $3,900 $3.900 4. Create formulas in the appropriate locations to summarize cash receipts, cash available, and cash disbursements 5. Create formulas for the ending cash balances for Quarters 1-4 and for the beginning cash balances for Quarters 2-4 Adjust Margins, Orientation, and Print Area 6. Apply Wide margins and Landscape orientation and then switch to Normal margins and Portrait orientation 7. Set the print area to display all data through the Total Cash Receipts row 8. In Backstage view, scale the worksheet to Fit All Columns on One Page and set the orientation to Landscape

Answer 1

Quarter 1 47,200 Gary's Housewares Cash Budget in $ For the year Ending December 31, 2017 Quarter 4 Quarter 3 Quarter 2 Beginning Cash Balance 43,150 47,570 62,950 Add- Cash Receipts from In Store customer Sales 118,400 156,000 126,400 Cash Receipts from online Sales 19.600 33,200 21,200 Cash Receipts from In service Rendered 8,000 4,230 3,800 Reduction in Notes Receivable 960 1,550 1,100 Interest Received 730 850 420 Sale of Automoblie 2,350 Total Cash Receipts 147,690 198,180 152,920 Gary's Housewares Purchase Budget in $ For the year Ending December 31, 2017 Budgeted Cost of Goods Sold 420,000 Add:Desired Ending Inventory 35,000 Total Inventory 455,000 Less:Beginning Inventory 26,700 Required Merchandise Purchas 428,300 172,000 24,600 9,700 1,400 650 208,350 40 Gary's Housewares CVP Income Statement in $+ For the year Ending December 31, 2017 Sales Revenue per unit Less:Variable costs per unit Contribution Margin per unit Less:Fixed Costs Net Income Total Cash Available (A) 190,840 245,750 215,870 255,550 Less Cash Disbursement for Merchandise Purch Cash Disbursement for operating Expenses Loan Payments (Principal) Total Cash Disbursement (B) 98,300 73,600 2,600 174,500 127,300 71,400 - 3,900 202,600 95,300 69,100 3,900 168,300 107,400 81,300 3,900 192,600 Breakeven Units sold fixed cost/contribution per unit 16875 Ending Cash Balance (A-B) 16340 43150 47570 62950