Question

In: Accounting

Comprehensive Budgeting Problem To be completed using Excel. The Highlander Corporation Balance Sheet December 31, 2019...

Comprehensive Budgeting Problem

To be completed using Excel.

The Highlander Corporation

Balance Sheet

December 31, 2019

Assets

Cash

           $        6,595

Accounts Receivable

                   10,000

Finished Goods

(575 units x $7.00 per unit

                     4,025

Raw Materials

(2,760 square inches @ $0.50 per square inch)

                     1,380

Plant and Equipment

               $     60,000

Less: Accumulated Depreciation

                      15,000     

                    45,000

              Total Assets

                    67,000

Liabilities

Trade Accounts Payable

                       9,000

                     9,000

Stockholders’ Equity

Common Stock

                       33,000

Retained Earnings

                       25,000

Total Stockholders’ Equity

                    58,000

Total liabilities & Stockholders’ equity

             $     67,000

In preparation for developing the master budget for the first three months of 2020, the following has been extracted from the company’s accounting records

  1. All sales are made on account at $25 per unit. Sixty Percent of the sales are collected in the month of sale, the remaining 40% are collected in the following month. Forecast sales for the first five months of 2020 are: January, 1,800 units; February, 2,000 units; March, 2,100 units; April, 2,000 units; May 2,200 units.
  2. Management wants to maintain the finished goods inventory at 30% of the following month’s sales.
  3. Watson uses four square inches of direct materials in each finished unit. The direct material prices

have been stable and are expected to remain so over the next six months. Management wants to maintain the ending direct materials inventory at 60% of the following month’s production needs.

  1. 70% of all purchases are paid in the month of purchase; the remaining 30% are paid in the subsequent month.
  2. Watson’s product requires 30 minutes of direct labor time. Each hour of direct labor costs $8. Additionally factory overhead is charged to products at a rate of $4 per labor hour.
  3. Equipment depreciation for 2020(already included in the factory overhead) will be $6,000 ($500 per month).
  4. No new equipment will be purchased and no new common stock will be issued during 2020.
  5. Operating expenses, all paid in cash, are expected to be $2,500 in January and grow by 1% each month after that.
  6. Beginning and ending Work-In-Process inventories are insignificant and can be ignored.

Prepare the following for January, February and March of 2020:

  1. a budgeted income statement
  2. a budgeted balance sheet

        Each schedule should show budgets for January, February, and March (e.g. don’t put each month on a separate sheet, but each schedule, A to I, should be on a separate worksheet). You may omit a Quarter Total column or sheet. To the degree possible, each sheet should be linked to all related sheets. For example, the sales figures on your production budget should come from (be linked to) your sales budget. You may create any additional worksheets (to be linked as well) if you need.

Solutions

Expert Solution

Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks!
I have already solved this before so I am not preparing answer a to g. I will give reference in my solution if I will use any answer from a to g.
The Highlander Corporation
Cost of goods sold
Material cost January February March Total Note
Budgeted Sales units       1,800.00        2,000.00      2,100.00 A
Material required per unit               4.00                4.00
            4.00
B
Material required       7,200.00        8,000.00     8,400.00 C=A*B
Cost per inch               0.50                0.50             0.50 D
Direct Material cost       3,600.00        4,000.00     4,200.00     11,800.00
E=C*D
Direct Labor Budget January February March Total
Budgeted Sales units       1,800.00        2,000.00      2,100.00 See A
Labor Hour required per unit               0.50                0.50             0.50 F
Labor Hour required          900.00        1,000.00     1,050.00 G=A*F
Cost per Hour               8.00                8.00             8.00 H
Direct Labor Budget       7,200.00        8,000.00     8,400.00     23,600.00 I=G*H
Manufacturing overhead Budget January February March Total
Labor Hour required          900.00        1,000.00     1,050.00 See F
Variable manufacturing overhead per hour               4.00                4.00             4.00 J
Manufacturing overhead Budget       3,600.00        4,000.00     4,200.00     11,800.00 K=F*J
Budgeted Cost of goods sold     14,400.00     16,000.00 16,800.00     47,200.00 L=E+I+K
Income Statement January February March Total
Budgeted Sales Revenue     45,000.00      50,000.00 52,500.00 147,500.00 See answer a. Sales Budget.
Less: Budgeted Cost of goods sold     14,400.00      16,000.00 16,800.00     47,200.00 See L
Budgeted Gross Margin     30,600.00     34,000.00 35,700.00 100,300.00
Less: Operating expenses Budget
      2,500.00        2,525.00      2,550.25        7,575.25 See answer f. Operating expenses Budget.
Budgeted Net Operating Income     28,100.00     31,475.00 33,149.75     92,724.75 M
Balance Sheet Amount $ Amount $ Note
Assets
Current Assets
Cash     80,766.15 See answer g. Cash Budget.
Accounts Receivable     21,000.00 This is 40% of March Sales.
Finished goods Inventory       4,225.00 This is a balancing figure.
Raw Material Inventory       2,472.00 See answer d and e. This is closing inventory* cost per inch i.e. 4,944 units * 0.50= $ 2,472.
Total Current Assets 108,463.15
Plant Property & Equipment
Plant Property & Equipment     60,000.00
Accumulated Depreciation (16,500.00) This is $ 15,000 (given in question, Dec 31 balance) + 1,500 (current period depreciation).
Total Plant Property & Equipment     43,500.00
Total Assets 151,963.15
Liabilities & Stockholder's equity Amount $ Amount $
Liabilities
Current Liabilities
Accounts Payable       1,238.40 This is 30% of March purchases.
Total Current Liabilities        1,238.40
Non Current Liabilities
Total Liabilities        1,238.40
Stockholder's Equity
Common Stock

Related Solutions

Prepare a balance sheet as of December 31, 2019. BUSINESS SOLUTIONS Balance Sheet December 31, 2019...
Prepare a balance sheet as of December 31, 2019. BUSINESS SOLUTIONS Balance Sheet December 31, 2019 Assets Cash Accounts receivable Prepaid insurance Prepaid insurance Prepaid rent Office equipment Accumulated depreciation—Office equipment 0 Accumulated depreciation—Computer equipment Computer equipment 0 Total Assets 0 Liabilities Accounts payable Wages payable Unearned computer services revenue Total Liabilities 0 Equity Retained earnings Common stock Total equity $0 Total Liabilities and Equity $0 After the success of the company’s first two months, Santana Rey continues to operate...
The December 31, 2019, balance sheet for Franklin Corporation is presented here. These are the only...
The December 31, 2019, balance sheet for Franklin Corporation is presented here. These are the only accounts on Franklin’s balance sheet. Amounts indicated by question marks (?) can be calculated using the following additional information: FRANKLIN CORPORATION Balance Sheet As of December 31, 2019 Assets Cash $ 40,000 Accounts receivable (net) ? Inventory ? Property, plant, and equipment (net) 294,000 $ 441,000 Liabilities and Stockholders’ Equity Accounts payable (trade) $ ? Income taxes payable (current) 40,000 Long-term debt ? Common...
The December 31, 2019, balance sheet for Baird Corporation is presented here. These are the only...
The December 31, 2019, balance sheet for Baird Corporation is presented here. These are the only accounts on Baird’s balance sheet. Amounts indicated by question marks (?) can be calculated using the following additional information: BAIRD CORPORATION Balance Sheet As of December 31, 2019 Assets Cash $ 20,000 Accounts receivable (net) ? Inventory ? Property, plant, and equipment (net) 295,000 $ 442,000 Liabilities and Stockholders’ Equity Accounts payable (trade) $ ? Income taxes payable (current) 20,000 Long-term debt ? Common...
Comprehensive Problem 1 Part 6 Prepare a balance sheet (in report form) as of December 31.
On December 1, Year 1, John and Patty Driver formed a corporation called Susquehanna Equipment Rentals. The new corporation was able to begin operations immediately by purchasing the assets and taking over the location of Rent-It, an equipment rental company that was going out of business. The newly formed company uses the following accounts.     Cash Capital Stock Accounts Receivable Retained Earnings Prepaid Rent Dividends Unexpired Insurance Income Summary Office Supplies Rental Fees Earned Rental Equipment Salaries Expense Accumulated...
Deitz Corporation is projecting a cash balance of $34,200 in its December 31, 2019, balance sheet....
Deitz Corporation is projecting a cash balance of $34,200 in its December 31, 2019, balance sheet. Deitz’s schedule of expected collections from customers for the first quarter of 2020 shows total collections of $210,900. The schedule of expected payments for direct materials for the first quarter of 2020 shows total payments of $49,020. Other information gathered for the first quarter of 2020 is sale of equipment $3,420; direct labor $79,800, manufacturing overhead $39,900, selling and administrative expenses $51,300; and purchase...
Deitz Corporation is projecting a cash balance of $36,900 in its December 31, 2019, balance sheet....
Deitz Corporation is projecting a cash balance of $36,900 in its December 31, 2019, balance sheet. Deitz’s schedule of expected collections from customers for the first quarter of 2020 shows total collections of $227,550. The schedule of expected payments for direct materials for the first quarter of 2020 shows total payments of $52,890. Other information gathered for the first quarter of 2020 is sale of equipment $3,690; direct labor $86,100, manufacturing overhead $43,050, selling and administrative expenses $55,350; and purchase...
On December 31, 2019, the balance sheet of Red Stout Corporation reported bonds outstanding with a...
On December 31, 2019, the balance sheet of Red Stout Corporation reported bonds outstanding with a face value of $2,000,000 and a related unamortized premium of $60,000. Interest is payable semiannually on January 1 and July 1. A) Prepare an entry in journal form without explanations to record the retirement of bonds with a face value of $1,200,000 on January 1, 2020, assuming the bonds were redeemed at a call price of 104. B) Prepare an entry in journal form...
Riyadh Star, Balance Sheet Statement December 31, 2018 & December 31, 2019 2018 2019 Cash $        ...
Riyadh Star, Balance Sheet Statement December 31, 2018 & December 31, 2019 2018 2019 Cash $         104,000 $       123,250 Accounts Receivable             183,350           100,000 Inventory             250,000           210,000 Prepaid Expenses               80,000           120,000 Equipment (Net)             584,650           800,000 Total Assets $      1,202,000 $    1,353,250 Accounts Payable $         220,000 $       180,000 Salary Payable               94,000             56,250 Interest Payable               20,000             37,000 Bonds Payable             320,000           300,000 Common Shares             370,000           580,000 Retained Earnings...
A comparative balance sheet for Carla Corporation is presented as follows. December 31 Assets 2020 2019...
A comparative balance sheet for Carla Corporation is presented as follows. December 31 Assets 2020 2019 Cash $ 72,880 $ 22,000 Accounts receivable 84,590 68,710 Inventory 182,590 191,710 Land 73,590 112,710 Equipment 262,590 202,710 Accumulated Depreciation-Equipment (71,590 ) (44,710 )    Total $604,650 $553,130 Liabilities and Stockholders' Equity Accounts payable $ 36,590 $ 49,710 Bonds payable 150,000 200,000 Common stock ($1 par) 214,000 164,000 Retained earnings 204,060 139,420    Total $604,650 $553,130 Additional information: 1. Net income for 2020 was $130,180. No...
A comparative balance sheet for Pharoah Corporation is presented as follows. December 31 Assets 2020 2019...
A comparative balance sheet for Pharoah Corporation is presented as follows. December 31 Assets 2020 2019 Cash $ 72,800 $ 22,000 Accounts receivable 83,260 67,460 Inventory 181,260 190,460 Land 72,260 111,460 Equipment 261,260 201,460 Accumulated Depreciation-Equipment (70,260 ) (43,460 )    Total $600,580 $549,380 Liabilities and Stockholders' Equity Accounts payable $ 35,260 $ 48,460 Bonds payable 150,000 200,000 Common stock ($1 par) 214,000 164,000 Retained earnings 201,320 136,920    Total $600,580 $549,380 Additional information: 1. Net income for 2020 was $127,520. No...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT