In this exercise, you will create budgets and perform CVP analyses for Garys 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: 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 Cash Disbursements for Merchandise Purchases Cash Disbursements for Operating Expenses Loan Payments (Principal) Quarter #4 $118,400 $19,600 $8,000 $960 $730 $0 $98,300 $73,600 $2,600 Quarter #3 $156,000 $33,200 $4,230 $1,550 $850 $2,350 $127,300 $71,400 $3,900 Quarter #2 $126,400 $21,200 $3,800 $1,100 $420 $0 $95,300 $69,100 $3,900 Quarter #1 $172,000 $24,600 $9,700 $1,400 $650 $0 $107,400 $81,300 $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 14 and for the beginning cash balances for Quarters 24. 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: For 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 AB will print on every page. 14. Switch to the Purchases Budget tab and enter this data: Budgeted Cost of Goods Sold Desired Ending Inventory Beginning Inventory $420,000 $35,000 $26,700 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, fixed 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.