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

QUESTION

Excel
Problems
P21-20ADoggy
world operates
P21-21AThe budget committee of Clipboard Office Supply
P21-22AClipboard Office Supply’s sales are 75%

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.

Attached are
the case studies and its working spread sheets that goes together.

P21-20ADoggy
world operates a chain of pair store in the Midwest. The manager of each store
reports to the regional manager, who, in turn, reports to the headquarters in
the Milwaukee, Wisconsin. The actual income statements for the Dayton store,
the Ohio region (include the Dayton store), and the company as a whole
(including the Ohio region) for July 2011 are as follows:

DOGGY WORLD
Income statement
For the month ended
July 31, 2011

Dayton

Ohio

Companywide

Revenue

$158,400

$1,760,000

$4,400,000

Expense:

Regional manager/headquarters
office

$ —-

$58,000

$122,000

Cost of materials

85,536

880,000

1,760,000

Salary expense

41,184

440,000

1,100,000

Depreciation expense

7,800

91,000

439,000

Utility expense

4,000

46,600

264,000

Rent expense

2,500

34,500

178,000

Total expenses

141,020

1,550,100

3,863,000

Operating income

$17,380

$209,900

$537,000

Budgeted amounts for July were as
follows:

DOGGY WORLD
Income statement
For the month ended
July 31, 2011

Dayton

Ohio

Companywide

Revenue

173,400

1,883,000

4,650,000

Expense:

Regional manager/headquarters
office

—-

64,600

124,000

Cost of materials

91,902

1,035,650

2,092,500

Salary expense

41,616

470,750

1,162,500

Depreciation expense

7,800

87,500

446,000

Utility expense

4,900

54,600

274,000

Rent expense

3,400

32,700

169,000

Total expenses

149,618

1,745,800

4,268,000

Operating income

23,782

137,200

382,000

Requirement
Prepare a report for July 2011
that shows the performance of the Dayton store, the Ohio region, and the
company as a whole.

P21-21AThe budget committee of Clipboard Office Supply has assembled
the following data. As the business manager, you must prepare the budgeted
income statements for May and June 2011.

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 of $9000 plus 25% of the sales revenue budgeted for the
following month. Monthly purcahses average 50% of sales revenue for the same
month. Actual inventory on April 30th was $13000. Sales budgeted for July are
$65000
c monthly salaries
amount to $3000. Sales commissions equal 4% of sales for that month. Combine
salaries and commissions into a single figure.
d. other monthly
expenses are
rent $2600 paid as
incurred
depreciation $300
insurance $200
expiration of prepaid amount
income tax 20% of
operating income.

P21-22AClipboard Office Supply’s 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 half in the month
earned and half the next month. Income tax is paid at the end of the year. The
April 30, 2011, balance sheet showed the following balances:
cash $25000
accounts payable
$53000
Salaries and commissions
payable $2500

Requirements
R1. 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 May and June. Round your computations to
the nearest dollar.

Prepare a cash
budget. If no financing activity took place, what is the budgeted cash balance
on June 30, 2011?

ANSWER:

REQUEST HELP FROM A TUTOR