My part to an accounting group project

profilescotla
input_example.xlsx
Home>Business & Finance homework help>Accounting homework help>My part to an accounting group project

inputs

Schedule 1: Sales Budget
April May June July August
Sales in units 20,000 50,000 30,000 25,000 15,000
Sales price per unit $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00
Schedule 2: Cash receipts
1. All sales are on account.
2. The accounts receivable balance on March 31 was $30,000. All of this balance was collectible.
3. The company collects 70% of these credit sales in the month of the sale;
25% are collected in the month following sale; and the remaining 5% are uncollectible.
Cash in initial month 70%
Cash in subsequent month 25%
Uncollectible 5%
Schedule 3: Production Budget in Units
1. The company desires to have inventory on hand at the end of each month equal to
20% of the following month’s budgeted unit sales.
2. On March 31, 4,000 units were on hand.
EI as % of next month sales 20%
Schedule 4: Direct Materials Purchases
1. Five lbs. of material are required per unit of product
2. The material costs $0.40 per lb.
3. Management desires to have materials on hand at the end of each month equal to
10% of the following month’s production needs.
4. The beginning materials inventory was 13,000 lbs.
Each unit requires in lbs 5
cost per lb $ 0.40
Beg Inv RM in lbs 13,000
EI RM of % next month 10%
Schedule 5: Cash Purchases Budget
1. Half of a month’s purchases are paid for in the month of purchase;
the other half is paid for in the following month.
2. There are no discounts for early payment.
3. The accounts payable balance on March 31 was $12,000.
They are all collected in April.
Purchase on Credit 50%
Purchase on Cash 50%
Schedule 6: Direct Labor Budget
Each unit produced requires 0.05 hour of direct labor.
Each hour of direct labor costs the company $10.
Management fully adjusts the workforce to the workload each month.
DLH per unit 0.05
cost per DLH $ 10.00
Schedule 7: Overhead Budget
1. Variable manufacturing overhead is $20 per direct labor hour.
2. Fixed manufacturing overhead is $50,500 per month.
This includes $20,500 in depreciation, which is not a cash outflow.
Var MO per DLH $ 20.00
Fixed MO per month $ 50,500
Depr in Fixed MO per month $ 20,500
Schedule 8: Ending Finished goods
1. Royal Company uses absorption costing in its budgeted income statement and balance sheet.
2. Manufacturing overhead is applied to units of product on the basis of direct labor hours.
3. The company has no work in process inventories.
Schedule 9: Selling and Administrative Expense Budget
Variable selling and administrative expenses are $0.50 per unit sold.
Fixed selling and administrative expenses are $70,000 per month and include $10,000 in depreciation.
Var Selling per unit $ 0.50
Fixed selling per month $ 70,000
Depr incl in fixed selling per month 10,000 $ 10,000
Schedule 10: Cash Budget
1. A line of credit is available at a local bank, which allows the company to borrow up to $75,000.
a. All borrowing occurs at the beginning of the month,
and all repayments occur at the end of the month.
b. Any interest incurred during the second quarter will be paid at the end of the quarter
The interest rate is 16% per year.
2. Royal Company desires a cash balance of at least $30,000 at the end of each month.
The cash balance at the beginning of April was $40,000.
3. Cash dividends of $51,000 are to be paid to shareholders in April.
Equipment purchases of $143,700 are scheduled for May and $48,800 for June.
4. This equipment will be installed and tested during the second quarter and will not
become operational until July, when depreciation charges will commence.
Interest rate per year 16%
Interest rate per quarter 4%
Minimum Cash $ 30,000
Dividends:
April $ 51,000
May $ - 0
June $ - 0
Equipment purchases
April $ - 0
May $ 143,700
June $ 48,800