EA10-A1 Complete Budgets and a CVP Analysis for Dairy Producers, Inc. In this exercise, you will create budgets and perform CVP analyses for Dairy Producers, Inc. 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-A1-Budget from your Chapter 10 folder and save it as: EA10-A1-Budget-[YourName] 2. On the Cash Budget tab, enter 3200 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 Delivery Sales Cash Receipts from Services Rendered Interest Received Sale of Machinery Cash Disbursements for Merchandise Purchases Cash Disbursements for Operating Expenses Loan Payments (Principal) Loan Payments (Interest) Quarter #4 $12,200 $91,300 $700 $650 $0 $64,200 $31,000 $2,400 $900 Quarter #3 $11,300 $71,800 $1,600 $650 $4,000 $51,300 $36,900 $2,400 $900 Quarter #2 $8,100 $84,200 $2,300 $600 $0 $55,400 $39,300 $2,400 $900 Quarter #1 $14,200 $81,600 $4,100 $1,300 $0 $61,000 $34,200 $2,400 $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 Narrow 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 in 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 $237,000 $20,000 $25,100 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 $80 $58 $104,500 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 $18,500. 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 $82, variable costs per unit cannot fall below $55, fixed costs cannot fall below $104,500, and contribution margin as a percentage of sales revenue cannot exceed 32%. Dairy Producers, Inc. Cash Budget For the Year Ending December 31, 2017 Quarter #4 Quarter #3 Quarter #2 Quarter #1 Beginning Cash Balance Add: Cash Receipts from In-Store Customer Sales Cash Receipts from Delivery Sales Cash Receipts from Services Rendered Interest Received Sale of Machinery Total Cash Receipts: Total Cash Available Less: Cash Disbursements for Merchandise Purchases Cash Disbursements for Operating Expenses Loan Payments (Principal) Loan Payments (Interest) Total Cash Disbursements: Ending Cash Balance