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 Apply Print Titles, Headings, and Gridlines 9. Remove the scaling so the worksheet will print on multiple pages. 10. Insert vertical page breaks between each of the four quarters, taking care to adjust the three-line header at the top of the worksheet as necessary. Hint: Remove the merged and centered attributes in the header before adding page breaks. 11. Edit the header within row 3 to: Por Quarters Ending in 2017 12. Merge and center the three-line header at the top of the worksheet so that it does not extend beyond the fourth quarter data 13. Set the worksheet so the three-line header at the top and the headers for columns A-B will print on every page. 14. Switch to the Purchases Budget tab and enter this data: Budgeted Cost of Goods Sold $420,000 Desired Ending Inventory $35,000 Beginning inventory $26,200 15. Create formulas in the appropriate locations for total inventory required and required merchandise purchases. 16. Simultaneously apply gridlines and headings to every printed page on the Cash Budget and Purchases Budget tabs Use Goal Seek and Solver 17. Switch to the CVP Analysis #1 tab and enter this data: Sales Revenue Per Unit Variable Costs Per Unit Fixed Costs $40 $31 $151,875 18. Create formulas in the appropriate locations for all remaining elements except Units Sold, which should remain blank. 19. Use Goal Seek to determine the breakeven unit sales. 20. Create a second CVP Analysis tab (named appropriately) and use Goal Seek to deter mine unit sales necessary to generate a net income of $72,000. 21. Create a third CVP Analysis tab (named appropriately) and use Solver to determine the maximum net income if all of the following apply unit sales remain the same as those in the second CVP analysis, sales revenue per unit cannot exceed $43. variable costs per unit cannot fall below $28. fed costs cannot fall below $150,000, and contribution margin as a percentage of sales revenue cannot exceed 34% 22. If necessary, widen columns to display all Solver results. 23. Save and cose your file. I J om C D E F G H Gary's Housewares Cash Budget For the Year Ending December 31, 2017 7 Quarter #4 Quarter #3 Quarter #2 Quarter #1 5 Beginning Cash Balance 3 Add: Cash Receipts from In-Store Customer Sales Cash Receipts from Online Sales Cash Receipts from Services Rendered Reduction in Notes Receivable Interest Received Sale of Automobile 4 Total Cash Receipts: 6 Total Cash Available 8 Less: Cash Disbursements for Merchandise Purchases Cash Disbursements for Operating Expenses 0 Loan Payments (Principal) 1 Total Cash Disbursements: 3 Ending Cash Balance: 00 VOU Gary's Housewares Purchases Budget For the Year Ending December 31, 2017 Budgeted Cost of Goods Sold Add: Desired Ending Inventory Total Inventory Required Less: Beginning Inventory Required Merchandise Purchases U + WI F B C D E Gary's Florist CVP Income Statement For the Year Ending December 31, 2017 Total Per Unit Sales Revenue Variable Costs Contribution Margin Fixed Costs Net Income Units Sold