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 1–4 and for the beginning cash balances for Quarters 2–4.
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.
9. 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 A–B 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%.