Question

Holt Manufacturing
For the year ended December 31 2017
Beginning raw materials inventory	210,000
Ending raw materials inventory	30,000
Purchases of raw materials	552,000
Direct labor	408,000
Indirect manufacturing labor	120,000
Depreciation of factory equipment	240,000
Factory Rent	60,000
Rent on sales offices	24,000
Factory utilities	45,000
Property taxes on factory premises	15,000
Maintenance of factory equipment	60,000
Beginning work in process inventory	180,000
Ending work in process inventory	420,000
Beginning finished goods inventory	270,000
Ending finished goods inventory	150,000
Sales representative salaries	84,000
Distribution costs	114,000
Interest expense	8,000
Sales revenue	2,000,000
Tax rate if pre-tax income/profit is less than	100,000	10%
Tax rate if income/profit is at least	100,000	20%

Your basic financial model is complete but you must learn to adjust it for data errors. The accountant for Holt Manufacturing has discovered the following errors in the accounting estimates for the year ended December 31, 2017:

1. The annual interest expense was $4,800, not $8,000 as per the original data sheet.    

2. The annual insurance charge expense for the factory was $30,000. This was omitted from the original list of expenses in the data sheet. Add this extra manufacturing overhead to your "data" sheet.

3. Administrative salaries of $18,000 were also omitted. Add this to the "data" sheet and include a formula and extra line item in your income statement.

4. The accountant failed to account for the factory utilities rebate the company receives from using renewable energy sources. The rebate has both a fixed and percentage element ($1,500 + 10% of the total annual utilities cost), and should be deducted from the utilities overhead cost at the year end. The amount of the rebate is negotiable with the energy company, so you must allow for its adjustment in your Excel workbook.

5. The company gives customers a 1.5% sales discount, but this has not been accounted for. As a result, total sales annual revenue must be reduced by the amount of the discount. The discount rate may change in the future, so must be added to the original data sheet as a key variable, and the amounts of both gross and net revenue must be shown as line items in the income statement.           `

6. The income tax threshold is as follows: 10% tax rate is applicable for up to $100,000 of net income for the year and 20%, if the income is more than $100,000. Modify your data sheet and formulae to reflect this change.

7. Add a comment box to the Net Income/Loss cell that explains the impact that changes (i) to (vi) have on reported net income - does the company now report a net income or loss for 2017? Make sure this comment box is visible and printed on your worksheet.

8. The accountant wishes you to provide an analysis of the company’s operating cost structure. Add an extra worksheet called “Cost analysis”, and construct a suitable table of the company operating costs (i.e. cost of sale, selling, admin and distribution costs) from the income statement for 2016. Link you table with the income statement entries. Do not hard code this data.

Answer 1

This can be solved without excel and is helpful in exams where You need to write the answers

Part 1 - Preparation of Income Statement (after adjustment of error)

Holt Manufacturinmg

Particulars		Amount
Sales Revenue	2000000
Less - Sales Discount (2000000*1.5%)	(30000)	1970000
Less : Operating cost of sales (Note 1)		(1824000)
Net Operating Income		146000
Non Operating Expenses
Interest Cost		(4800)
Net Income		141200
Less : Income Tax (assuming slab analysis) For 100000 = 10% = 10000 For remaining 41200 = 20% = 8240 Total tax = (10000+8240) = 18240		(18240)
Net Income after tax		122960

Working Note 1 - Cost Analysis

Operating Cost Structure

Particulars		Amount
Beginning raw materials inventory	210000
+ Purchases of raw materials	552000
- Ending raw materials inventory	(30000)
Cost of Material consumed		732000
Direct Labour		408000
Prime Cost (732000+408000)		1140000
Factory Overhead
Indirect Manufacturing Labour		120000
Factory rent		60000
Depreciation on factory equipment		240000
Factory utilities (45000 - 1500) - (45000*10%)		39000
Property taxes on factory premises		15000
Maintenance of factory equipment		60000
Factory Insurance		30000
+ Beginning work in process inventory		180000
- Ending work in process inventory		(420000)
Work cost		1464000
+ Beginning finished goods inventory		270000
- Ending finished goods inventory		(150000)
Cost of goods sold		1584000
Selling, administration and distribution cost
rent of sales office		24000
administrative salary		18000
sales representative salary		84000
distribution cost		114000
Cost of sales		1824000

Part 2 - Impact on net income/loss due to adjusting error

Particulars	Amount
Decrease in revenue
Administrative salary	18000
sales discount	30000
Factory Insurance	30000
Total Decrease in revenue	78000
Increase in revenue
Factory Utilities (45000 - 39000)	6000
Interest expenses (8000-4800)	3200
Total increase in revenue	9200
Net Reduction in income	68800