Question

Chapter 7 Case        
Brewer, Garrison, Noreen        
Introduction to Managerial Accounting, 7th Edition        
- Refer to the end-of-chapter case for Chapter 7 (book pages 344-346)        
- Use templates provided in this workbook to complete requirements #1 - #4. Follow instructions on templates.        
- Save Excel file and print out entire workbook incl. Instructions tab (worksheets are formatted for printing).         
- Submit budget hard copy and upload Excel file to Titanium.         
- Team members must sign Instructions page to receive credit for the assignment.        

Data and Assumptions
Enter data and assumptions per case study
	Actual	Actual	Actual
	January	February	March	April	May	June	July	August	September
Budgeted unit sales (actual sales for Jan - Mar)	20,000	26,000	40,000	65,000	100,000	50,000	30,000	28,000	25,000
• Selling price	$10.00	per unit (pair of earrings)
• Sales collected in the month sales are made	20%
• Sales collected in the month after sales are made	70%
• Sales collected in the second month after sales are made	10%
• Desired ending merchandise inventory	40%	of the budgeted unit sales of the next month
• Inventory costs	$4.00	per unit (pair of earrings)
• Inventory purchases are paid	50%	in the month the purchases are made
and	50%	in the month following purchase
Monthly operating expenses:
• Sales commissions	4%	of sales
• Advertising	$200,000
• Rent	$18,000
• Salaries	$106,000
• Utilities	$7,000
• Insurance	$36,000	paid annually in November
• Depreciation	$14,000
Budgeted equipment purchases (paid in cash)	May	June
	$16,000	$40,000
Budgeted dividends	$15,000	declared each quarter, payable in the first month of the following quarter
Company's general ledger balances as of March 31
Assets
Cash	$     74,000
Accounts receivable	346,000
Inventory	104,000
Prepaid insurance	21,000
Property and equipment (net)	950,000
Total assets	$1,495,000
Liabilities and Stockholders' Equity
Accounts payable	$   100,000
Dividends payable	15,000
Common stock	800,000
Retained earnings	580,000
Total liabilities and stockholders' equity	$1,495,000					-	check (must be zero)
Minimum cash balance
Monthly interest rate on loans
# 1: Budget Schedules
Enter a formula or link directly to the assumptions section. Leave cell blank if amount should be zero.
1a: Sales budget
	April	May	June	Quarter
Budgeted unit sales	65,000	100,000	50,000	215,000
Selling price per unit	$10	$10	$10	$10
Total sales	$650,000	$1,000,000	$500,000	$2,150,000		$0	check (must be zero)
1b: Schedule of expected cash collections
	April	May	June	Quarter
February sales	$     26,000
March sales	$     80,000
April sales	130,000
May sales		200,000
June sales
Total cash collections	$   236,000	$   200,000	$            -	$            -		(436,000.00)	check (must be zero)
1c: Merchandise purchases budget
	April	May	June	Quarter
Budgeted unit sales
Add desired ending merchandise inventory
Total needs	-	-	-	-
Less beginning merchandise inventory
Required purchases (units)	-	-	-	-		-	check (must be zero)
Cost per unit
Cost of purchases	$            -	$            -	$            -	$            -		-	check (must be zero)
1d: Budgeted cash disbursements for merchandise purchases
	April	May	June	Quarter
Accounts payable, beginning balance
April purchases
May purchases
June purchases
Total cash disbursements	$            -	$            -	$            -	$            -		-	check (must be zero)

#2: Cash Budget
Select row title from drop down menu. Be sure to scroll all the way down on drop down menu: 10 options are given, but only 8 apply to the cash disbursements section.
Enter a formula or link directly to a cell in the assumptions or budgets section. Leave cell blank if amount should be zero.
Enter amount directly.
Beg. cash balance, collections, disbursements, and borrowings should be shown as positive numbers.
Repayments and interest should be shown as negative numbers.
Earrings Unlimited
Cash Budget
For the Three Months Ending June 30
	April	May	June	Quarter
Beginning cash balance
Add collections from customers
Total cash available	-	-	-	-
Less cash disbursements:
Total cash disbursements	-	-	-	-		-
Excess (deficiency) of cash available over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing	-	-	-	-
Ending cash balance	$              -	$              -	$              -	$              -
Interest calculation	April	May	June	Total Interest
Amount borrowed
Monthly interest rate
# of months loan outstanding
Interest	$              -	$              -	$              -	$              -

#3: Budgeted Income Statement
Enter row titles from drop down menu.
Enter a formula or link directly to a cell in the assumptions or budgets section.
All amounts (sales revenue and expenses) should be shown as positive numbers.
Earrings Unlimited
Budgeted Income Statement
For the Three Months Ended June 30
Sales
Variable expenses:
			-
Contribution margin			-
Fixed expenses:
			-
Net operating income			-
Interest expense
Net income			$             -

#4: Budgeted Balance Sheet
Enter a formula or link directly to a cell in the assumptions or budgets section.
All amounts should be shown as positive numbers.
Earrings Unlimited
Budgeted Balance Sheet
June 30
Assets
Cash
Accounts receivable (see calculation below)
Inventory
Prepaid insurance
Property and equipment, net
Total assets	$                   -
Liabilities and Stockholders’ Equity
Accounts payable	$                   -
Dividends payable	15,000
Common stock	800,000
Retained earnings (see calculation below)	565,000
Total liabilities and stockholders’ equity	$        1,380,000
Calculations:
Accounts receivable at June 30:
Related to May sales	$           100,000
Related to June sales	400,000
Total	$           500,000
Retained earnings at June 30:
Balance, March 31	$           580,000
Add net income	-
Total	580,000
Less dividends declared	15,000
Balance, June 30	$           565,000

(PLEASE SOLVE IT WITH FORMULAR)

PLEASE SOLVE THIS QUESTION THROUGH EXCEL AND SEND THOSE EXCEL FILE TO MY EMAIL.

THANK YOU,

[email protected]

Answer 1

		Earrings Unlimited
a	Sales budget
		April	May	June	Total
	Sales (units)	65,000	100,000	50,000	215,000
	Sales Price Per Unit	10	10	10	10
	Sales (dollars)	$                                             650,000	$                               1,000,000	$                                    500,000	$ 2,150,000
b	Cash collections from sales
		April	May	June	Total
	February sales	$                                               26,000			$        26,000
	March sales	$                                             280,000	$                                     40,000		$      320,000
	April sales	$                                             130,000	$                                  455,000	$                                      65,000	$      650,000
	May sales		$                                  200,000	$                                    700,000	$      900,000
	June sales			$                                    100,000	$      100,000
	Total collections	$                                             436,000	$                                  695,000	$                                    865,000	$ 1,996,000
		April	May	June
	February Sales	($260000*10%)=$26000
	March Sales	($400000*70%)=$280000	($400000*10%)=$40000
	April Sales	($650000*20%)=$130000	($650000*70%)=$455000	($650000*10%)=$65000
	May Sales		($1000000*20%)=$200000	($1000000(70%)=$700000
	June Sales			($500000*20%)=$100000
c	Merchandise purchase budget
		April	May	June	Total
	In units
	Desired ending inventory=(A)	40,000	20,000	12,000	72,000
	Add: Sales during the month=(B)	65,000	100,000	50,000	215,000
	Less: Opening inventory=(C )	(26,000)	(40,000)	(20,000)	(86,000)
	Purchases=(A)+(B)-(C )	79,000	80,000	42,000	201,000
	Purchases (in dollars) =(Purchases*@ $4 per unit)	$                                             316,000	$                                  320,000	$                                    168,000	$      804,000
		April	May	June
	In units
	Desired ending inventory	(100000*40%)=40000	(50000*40%)=20000	(30000*40%)=12000
	Beginning Inventory of April
	Inventory as per Balance sheet	$                                       104,000.00
	Inventory cost	$                                                    4.00	Per Unit
	Beginning Inventory of Apri(Units)	($104000/$4)=26000
d	Cash disbursement for purchases
		April	May	June	Total
	March purchases as per balance sheet	$                                             100,000			$      100,000
	April purchases=$316000 amt collected 50% in April and 50% in May	$                                             158,000	$                                  158,000		$      316,000
	May purchases=$320000=50% in May and 50% in June		$                                  160,000	$                                    160,000	$      320,000
	June purchases=$168000 =50% in June			$                                      84,000	$        84,000
	Total disbursements	$                                             258,000	$                                  318,000	$                                    244,000	$      820,000
	Cash budget
		April	May	June	Total
	Beginning cash balance	$                                               74,000	$                                     50,000	$                                      50,000	$        74,000
	Receipts from customers	$                                             436,000	$                                  695,000	$                                    865,000	$ 1,996,000
	Total cash	$                                             510,000	$                                  745,000	$                                    915,000	$ 2,070,000
	Cash disbursements:
	To vendors for purchases	$                                             258,000	$                                  318,000	$                                    244,000	$      820,000
	Sales commissions=(Sales*4%)	$                                               26,000	$                                     40,000	$                                      20,000	$        86,000
	Advertising	$                                             200,000	$                                  200,000	$                                    200,000	$      600,000
	Rent	$                                               18,000	$                                     18,000	$                                      18,000	$        54,000
	Salaries	$                                             106,000	$                                  106,000	$                                    106,000	$      318,000
	Utilities	$                                                  7,000	$                                       7,000	$                                        7,000	$        21,000
	Purchase of new equipment	$                                                         -	$                                     16,000	$                                      40,000	$        56,000
	Payment of dividends	$                                               15,000	$                                              -	$                                               -	$        15,000
	Interest on borrowings	$                                                         -	$                                              -	$                                        5,300	$          5,300
	Total disbursements	$                                             630,000	$                                  705,000	$                                    640,300	$ 1,975,300
	Excess (Deficiency) of Cash Available	$                                           (120,000)	$                                     40,000	$                                    274,700	$        94,700
	Financing:
	Cash Borrowed	$                                             170,000	$                                     10,000	$                                               -	$      180,000
	Loan Repayments	$                                                         -	$                                              -	$                                    180,000	$      180,000
	Loan Balance	$                                             170,000	$                                  180,000	$                                               -	$                 -
	Cash Balance at End of Month	$                                               50,000	$                                     50,000	$                                      94,700	$        94,700
	Interest	($170000*1%*3+$10000*1%*2)=	$                                       5,300
		EARRINGS UNLIMITED
		Budgeted Income Statement for the period ended June 30
	Sales		$                               2,150,000
	Less: Variable expenses
	Beginning inventory	$                                             104,000
	Add: Purchases	$                                             804,000
	Less: Ending inventory=(12000*$4)	$                                             (48,000)
	Cost of goods sold		$                                  860,000
	Sales commission=($2150000*4%)		$                                     86,000
	Contribution margin=(A)		$                               1,204,000
	Less: Operating expenses
	Advertising	$                                             600,000
	Rent	$                                               54,000
	Salaries	$                                             318,000
	Utilities	$                                               21,000
	Insurance	$                                                  9,000
	Depreciation=($14000*3)	$                                               42,000
	Interest expense	$                                                  5,300
	Total operating expenses=(B)		$                               1,049,300
	Net income=(A)-(B)		$                                  154,700
	EARRINGS UNLIMITED
	Budgeted Balance Sheet as at June 30th
	Assets
	Cash	$                                               94,700
	Accounts receivable (May sales $1000,000*10% ; June sales $500,000*80%)	$                                             500,000
	Inventory=(12000*$4)	$                                               48,000
	Prepaid insurance=(Four months from July to October)=($36000*4/12)	$                                               12,000
	Property, plant and equipment (net)=($95000+$16000+$40000-$42000)	$                                             964,000
	Total assets	$                                         1,618,700
	Liabilities and stockholder's equity
	Accounts payable(June Purchase*50%)	$                                               84,000
	Dividends payable	$                                               15,000
	Capital stock	$                                             800,000
	Retained earnings=($580000+$154700-$15000)	$                                             719,700
	Total liabilities and stockholder's equity	$                                         1,618,700