Case Study Ch. 10

profilestudent747
Ch.10CaseStudy.xlsx
Home>Business & Finance homework help>Accounting homework help>Case Study Ch. 10

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: