make a Selling and Admin budget. show all multiplication/divsion formulas on how you got answers pls.
make a Selling and Admin budget. show all multiplication/divsion formulas on how you got answers pls.
EXCEL PROJECT ACCT2102 SPRING 2021 DUE FRIDAY APR 23, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Horsexcom Excel Project DROPBOX IN FOLIO. Information for Horsex Com Budgets. Horsexcom produces horse harnesses. The harnesses sales for the next 5 months are projected to be: January 15,000 February 25,000 March 24,000 April 21,000 May 26,000 The following information pertains to the budget assumptions. a. Finished goods inventory on January 1 is expected to be 5000 units. The desired ending FGI for any month (except for the Dec 31 inventory as noted previously) is expected to be 20% of the following month's sales. b. Data for materials used are: Part A87 3 parts per harness $35.50 per part Part D33 2 parts per harness $15.00 per part Raw materials ending inventory is always budgeted to equal 20% of the following month's production needs. On March 31, the ending inventory of A87 is expected to be 18,000 parts and for D33 is expected to be 12,000 parts. C. Direct labor used per harness: Labor type hours per harness wage rate Leather worker 2.5 hours per harness $15 per hour. Assembler worker 2 hours per harness $10 per hour. d. Overhead each month is estimated at: Overhead estimates Fixed portion (per month) Supplies Power Maintenance 12500 Supervision 14000 Depreciation 45000 Taxes 4300 Other 86000 variable cost ($ per DLH (cost dri' 1 0.2 1.1 1.6 e. Selling and Admin is estimated each to be: Selling and admin per month variable cost ($ per unit sold (cost driver)) Fixed portion (per month) 30000 1.4 Salaries Commissions Depreciation Shipping Other 5000 2.6 0.6 13000 f. Selling price per harness is $330.00. g. Sales are all on account. 35% of sales are collected in the month of sale and 65% in the month following the sale. Accounts receivable on January 1 is $150,000. h. March is a planned purchase of equipment for $250,000. i. Borrowings are at 6% per year interest, and borrowings are assumed to be at the beginning of the month required and at the end of the month of repayment. January 1 beginning cash is $20,000. Interest on borrowings is paid before any principal can be paid. To the extent possible, the company strives to repay any debt at the end of each month if there is cash available. Minimum cash balance at the end of any month is $10,000. SALES INFORMATION Projected sales in units January February March April May 15000 25000 24000 21000 26000 5000 units Finished Goods Inventory predictions Finished good inventory, Jan 1 Finished Goods Inventory policy Desired ending FGI 20% percent of next months sales volume PRODUCTION INFORMATION Direct labor two types Leather worker each harness takes wage rate for direct laboris 2.5 hours of direct labor 15 dollars per hour Assembler each harness takes wag rate for Assembler 2 hour of direct labor 10 dollars per hour Overhead estimates Fixed portion (per month) variable cost ($ per DLH (sum of Leather worker plus Assembler)) 1 0.2 1.1 Supplies Power Maintenance Supervision Depreciation Taxes Other 12500 14000 45000 4300 86000 1.6 Direct materials usage-two types quantity per unit of output (per harness) 3 A87 D33 price per quantity unit 35.5 dollars 15 dollars 2 Raw Materials inventory policy Desired ending of the month RMI Assume beginning RMI (on Jan 1) is A87 20% percent of the next month's production needs 18000 12000 D33 BANKING INFORMATION Borrowings are assumed to occur at the beginning of the month Repayments are assumed to occur at the end of the month. When the company has enough cash on hand at the end of the month, it repays the interest first, then any principal to the extent possible. Interest rate is 6% percent per year Cash Balance at January 1 $20,000 dollars Minimum cash balance each month end is $10,000 dollars 330 dollars ADDITIONAL INFORMATION Selling price of each harness (finished product outptut) Collections predition collected in month of sale 35% of current month sales collected in subsequent month 65% of prior month sales Accounts receivable as at January 1 $150,000 Selling and admin per month Fixed portion (per month) 30000 variable cost ($ per unit sold (cost driver)) 1.4 Salaries Commissions Depreciation Shipping Other 5000 2.6 0.6 13000 March purchase land for 250,000 dollars
