# Case Study Ch. 10 Any and all would be appreciated, I need to double check my work, and I am defiantly not at all confident with this one. Instructions

Click here to Order a Custom answer to this Question from our writers. It’s fast and plagiarism-free.

Case Study Ch. 10 Any and all would be appreciated, I need to double check my work, and I am defiantly not at all confident with this one. Instructions

Ch. 10 Case Study

Background

You are an accountant for Alphabet Industries. The controller has asked you to complete the cash budget and purchases budget and then perform cost volume profit analysis.

Instructions

1. Complete the cash budget

On row 13 create a formula that sums the cash receipts for each quarter

On row 15, create a formula that calculates the cash available for each quarter using the beginning cash balance and the total cash receipts

On row 21, create a formula that sums the cash disbursements for each quarter

On row 23, create a formula that calculates the ending cash balance for each quarter using the cash available and the total cash disbursements

On row 6, create a formula for the beginning cash balances for quarters 2-4; hint beginning cash balance for one quarter is the ending cash balance for the previous quarter

Change the text in row 3 to the following: For Quarters Ending in 2027

Merge and center each of the three header row of the table across columns A-D

2. Complete the purchases budget

On row 7, create a formula to calculate the total inventory required

On row 9, create a formula to calculate the required merchandise purchases

Apply girdlines to the table and column headings

3. Complete CVP analysis

In cell D6, enter a formula that will take the units sold (B13) and multiple them by the sales revenue per unit (F6) to determine the total sales revenue

In cell D7, enter a formula that will take the units sold (B13) and multiple them by the variable cost per unit (F6) to determine the total variable costs

On row 8, create a formula to calculate contribution margin by taking sales minus variable costs

In cell F9, entere a formula to determine the fixed costs per unit by taking the total fixed costs (D9) and dividing it by the units sold (B13)

On row 10, create a formula to calculate net income by taking the contribution margin minus the fixed costs

In cell D10, use goal seek function to determine the breakeven units sold

The “to value” should be zero

The “changing cell” box should be equal to cell B13

Create a copy of the CVP Analysis #1 worksheet on a new worksheet labeled CVP Analysis #2

In cell D10, use goal seek function

The “to value” should be 52000

The “changing cell” box should be equal to cell B13

Create a copy of the CVP Analysis #2 worksheet on a new worksheet labeled CVP Analysis #3

In cell D10, use the solver function

The set objective box should be equal to D10

The “by changing variable cells” box should be equal to cell F6, F7, & D9

Setup the following constraints:

D9>=121000

F6<=125 F7>=95

F8<=F6*23% Cash Budget Dairy Producers, Inc. Cash Budget For the Year Ending December 31, 2017 Quarter #4 Quarter #3 Quarter #2 Quarter #1 Beginning Cash Balance \$ 3,200 Add: Cash Receipts from In-Store Customer Sales \$ 12,200 \$ 11,300 \$ 8,100 \$ 14,200 Cash Receipts from Delivery Sales \$ 91,300 \$ 71,800 \$ 84,200 \$ 81,600 Cash Receipts from Services Rendered \$ 700 \$ 1,600 \$ 2,300 \$ 4,100 Interest Received \$ 650 \$ 650 \$ 600 \$ 1,300 Sale of Machinery \$ - 0 \$ 4,000 \$ - 0 \$ - 0 Total Cash Receipts: Total Cash Available Less: Cash Disbursements for Merchandise Purchases \$ 64,200 \$ 51,300 \$ 55,400 \$ 61,000 Cash Disbursements for Operating Expenses \$ 31,000 \$ 36,900 \$ 39,300 \$ 34,200 Loan Payments (Principal) \$ 2,400 \$ 2,400 \$ 2,400 \$ 2,400 Loan Payments (Interest) \$ 900 \$ 900 \$ 900 \$ 900 Total Cash Disbursements: Ending Cash Balance: Purchases Budget Dairy Producers, Inc. Purchases Budget For the Year Ending December 31, 2017 Budgeted Cost of Goods Sold \$ 237,000 Add: Desired Ending Inventory \$ 20,000 Total Inventory Required Less: Beginning Inventory \$ 25,100 Required Merchandise Purchases CVP Analysis #1 Dairy Producers, Inc. CVP Income Statement For the Year Ending December 31, 2017 Total Per Unit Sales Revenue \$ 80.00 Variable Costs \$ 58.00 Contribution Margin Fixed Costs \$ 104,500 Net Income Units Sold:

Place your order now for a similar assignment and have exceptional work written by one of our experts, guaranteeing you an A result.

#### Need an Essay Written?

This sample is available to anyone. If you want a unique paper order it from one of our professional writers.

Latest Questions

See More »

See More »

See More »

See More »

See More »

See More »