Using the master budget provided in the tables below, decide which quarter would
ID: 2815107 • Letter: U
Question
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:
Master Budget Tables:
------------------------------------------------------
Ending inventory % of next qtr sales
30%
---------------------------------------------------------------------
---------------------------------------------------------------------
$ 1,328,000
-------------------------------------------------------------
$ 425,000
-----------------------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------------
Explanation / Answer
Workings: 1 2 3 4 5 6 7 8 9 10 11 12 13 Original Total DLH needed (as per original) 11000 23000 28500 20500 83000 D/L cost Savings at $16 /hr. MOH saved at $ 3/hr. Amt. borrowed Int.costs on amt. Borrowed Total cash savings Col.(8+9-12) Installed Q1 1% Savings in D/L hrs. 110 230 285 205 830 13280 2490 40000-34875=5125 5125*6%= 308 15463 Installed Q2 -----do------ 0 230 285 205 720 11520 2160 0 0 13680 Installed Q3 -----do------ 0 0 285 205 490 7840 1470 0 0 9310 Installed Q4 -----do------ 0 0 0 205 205 3280 615 0 0 3895 (1)% Labor Original Profits +/(-) profits Revised profits Ending Cash Net Cash Cost Baseline 827629 827629 849025 Installed Q1 827629 15463-4000= 11463 839092 849025+5125-40000-308= 813842 -40000-15463= -24537 Installed Q2 827629 13680-(4000/4*3)= 10680 838309 849025-40000= 809025 -40000+13680= -26320 Installed Q3 827629 9310-(4000/4*2)= 7310 834939 849025-40000= 809025 -40000+9310= -30690 Installed Q4 827629 3895-(4000/4*1)= 2895 830524 849025-40000= 809025 -40000+3895= -36105 From the above, it is good /advisable to purchase & install the machine in Quarter 1 as it saves more cash costs & brings in more book profits --than in other quarters.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.