Excel Problems P21-20ADoggy world operatesP21-21AThe budget committee of Clipboard Office SupplyP21-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-20A Doggy 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 WORLDIncome statementFor 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 WORLDIncome statementFor 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-21A The 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 arerent $2600 paid as incurred depreciation $300 insurance $200 expiration of prepaid amount income tax 20% of operating income. P21-22A Clipboard 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 $25000accounts payable $53000Salaries 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?