Using the master budget provided in the tables below, decide which quarter would be most profitable to purchase, with cash, a new $40,000 machine that will reduce direct labor time per unit by 1%.

1) Is this purchase a good decision?

Make a table of the increase (decrease) in profits from the baseline of no new machine by quarter of purchase.

What is the effective (net) cash cost of the machine by quarter of purchase? Assume the machine (expected to last 10 years) is used from the beginning of the quarter purchased, is paid for at the beginning of that quarter, and the labor reduction is immediate.

Highlight (in yellow) the cells that are being changed within the Master Budget in this analysis.

Format a table in excel as shown below. Show formulas used clearly and show excel cell references:

(1)% Labor	Profits	+/(-) profits	Ending Cash	Net Cash Cost
Baseline	$827,629.00	$ -	$849,025.00	$ -
Installed Q1
Installed Q2
Installed Q3
Installed Q4

Master Budget Tables:

Toby LLC
Sales Budget
Year ending December 31, 2018
	Quarter
	1	2	3	4	Year
Budget sales in units	10,000	20,000	30,000	25,000	85,000
Selling price per unit	$ 40.00	$ 40.00	$ 40.00	$ 40.00	$ 40.00
Total sales	$ 400,000	$ 800,000	$ 1,200,000	$ 1,000,000	$ 3,400,000
Percent of sales collected in the period of the sale			80%
Percent of sales collected in the period after the sale			20%
Schedule of Expected Cash Collections
A/R beginning balance	$ 120,000				$ 120,000
Q1 Sales	$ 320,000	$ 80,000			$ 400,000
Q2 Sales		$ 640,000	$ 160,000		$ 800,000
Q3 Sales			$ 960,000	$ 240,000	$ 1,200,000
Q4 Sales	-	-	-	$ 800,000	$ 800,000
Total cash collections	$ 440,000	$ 720,000	$ 1,120,000	$ 1,040,000	$ 3,320,000

------------------------------------------------------

Toby LLC
Production Budget (in units)
Year ending December 31,2018
	Quarter
	1	2	3	4	Year
Budget sales in units	10,000	20,000	30,000	25,000	85,000
Add desired ending inventory (FGI)	6,000	9,000	7,500	3,000	3,000
Total needs	16,000	29,000	37,500	28,000	88,000
Less beginning inventory (FGI)	5,000	6,000	9,000	7,500	5,000
Required production	11,000	23,000	28,500	20,500	83,000
Ending inventory % of next qtr sales	30%

---------------------------------------------------------------------

Toby LLC
Direct Materials Budget
Year ending December 31,2018
	Quarter
	1	2	3	4	Year
Required Production	11,000	23,000	28,500	20,500	83,000
Raw material needed per unit (lbs)	15.0	15.0	15.0	15.0	15.0
Raw material needed for production	165,000	345,000	427,500	307,500	1,245,000
Add desired ending raw materials	69,000	85,500	61,500	40,000	22,500
Total raw materials needed	234,000	430,500	489,000	347,500	1,267,500
Less beginning inventory of RM	20,000	69,000	85,500	61,500	20,000
Raw material to be purchased	214,000	361,500	403,500	286,000	1,247,500
Cost of raw material per lbs	$ 0.25	$ 0.25	$ 0.25	$ 0.25	$ 0.25
Total cost of RM purchased	$ 53,500	$ 90,375	$ 100,875	$ 71,500	$ 311,875
Percent of next qtr RM for ending inventory RM			20%
Percent of purchases paid for in the period of purchase			75%
Percent of purchases paid for in the period after purchase			25%
2007 cost of raw material per unit:			$0.25

Schedule of Expected Cash Disbursements for Materials
A/P beginning balance	$ 50,000				$ 50,000
Q1 Purchases	$ 40,125	$ 13,375			$ 53,500
Q2 Purchases		$ 67,781	$ 22,594		$ 90,375
Q3 Purchases			$ 75,656	$ 25,219	$ 100,875
Q4 Purchases	-	-	-	$ 53,625	$ 53,625
Total Cash Disbursements for material	$ 90,125	$ 81,156	$ 98,250	$ 78,844	$ 348,375

---------------------------------------------------------------------

Toby LLC
Direct Labor Budget
Year ending December 31,2018
	Quarter
	1	2	3	4	Year
Required Production	11,000	23,000	28,500	20,500	83,000
Direct labor hours per unit	1.00	1.00	1.00	1.00	1.00
Total DLH needed	11,000	23,000	28,500	20,500	83,000
Direct labor cost per hour	$ 16.00	$ 16.00	$ 16.00	$ 16.00	$ 16.00
Total direct labor cost	$ 176,000	$ 368,000	$ 456,000	$ 328,000	$ 1,328,000

Toby LLC
Manufacturing Overhead Budget
Year ending December 31,2018
	Quarter
	1	2	3	4	Year
Budgeted Direct Labor Hours	11,000	23,000	28,500	20,500	83,000
Variable manufacturing overhead rate	$ 3.00	$ 3.00	$ 3.00	$ 3.00	$ 3.00
Variable MOH	$ 33,000	$ 69,000	$ 85,500	$ 61,500	$ 249,000
Fixed manufacturing overhead	$ 50,000	$ 50,000	$ 50,000	$ 50,000	200,000
Total manufacturing overhead	$ 83,000	$ 119,000	$ 135,500	$ 111,500	$ 449,000
Less Depreciation	$ 20,000	$ 20,000	$ 20,000	$ 20,000	80,000
Cash disbursements for MOH	$ 63,000	$ 99,000	$ 115,500	$ 91,500	$ 369,000
Total manufacturing overhead					$ 449,000
Budgeted direct labor hours					83,000
Predetermined overhead rate					$ 5.41

-------------------------------------------------------------

Toby LLC
Ending Finished Goods Inventory Budget (absorption)
Year ending December 31,2018
Item	Quantity		Cost		Total
Production cost per unit:
Direct Materials	15.00	lbs	$ 0.25	per lbs		$ 3.75
Direct Labor	1.00	hrs	$ 16.00	per hr		$ 16.00
Manufacturing Overhead	1.00	hrs	$ 5.41	per hr		$ 5.41
Unit product cost						$ 25.16
Budgeted finished goods inventory:
Ending finished goods inventory in units						3,000
Unit product costs						$ 25.16
Ending finished goods inventory in dollars						$ 75,479
2017 absorption cost per unit:		$20.40

Toby LLC
Selling and Administrative Expense Budget
Year ending December 31,2018
	Quarter
	1	2	3	4	Year
Budgeted sales in units	10,000	20,000	30,000	25,000	85,000
Variable Sell/Admin expense per unit	$ 1.80	$ 1.80	$ 1.80	$ 1.80	$ 1.80
Variable Sell/Admin expense	$ 18,000	$ 36,000	$ 54,000	$ 45,000	$ 153,000
Fixed Selling and Admin expenses:
Advertising	10,000	10,000	10,000	10,000	40,000
Executive salaries	50,000	50,000	50,000	50,000	200,000
Insurance	6,000	6,000	6,000	6,000	24,000
Property taxes	2,000	2,000	2,000	2,000	8,000
Depreciation	8,000	8,000	8,000	8,000	32,000
Total fixed selling & admin expense	76,000	76,000	76,000	76,000	304,000
Total Selling and Administrative	$ 94,000	$ 112,000	$ 130,000	$ 121,000	457,000
Less Depreciation	8,000	8,000	8,000	8,000	32,000
Cash disbursement for sell & admin	$ 86,000	$ 104,000	$ 122,000	$ 113,000	$ 425,000

-----------------------------------------------------------------------------

Cash Budget
Year ending December 31,2018
	Quarter
	1	2	3	4	Year
Cash balance, beginning	$ 40,000	34,875	102,719	430,969	$ 40,000
Add receipts:
Collections from customers	$ 440,000	$ 720,000	$ 1,120,000	$ 1,040,000	$ 3,320,000
Total Cash Available	$ 480,000	$ 754,875	$ 1,222,719	$ 1,470,969	$ 3,360,000
Less disbursements:
Direct Material	$ 90,125	$ 81,156	$ 98,250	$ 78,844	$ 348,375
Direct Labor	$ 176,000	$ 368,000	$ 456,000	$ 328,000	$ 1,328,000
Manufacturing overhead	$ 63,000	$ 99,000	$ 115,500	$ 91,500	$ 369,000
Selling & Administrative	$ 86,000	$ 104,000	$ 122,000	$ 113,000	$ 425,000
Equipment Purchases					$ -
Dividends	$ 40,000				$ 40,000
Total Disbursements	$ 455,125	$ 652,156	$ 791,750	$ 611,344	$ 2,510,375
Excess (deficiency) of cash	$ 24,875	$ 102,719	$ 430,969	$ 859,625	$ 849,625		Interest:
Financing:							6.00%
Borrowings (at begin of qtr)	$ 10,000	$ -	$ -	$ -	$ 10,000
Repayments (at end of year)	-	-	-	$ (10,000)	$ (10,000)
Interest	-	-	-	$ (600)	$ (600)
Total Financing	$ 10,000	$ -	$ -	$ (10,600)	$ (600)
Cash balance, ending	$ 34,875	$ 102,719	$ 430,969	$ 849,025	$ 849,025

----------------------------------------------------------------

Toby LLC
Income Statement
Year ending December 31,2018
	Quarter
	1	2	3	4	Year
Sales	$ 400,000	$ 800,000	$ 1,200,000	$ 1,000,000	$ 3,400,000
Cost of Goods Sold	227,798	503,193	754,789	628,991	2,114,771
Gross Margin	172,202	296,807	445,211	371,009	1,285,229
Selling & Admin Expense	94,000	112,000	130,000	121,000	457,000
Net operating income	78,202	184,807	315,211	250,009	828,229
Interest Expense	150	150	150	150	$ 600
Net Income	$ 78,052	$ 184,657	$ 315,061	$ 249,859	$ 827,629

----------------------------------------------------------------------

Toby LLC
Balance Sheet
As of December 31,2018
Assets
Current Assets:	2018			2017
Cash	$ 849,025			$ 60,000
A/R	$ 200,000			$ 180,000
Raw Material Inventory	$ 10,000			$ 6,000
Finished Goods Inventory	$ 75,479			$ 26,000
Total Current Assets		$ 1,134,504			$ 272,000
Plant & Equipment:
Land	$ 40,000			$ 40,000
Buildings & Equipment	$ 400,000			$ 400,000
Accumulated Depreciation	$ (302,000)			$ (190,000)
Plant & Equipment (net):		$ 138,000			$ 250,000
Total Assets		$ 1,272,504			$ 522,000
Liabilities and Share Holder's Equity
Current Liabilities:
A/P		$ 17,875			$ 55,000
Stock Holder's Equity:
Common Stock (at par)	$ 175,000			$ 175,000
Retained Earnings	$ 1,079,629			$ 292,000
Total Stock Holder's Equity		$ 1,254,629			$ 467,000
Total Liabilities & Equity		$ 1,272,504			$ 522,000