Panther Corporation appeared to be experiencing a good year. Sales in the first quarter were one-third ahead of last year, and the sales department predicted that this rate would continue throughout the entire year. The controller asked you, a summer accounting intern, to prepare a draft forecast for the year and to analyze the differences from last year's results. She based the forecast on actual results obtained in the first quarter plus the expected costs of production to be completed in the remainder of the year. She worked with various department heads (production, sales, and so on) to get the necessary information.

The results of these efforts follow:

Adjustments for the change in inventory and for income taxes have not been made. The scheduled production for this year is 415,000 units, and the planned sales volume is 365,000 units. Sales and production volume was 265,000 units last year. The company uses a full-absorption costing and FIFO inventory system and is subject to a 40 percent income tax rate.

The actual income statement for last year follows:

Required:

Prepare a budgeted income statement and balance sheet using the attached Excel template.

Provide your recommendations to management.

Also, please guide me with excel formulas. Thanks!

I have attached the excel template. Can't you solve it like that?? I mean it's really simple Come on!

$ 177,000 193,000 213,000 106,000 410,000 771,600 2,530,000 62,000 PANTHER CORPORATION Expected Account Balances for December 31, Year 2 Cash S 6,100 Accounts receivable 333,000 Inventory (January 1, Year 2) 318,000 Plant and equipment 585,000 Accumulated depreciation Accounts payable Notes payable (due within one year) Accrued payables Common stock Retained earnings Sales revenue Other income Manufacturing costs Materials 963,000 Direct labor 987,000 Variable overhead 629,000 Depreciation 33,000 Other fixed overhead 44,000 Marketing Commissions 106,000 Salaries 77,000 Promotion and advertising 206,000 Administrative Salaries 77,000 Travel 16,500 Office costs 49,000 Income taxes Dividends 33,000 $ 4,462,600 $ 4,462,600 PANTHER CORPORATION Statement of Income and Retained Earnings For the Budget Year Ended December 31, Year 1 2,030,000 82,000 $ 2,112,000 Revenues Sales revenue Other income Expenses Cost of goods sold Materials Direct labor Variable overhead Fixed overhead $ 610,000 622,000 297,000 61,000 1,590,000 318,000 1,908,000 318,000 $ Beginning inventory $ $ 1,590,000 $ 67,000 73,000 139,000 279,000 $ Ending inventory Selling Salaries Commissions Promotion and advertising General and administrative Salaries Travel Office costs Income taxes Operating profit Beginning retained earnings Subtotal Less dividends Ending retained earnings 69,000 14,000 45,000 128,000 46,000 2,043,000 69,000 735,600 804,600 33,000 771,600 $ B G H 1 1 C DE F PANTHER CORPORATION Budgeted Income Statement For Year Ended December 31, Year 2 2 $ 6 3 4 Revenues 5 Sales revenue 6 Other income 7 Expenses 8 Cost of goods sold 9 Materials $ 10 Direct labor 11 Variable overhead 12 Fixed overhead 13 $ 14 Beginning inventory 15 $ 16 Ending inventory 17 Selling 18 Salaries 19 Commissions Promotion and advertising 20 21 General and administrative 22 Salaries $ GA 23 Travel 24 Office costs 25 Income taxes 26 Operating profit $ A D E F 1 PANTHER CORPORATION Budgeted Balance Sheet Budgeted December 31, Year 2 2 $ 2 3 4 Current assets 5 Cash 6 Accounts receivable 7 Inventory 8 Income tax receivable 9 10 Total current assets Plant and equipment 12 Accumulated depreciation 13 Total assets 14 15 Current liabilities 16 Accounts payable 17 Accrued payable 18 Notes payable 19 20 Total current liabilities 21 Shareholders' equity 22 Common stock $ $ $ 23 Retained earnings 24 25 Total shareholders' equity Total liabilities and shareholders' equity $ 26 $ 177,000 193,000 213,000 106,000 410,000 771,600 2,530,000 62,000 PANTHER CORPORATION Expected Account Balances for December 31, Year 2 Cash S 6,100 Accounts receivable 333,000 Inventory (January 1, Year 2) 318,000 Plant and equipment 585,000 Accumulated depreciation Accounts payable Notes payable (due within one year) Accrued payables Common stock Retained earnings Sales revenue Other income Manufacturing costs Materials 963,000 Direct labor 987,000 Variable overhead 629,000 Depreciation 33,000 Other fixed overhead 44,000 Marketing Commissions 106,000 Salaries 77,000 Promotion and advertising 206,000 Administrative Salaries 77,000 Travel 16,500 Office costs 49,000 Income taxes Dividends 33,000 $ 4,462,600 $ 4,462,600 PANTHER CORPORATION Statement of Income and Retained Earnings For the Budget Year Ended December 31, Year 1 2,030,000 82,000 $ 2,112,000 Revenues Sales revenue Other income Expenses Cost of goods sold Materials Direct labor Variable overhead Fixed overhead $ 610,000 622,000 297,000 61,000 1,590,000 318,000 1,908,000 318,000 $ Beginning inventory $ $ 1,590,000 $ 67,000 73,000 139,000 279,000 $ Ending inventory Selling Salaries Commissions Promotion and advertising General and administrative Salaries Travel Office costs Income taxes Operating profit Beginning retained earnings Subtotal Less dividends Ending retained earnings 69,000 14,000 45,000 128,000 46,000 2,043,000 69,000 735,600 804,600 33,000 771,600 $ B G H 1 1 C DE F PANTHER CORPORATION Budgeted Income Statement For Year Ended December 31, Year 2 2 $ 6 3 4 Revenues 5 Sales revenue 6 Other income 7 Expenses 8 Cost of goods sold 9 Materials $ 10 Direct labor 11 Variable overhead 12 Fixed overhead 13 $ 14 Beginning inventory 15 $ 16 Ending inventory 17 Selling 18 Salaries 19 Commissions Promotion and advertising 20 21 General and administrative 22 Salaries $ GA 23 Travel 24 Office costs 25 Income taxes 26 Operating profit $ A D E F 1 PANTHER CORPORATION Budgeted Balance Sheet Budgeted December 31, Year 2 2 $ 2 3 4 Current assets 5 Cash 6 Accounts receivable 7 Inventory 8 Income tax receivable 9 10 Total current assets Plant and equipment 12 Accumulated depreciation 13 Total assets 14 15 Current liabilities 16 Accounts payable 17 Accrued payable 18 Notes payable 19 20 Total current liabilities 21 Shareholders' equity 22 Common stock $ $ $ 23 Retained earnings 24 25 Total shareholders' equity Total liabilities and shareholders' equity $ 26