60.1K

Verified Solution

Link Copied!

Using the following information you are to prepare a comprehensive budget for Trail Tracker, Inc. The Company assembles a GPS System that is attached to trail signs in outdoor areas such as state and federal parks. In case of an emergency backpackers, hikers, and other outdoor users can use Trail Tracker to notify authorities in case of an emergency and it will transmit coordinates.

A key electronic component (referred to as electronic packet) of the system is built by independent contractors in small Idaho towns before being delivered to the company in Boise. Each Trail Tracker GPS System uses three electronic packets, all other materials are considered indirect materials. The factory in Boise assembles the final product, which it then sells directly to customers around the world.

The company has been in business for five years and has finally established a strong market for its product. Using past information it is preparing its budget for the third quarter of 2017. You are the companys accountant and will be presenting each statement and schedule at an upcoming board meeting in order for the board to discuss projected income, cash flows and the companys financial health

From the following information prepare the following schedules/statements for each of the following months: July, August and September 2017. You do NOT need to create quarter ending schedules.

1) Projected Production Schedule

2) Projected Direct Materials Schedule

3) Projected Raw Materials Purchases Schedule

4) Projected Cost of Goods Manufactured Schedule

5) Pro-forma income statement

6) Combined Cash Flow Forecast (Budgeted cash flows for each month)

7) Pro-forma Balance Sheet

8) Capital lease amortization schedule

9) Depreciation schedule

Use the following assumptions and data in making your calculations:

Sales Forecast: Past and projected sales in units.

Month				Sales
January		900
February		950
March		1,150
April		1,000
May		1,100
June		1,300
July		1,400
August		1,200
September		1,000
October		1,050
November		950
December		900

Sales Price: Devices are sold at 100% markup on cost.

Use this sales price to determine each months revenue.

Sales Collections:

All sales are on account.

Past history shows cash from sales is collected 30% in month of sale, 65% in the month following the sale and 3% in the second month following the sale.

2% of sales on account are typically uncollectible.

Uncollectible accounts are written off on June 30 and December 31

Raw and Direct Materials:

The production manager has determined the company needs to have 65% of the following months packets on hand at the end of each month.

Each packet of direct material costs $85.00.

Ending direct materials inventory is 2,100 packets on May 31, 2017 (priced at $85 per packet).

Direct material purchases are paid for on the 15^th day of the month following month of purchases.

Direct Material purchases in June were $592,238.

Direct Labor:

Ten hours of direct labor are required to assemble each device.

The direct labor cost (including fringe benefits) is $24.00 per hour.

Wages earned by employees during the first half of each month are paid on the 25th with the remainder paid on the 10^th of the following month. Assume that workforce is stable each month (hence, wages and salaries are the same every day of the month).

Direct labor costs in June were $324,000.

Manufacturing overhead:

The company has determined that MOH is 25% of direct labor cost.

Manufacturing overhead is paid 10% in cash with the balance paid in 30 days.

WIP Inventory: WIP Inventory is 0 at the end of each month.

Finished Goods Inventory: Ending Balance on June 30, 2017 300 units. The company wants to have at least 50% of next months projected sales in ending finished goods inventory each month (assume previous months met this requirement).

Capital Lease: On July 1, 2017, the company acquires equipment and finances 100% of it through a capital lease.

Life of the equipment is 60 months with no salvage value. The company uses straight-line depreciation.

Capital lease payments are $12,000 per month including an imputed interest component.

Cost of capital is 5%. Use this rate to calculate the present value of the cash payments and the present value of the lease principal as of July 1, 2017.

The first payment is due on August 1, 2017.

Selling, General and Administrative Expenses:

Selling commissions are 15% of sales price. These are paid on the 15^th day of the month following month of sale.

Administrative salaries and fringe benefits are $65,000 per month paid on the 10^th and 25^th of each month.

Rent is $14,000 per month payable on the first day of each month.

The companys $36,000 insurance premium for 6 months of coverage is due on July 1, 2017.

Other general and administrative expenses are estimated to be 10% of sales. They are paid in the month after they are incurred.

Credit Line:

The company has a $500,000 line of credit secured by inventory and accounts receivable.

Borrowings and repayments on this line must be in increments of $ 10,000.

Interest is 12% per annum and is paid on the 1^st day of each quarter.

Assume all borrowing and repayments are made on the 1^st of the month.

The company borrows from the line of credit if the cash balance is forecasted to fall below $100,000.

Other information:

Income tax rate is 35%. The company makes estimated tax payments of $200,000 on January 15, April 15, July 15, and October 15.

Account balances on June 30, 2017:

Beginning cash balance $ 185,000

Common stock $ 150,000

Retained earnings $ 452,000

2) Direct material schedule (Refer note 1 & 2)

Projected Direct Materials Schedule
										July	August	September
Budgeted Production Units (Refer Note 1)										1,700	1,100	1,025
Add: Budgeted ending direct material Inventory (65% of next month packet requirement)										715	666	650
Total Direct material required										2,415	1,766	1,675
Less: Beginning Direct material Inventory										2,100	715	666
Budgeted Direct material required purchases units										315	1,051	1,009

Note 1 Budgeted Production Units

Production Budget
													July	August	September	October	Nobember
Expected Sales in units													1,400	1,200	1,000	1,050	950
Plus: Desired Finished Goods Ending Inventory (50% of next month sales)													600	500	525	475	0
Total Needs													2,000	1,700	1,525	1,525	950
Less Beginning Finished Goods Inventory													300	600	500	525	475
Units to be produced													1,700	1,100	1,025	1,000	475

Note 2 Working for Direct material schedule

Direct Material Schedule
					July	August	September	October
Budgeted Production Units					1,700	1,100	1,025	1,000
Add: Budgeted ending direct material Inventory (65% of next month packet requirement)					715	666	650
Total Direct material required					2,415	1,766	1,675
Less: Beginning Direct material Inventory					2,100	715	666
Budgeted Direct material required purchases units					315	1,051	1,009

3)

Raw materials Purchase schedule
		JULY	AUGUST	SEPTEMBER
Production Requirement
Budgeted production		1700	1100	1025
Electronic Packets		3	3	3
Materials required (A)		5100	3300	3075
Add:
Finished Goods Inventory		715	666	650
Packets Required		3	3	3
Materials required (B)		2145	1998	1950
Materials Available (A+B)		7245	5298	5025
Less:
Beginning Inventory		2100	2145	1998
Raw materials Purchased		5145	3153	3027
Price per packet ($)		85	85	85
Cost of Materials purchased		437325	268005	257295

4)

Cost of Goods manufactured
	JULY	AUGUST	SEPTEMBER	Quarter
Beginning Inventory (2100 * $85)				178500
Inventory used
5100 * $85	433500			433500
3300 * $85		280500		280500
3075 * $85			261375	261375
Direct Labor:
Production Required	1700	1100	1025
Hours required	10	10	10
Total Hours employed	17000	11000	10250
Cost per hour ($)	24	24	24
Total D/Labor Cost	408000	264000	246000	918000
Manufacturing Overhead
(25% of Direct labor)	102000	66000	61500	229500
Cost of goods available for sale				2301375
Less:
Ending inventory (1950 * $85)				165750
Cost of goods sold				2135625

OKAY SO I HAVE THE FIRST FOUR SCHEDULES AND I JUST NEED HELP ON 5 AND 6 PLEASE. IF YOU COULD PLEASE SHOW ME I WOULD GREATLY APPRECIATE IT. THANK YOU. AND IF YOU COULD PLEASE DESCRIBE THE CALCULATIONS. Could you please copy it in word to see the instructions formatted correctly.

Answer & Explanation Solved by verified expert

See Answer