Responsibility Report, Budget Statement, Cash Budget

Management Accounting

M4A3

Assignment 3: Excel Problems

At the end of each module, you will apply the module’s concepts by completing comprehensive assignments from the textbook.

Complete problems P21-20A (p. 1121), P21-21A (p. 1122), and P21-22A (p.1123) in your textbook.

Present your analysis of the assigned problems in Excel format. Enter non-numerical responses in the same worksheet using textboxes.

If you need assistance with using Microsoft Excel, refer to the Microsoft Excel tutorials.

By Tuesday, November 4, 2014 deliver your assignment to the M4: Assignment 3 Dropbox. Create the file with the following name: LastnameFirstInitial_M4A3.Excel.xls.

Assignment 3 Grading Criteria	Maximum Points
P20-21A:
Prepared report for July 2011 which shows the performance of the Dayton store, the Ohio region and the company	10
Explained and justified if would investigate the Dayton store on the basis of the report	5
Explained if Doggy World should prepare a master budget and discussed the benefits of budgeting	5
P21-21A:
Prepared Clipboard Office Supply’s budgeted income statement for May and June	10
P21-22A:
Prepared schedules of budgeted cash collections, budgeted cash payments for purchases and budgeted cash payments for operating expenses	10
Prepared a cash budget and identified the cash balance as of June 30, 2011	10
Total:	50

P21-20A. Why managers use budgets, understanding the components of the master budget, and preparing performance reports for responsibility centers.

Doggy World operates a chain of pet stores in the Midwest. The manager of each store reports to the regional manager, who, in turn, reports to the headquarters in Milwaukee, Wisconsin. The actual income statements for the Dayton store, the Ohio region (including the Dayton store), and the company as a whole (including the Ohio region) for July 2011 are as follows.

Requirements for P21-20A

1. Prepare a report for July 2011 that shows the performance of the Dayton store, the Ohio region, and the company as a whole. Follow the format of Exhibit 21-20.

2. As the Ohio regional manager, would you investigate the Dayton store on the basis of this report? Why or why not?

3. Should Doggy World prepare the master budget? Briefly discuss the benefits of budgeting. Base your discussion on Doggy World’s performance report.

Exhibit 21-20

CEO’S QUARTERLY RESPONSIBILITY REPORT (in millions of dollars)
Operating income of Division and Corporate Headquarters Expense	Budget	Actual	Variance Favorable/Unfavorable
Downloads	218	209	(9)
Media	70	84	14
Other	79	87	8
International Media	35	34	(1)
Corporate Headquarters Expense	(33)	(29)	4
Operating Income	369	385	16

VP-MEDIA QUARTERLY RESPONSIBILITY REPORT (in millions of dollars)
Operating Income of Product Lines	Budget	Actual	Variance Favorable/Unfavorable
CD’s	25	38	13
DVD’s	45	46	1
Operating Income	70	84	14

MANAGER –CD’S QUARTERLY RESPONSIBILITY REPORT (in millions of dollars)
Revenue and Expenses	Budget	Actual	Variance Favorable/Unfavorable
Sales Revenue	80	84	4
Cost of goods sold	(36)	(30)	6
Gross Profit	44	54	10
Marketing expenses	(12)	(9)	3
Research and development expenses	(2)	(3)	(1)
Other expenses	(5)	(4)	1

Operating income	25	38	13

P21-21A. Preparing an operating budget

The budget committee of Clipboard Office Supply has assembled the following data. As the business manager, you must prepare the budgeted income statement for May and June 2011.

Requirement for P21-21A

a. Sales in April were $50,000. You forecast that monthly sales will increase 2.0% in May and 2.4% in June.

b. Clipboard maintains inventory on $9,000 plus 25% of the sales revenue budgeted for the following month. Monthly purchases average 50% of sales revenue in that same month. Actual inventory on April 30 is $13,000. Sales budgeted for July are $65,000.

c. Monthly salaries amount to $3,000. Sales commission equal 4% of sales for that month. Combine salaries and commissions into a single figure.

d. Other monthly expenses are as follows:

Rent expense	$2,600 paid as incurred
Depreciation expense	$300
Insurance expense	$200 expiration of prepaid amount
Income tax	20% of operating income

1. Prepare Clipboard Office Supply’s budgeted income statements for May and June. Show cost of goods sold computations. Round all amounts to the nearest $100. (Round amounts ending in $50 or more upward and amounts ending in less than $50 downward). For example, budgeted May sales are $51,000 ($50,000 x 1.02), and June sales are $52,200 ($51,000 x 1.024).

P21-22A. Preparing a financial budget

Refer to P21-21A. Clipboard Office Supply sales are 75% cash and 25% credit. (Use the rounded sales values). Credit sales are collected in the month after sale. Inventory purchases are paid 25% in the month of purchase and 75% the following month. Salaries and sales commissions are also paid at the end of the year. The April 30, 2011, balnce sheet showed the following balances:

Cash…………………………………...$25,000

Accounts payable……………………...$53,000

Salaries and commissions payable……. $2,500

Requirements for P21-22A

1. Prepare schedules of (a) budgeted cash collections, (b) budgeted cash payments for purchases, and (c) budgeted cash payments for operating expenses. Show amounts for each month and totals for Many and June. Round your computations to the nearest dollar.

2. Prepare a cash budget similar to Exhibit 21-14. If no financing activity took place, what is the budgeted cash balance on June 30, 2011?

Exhibit 21-14

GREG’S GROOVY TUNES INC. Cash Budget For Months Ending July 31, 2010
	April	May	June	July
Beginning cash balance	15,000	10,550	10,410	18,235
Cash collections	46,000	68,000	68,000	54,000
Cash available	61,000	78,550	78,410	72,235
Cash payments
Purchase of inventory	42,700	48,300	40,600	32,900
Operating expenses	13,750	18,250	18,000	15,250
Purchase of delivery truck	3,000
Total cash payments	59,450	66,550	58,600	48,150
(1) Ending cash balance before financing	1,550	12,000	19,810	24,085
Less Minimum cash balance desired	(10,000)	(10,000)	(10,000)	(10,000)
Cash excess (deficiency)	(8,450)	2,000	9,810	14,085
Financing of cash deficiency
Borrowing (at end of month)	9,000
Principal payments (at end of month)		(1,500)	(1,500)	(1,500)
Interest expense (at 12% annually)		(90)	(75)	(60)
(2) Total effects of financing	9,000	(1,590)	(1,575)	(1,560)
Ending cash balance (1) + (2)	10,550	10,410	18,235	22,525

10 years ago

Report issue

Responsibility Report, Budget Statement, Cash Budget A+ Tutorial use as Guide

NOT RATED

Purchase the answer to view it

responsibility_report_budget_statement_cash_budget.docx