Question

You are to prepare a P&L budget for the 2nd quarter of 2019 based on data from the previous 15 months. Your budget should include only cell references and formulas based on the budgeted data you will fill in for April through June. The budgeted data you will fill in for April through June should also use cell references and formulas based on your regression models which should be labeled as other sheets within your workbook.

-Management likes to keep an inventory equal to 15% of next month's estimated production. Build this into your budget to correctly calculate cost of goods sold. You can assume the company followed this pattern when purchasing March 2018 production needs. The average cost of each unit in ending inventory at March 31, 2019 is $51.29 (you should be able to calculate this number from the data). You will need to calculate the average cost of each unit in ending inventory for April through June based on each month's forecasted production costs.

I have to do the last part, the income statement. it can only be cell references. So I am not sure where he gets the $51.29 from the instructions.

Month Overhead Machine Hours #ofSetups Hours units S649.589S 2,287,650 1,258,233 $760,943 S 5,001,020 S1,440.725 $1,158,527 $5,835,425 S1,892,152 5 $1,100,496 $ 4,862,457 $1,690,450 0 19 BUDGETED: 2 S967,084 S 7,131,079 $1,883,292 5 2 8 28 Less cost of goods sold 29 Beginning inventory 0 Add: Cost of goods manufactured 1 Less: ending inventory 2 Cost of goods sold 3 Gross Margin 4 Less: SG&A Expense Income

Answer 1

Income Statement for the 2nd Quarter 2019
Point	Particulars	April	May	June
1	Sales ( As calculated in Question)	$     11,100,000.00	$   11,500,000.00	$    11,800,000.00
2	Less: Cost of Goods Sold ( = point no. 6)	$        9,689,584.00	$      9,950,008.00	$    10,171,708.00
3	Beginning Inventory ( Refer Working Note 2)	$        1,511,184.00	$      1,504,825.00	$      1,536,272.00
4	Add: Cost of Goods Manufactured ( Refer Working Note 1)	$        9,683,225.00	$      9,981,455.00	$    10,205,126.00
5	Less: Ending Inventory (( Refer Working Note 2)	$        1,504,825.00	$      1,536,272.00	$      1,569,690.00
6	Cost of Goods Sold	$        9,689,584.00	$      9,950,008.00	$    10,171,708.00
7	Gross Margin ( 1-2)	$        1,410,416.00	$      1,549,992.00	$      1,628,292.00
8	Less: SG & A Expense ( given )	$        1,499,925.00	$      1,518,391.00	$      1,532,240.00
9	Operating Income (7-8)	$           (89,509.00)	$            31,601.00	$            96,052.00
Note:	Since you have solved the budgeted data , so I have only prepare the income statement of 2nd Quarter showing all the calculation like ending inventory in which you would not get how it comes 51.29 and all. Only you have to do is that you should now accordingly select the cell reference to solve the question.
Working Note 1	Calculation of average cost of each unit in ending inventory
	Particulars	At 31-03-2019	At 30-04-2019	At 31-05-2019	At 30-06-2019
1	Direct Labour	$           585,748.00	$         953,271.00	$          967,084.00	$          977,443.00
2	Direct Material	$        3,991,543.00	$      6,882,960.00	$      7,131,079.00	$      7,317,168.00
3	Overhead	$        1,363,886.00	$      1,846,994.00	$      1,883,292.00	$      1,910,515.00
4	Total Cost of Goods Manufactured (1+2+3)	$        5,941,177.00	$      9,683,225.00	$      9,981,455.00	$    10,205,126.00
5	No. of Units	115830	196415	203493	208801
6	Average cost of each unit in ending inventory (4/5)	$                      51.29	$                    49.30	$                    49.05	$                     48.87
Working Note 2	Calculation of average total cost of ending inventory
	Particulars	At 31-03-2019	At 30-04-2019	At 31-05-2019	At 30-06-2019
1	Average cost of each unit in ending inventory ( refer Working Note 1)	$                      51.29	$                    49.30	$                    49.05	$                     48.87
2	Next Month Estimated Production ( Units produced)	196415	203493	208801	214110
3	No.of unit of ending inventory ( 15% of point 2 above)	$              29,462.25	$            30,523.95	$            31,320.15	$            32,116.50
4	Average total cost of ending inventory (1*3)	$        1,511,184.00	$      1,504,825.00	$      1,536,272.00	$      1,569,690.00