Excel Problems 2

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 $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 commissions equal 4% of sales for that month. Combine salaries and commissions into a single figure.

d. Other monthly expenses are as follows:

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 that %50 downward.) For example, budgeted May sales are $51,000 ($50,000 x 1.02), and June sales are $52,000 ($51,000 x 1.024)

Rent Expense$2600 paid as incurred

Depreciation Expense$300

Insurance Expense$200 expiration of prepaid amount

Income Tax20% of operating income