Using the following information you are to prepare a comprehensive budget for Ri
ID: 2787918 • Letter: U
Question
Using the following information you are to prepare a comprehensive budget for River City Micro Systems, Inc. The Company assembles a specialized device used in airports to detect certain types of explosives to prevent terrorist attacks. Arrangements have been made for the component parts (bundled in packets, one per unit) to be produced in Indonesia, shipped to Boise, then assembled and sold by River City Micro to the end users. You have developed the prototypes, established a market, and now you are putting together a budget for the first three months of 2017. The Company will actually start manufacturing and distribution on January 2, 2017. The purpose of this comprehensive budget is to formalize your expected income, cash flow and balance sheets. From the following information you are to prepare the following schedules/statements for the months ending January 31, 2017, February 28, 2017 and March 31, 2017:
1) Projected units of production
2) Projected raw material requirements
3) Projected raw material purchases in dollars
4) Projected cost of goods manufactured statement
5) Pro-forma income statement
6) Pro-forma cash flow statement
7) Pro-forma balance sheet
8) Capital lease amortization schedule
9) Depreciation schedule
You should utilize the following assumptions in making your calculations: Projected sales in units are as follows: January = 500, February = 600, March = 600, April and following months = 800. At the start of each month the management plans to have 30 days, (1 month) of direct materials on hand. Each packet of direct material costs $80.00. The company will have 800 units on hand on January 1, 2017 (all purchased during December 2016). Ten hours of direct labor are required to assemble each device. The direct labor cost (including fringe benefits) is $35.00 per hour. Manufacturing overhead is 50% of direct labor cost. Devices are sold at 100% markup on cost. The company wants to have at least 50% of next months projected sales in ending finished goods inventory each month. f. Direct materials purchases are paid for on the 10th day of the month following month of purchases. g. Manufacturing overhead is paid 25% in cash and with the balance paid in 30 days. h. Wages earned by employees during the first half of each month are paid on the 22nd with the remainder paid on the 7th of the following month. Assume that workforce is stable each month (hence, wages and salaries are the same every day of the month). i. On January 1, 2017 you acquire equipment and finance it 100% through a capital lease. Life of equipment is 60 months with no salvage value. Capital lease payments are $12,000 per month including an imputed interest component. Your cost of capital is 10%. Use this rate to calculate the present value of the cash payments and the present value of the lease principal as of January 1, 2017. The first payment is due on February 1, 2017. j. Selling commissions are 10% of sales price. These are paid on the 15th day of the month following month of sale. k. Administrative salaries and fringe benefits are $60,000 per month payable on schedule outlined in h. l. Rent is $8,000 per month payable on the first day of each month. m. On January 1, 2017 the Company will pay 6 months insurance premiums in advance for a total of$24,000. n. Other general and administrative expenses are estimated to be 15% of sales. They are paid in the month after they are incurred. o. The company has a $500,000 line of credit secured by inventory and accounts receivable. Borrowing against this line must be in increments of $50,000. Interest is 12% per annum and is payable on the 1st day of the month following the borrowing. Assume all borrowing occur on the 15th day of the month. Repayments must also occur in $50,000 increments on the 15th day of the month. p. All sales are on account and are collected 15% in month of sale, 75% in next month and the balance in the following month. q. Income tax rate is 35%. Taxes accrue on each month’s income and are paid in arrears on January 15, Apr 15, Jul 15 and Oct 15 for the preceding quarter. Note: any expected losses create tax benefits that can be used in reduce taxes paid in future quarters. r. Beginning cash balance on January 1, 2017 is projected to be $100,000 that was raised through the sale of capital stock in December 2016.
Some helpful check figures and information that were provided :
These relate to the production, purchases and cost of goods manufactured budgets
Required production in units for Jan. 800
Required purchases of raw materials in Jan 600 or $48,000
Total cost of goods manufactured in Jan $484,000 or $605 per unit
You need to borrow enough to have sufficient cash on hand for the next 10 days of the following month. You do not need to repay the loan until you have extra cash to do so. Most students make a mistake in not borrowing enough money. I think that is because from the cradle we are taught that interest is bad. It is better to borrow more and not run out. You can only borrow once a month so you need to plan ahead.
Interest expense for the bank loan and capital lease need to be accrued on the previous month. For example you do not pay any interest until Feb but you need to accrue it on the IS and BS in Jan
January figures:
Sales $605,000
Gross Margin $302,500
Operating Expenses $232,663
Operating Income $69,837
Interest expense and income taxes will vary by group depending on how much you borrow.
Cash Flow Statement:
Cash Receipts $90,750 (excludes any borrowings)
Cash Disbursements $301,000
Selected Balance Sheet Figures for January 31
Accounts Receivable $514,250
Raw Materials Inventory $48,000
Finished Goods Inventory $181,500
Prepaid Insurance $20,000
Accounts Payable $243,750
Wages Payable $230,500
Common Stock $100,000
These were numbers provides to make sure that it is on track and the information is accurate.
Preliminary budget sheet work book
Budgeted income statement workbook
$-
Budgeted cash flow work book
Budgeted balance sheet workbook
SALES FORECAST Jan Feb Mar Apr May June in Units PRODUCTION BUDGET Sales EI -BI Production - - - - RAW MATERIAL PURCHASES (packets--1 per unit) Production EI BI Purchases - - - - Purchases in $ (@$80) - - - COST OF GOODS MANUFACTURED BUDGET Production Direct Material (@$80) - - Direct Labor (10 hrs @$35/hr) - - - Overhead (50% DL$) - - - Total CGM - - - Average Cost/Unit #DIV/0! #DIV/0! #DIV/0! Projected Sellling Price #DIV/0! #DIV/0! #DIV/0!Budgeted income statement workbook
January February March Sales in Units Sales (@$1210) $- $- $- Cost of Sales (@605) - - - Gross Margin - - - Operating Expenses: Administrative salaries (given) Sales commissions (10% of sales) Rent (given) Insurance ($24,000 ÷ 6) Other general and administrative (15% of sales) Depreciation ($564,180 ÷ 60) Totals - - - Operating Income (Loss) - - - Inrterest Expense Capital Lease Operating Line Income (Loss) Before Taxes - - - Estimated Income Taxes Net Income (Loss) $- $-$-
Explanation / Answer
Preliminary budget sheet work book 1. Production & Sales: SALES FORECAST Jan Feb Mar Apr in Units 500 600 600 800 PRODUCTION BUDGET Sales 500 600 600 800 EI 300 300 400 400 -BI 0 300 300 400 Production 800 600 700 800 2. RAW MATERIAL PURCHASES (packets--1 per unit) Production 800 600 700 800 EI 600 700 800 BI 800 600 700 Purchases 600 700 800 - 3. Purchases in $ (@$80) 48000 56000 64000 4. COST OF GOODS MANUFACTURED BUDGET Production 800 350 700 Direct Material (@$80) 64000 28000 56000 Direct Labor (10 hrs @$35/hr) 280000 122500 245000 Overhead (50% DL$) 140000 61250 122500 Total CGM 484000 211750 423500 Average Cost/Unit 605 605 605 Projected Sellling Price(100% mark-up) 1210 1210 1210 5. Budgeted income statement workbook January February March Sales in Units 500 600 600 Sales (@$1210) 605000 726000 726000 Cost of Sales (@605) 302500 363000 363000 Gross Margin 302500 363000 363000 Operating Expenses: Administrative salaries (given) 60000 60000 60000 Sales commissions (10% of sales) 60500 72600 72600 Rent (given) 8000 8000 8000 Insurance ($24,000 ÷ 6) 4000 4000 4000 Other general and administrative (15% of sales) 90750 108900 108900 Depreciation ($564,180 ÷ 60) 9403 9403 9403 Totals 232653 262903 262903 - Operating Income (Loss) 69847 100097 100097 - Interest Expense Capital Lease 4702 4641 4579 Operating Line 750 1500 1000 Income (Loss) Before Taxes 64396 93956 94518 - Estimated Income Taxes 22538 32885 33081 Net Income (Loss) 41857 61072 61436 $- 8. Capital lease amortisation-- 1st 3 months Months Annuity Tow. Int. Tow.Principal Prin. Bal. 1-Jan 564180 31-Jan 12000 4702 7299 556882 Feb 12000 4641 7359 549522 Mar 12000 4579 7421 542102 6. Budgeted cash flow work book Carried to B/S figures Carried to B/S figures Carried to B/S figures Cash Forecast Cash Receipts: January February March Sales (from I/S) collection in month (15%) 90750 108900 108900 collection in 2nd month (75%) 453750 544500 collection in 3rd month (10%) 60500 Total Cash Receipts 90750 514250 562650 677600 713900 689700 Cash Disbursements: Direct Labor Costs Incurred (production) paid in mo (50%) 140000 140000 61250 61250 122500 122500 paid in 2nd mo (50%) 140000 61250 Total direct labor cash payments 140000 201250 183750 Administrative Salaries (Expense) paid in mo (50%) 30000 30000 30000 30000 30000 30000 paid in 2nd mo (50%) 30000 30000 Total adminstrative salary payments 30000 60000 60000 Sales commissions (Expense) 60500 72600 72600 paid in full in following mo 60500 72600 Material purchases (Procurement) paid in full in following month 64000 48000 48000 56000 56000 64000 MOH paid in month (25%) 35000 105000 15312.5 45937.5 30625 91875 paid in 2nd mo (75%) 105000 45937.5 Total MOH 35000 120312.5 76562.5 Other administrative costs (Expense) paid in full in following month 90750 90750 108900 108900 108900 Insurance 24000 20000 16000 12000 Rent 8000 8000 8000 Capital Lease Principal 7299 7359 7421 Interest expense 564180 556881 549522 on capital lease (per schedule) 4702 4702 4641 4641 4579 on bank loan* 750 750 1500 1500 1000 Total interest 5452 6141 Total disbursements 301000 601564 579313 Cash Receipts Less Cash Disbursements -210250 -38913.5 134588 Beginning Balance 100000 39750 837 Cash Available -110250 837 135424 Borrowings 150000 100000 Ending Cash Balance 39750 837 35424 7. Budgeted balance sheet workbook Assets 1-Jan 31-Jan 28-Feb 31-Mar Current Assets: Cash 76000 39750 837 35424 Accounts Receivable 514250 677600 689700 Raw Material Inventory 64000 48000 56000 64000 Finished Goods Inventory 181500 181500 242000 Prepaid Insurance 24000 20000 16000 12000 Total Current Assets 164000 803500 931937 1043124 Property and Equipment: Equipment on Capital Lease 564180 564180 564180 564180 Accumulated Depreciation 0 9403 18806 28209 Net Property and Equipment 564180 554777 545374 535971 Total Assets 728180 1358277 1477311 1579095 Liabilities and Stockholders' Equity Current Liabilities: Accounts Payable 64000 243750 210838 264775 Wages Payable 230500 163850 225100 Bank Note Payable 150000 150000 50000 Interest Payable(Bank note+ Lease) 5452 6141 5579 Income Taxes Payable 22538 55423 88504 Capital Lease Payable-Current Portion 7298 7359 7421 Total Current Liabilities 64000 659538 593611 641379 Capital Lease - Amount Due After One Year 564180 556882 549522 542102 Stockholders' Equity: Common Stock 100000 100000 100000 100000 Retained Earnings (Deficit) 41857 102929 164365 Total Stockholders' Equity 100000 141857 202929 264365 728180 1358277 1346062 1447846 0 -131249 -131249
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.