Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but to also provide pro-forma financial statements for 2018. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows:

End of the year information:

Additional Information:

• Sales for December total 10,000 units. Each month’s sales are expected to exceed the prior month’s results by 5%. The product’s selling price is $25 per unit.
• Company policy calls for a given month’s ending inventory to equal 80% of the next month’s expected unit sales. The December 31 2017 inventory is 8,400 units, which complies with the policy. The purchase price is $15 per unit.
• Sales representatives’ commissions are 12.5% of sales and are paid in the month of the sales. The sales manager’s monthly salary will be $3,500 in January and $4,000 per month thereafter.
• Monthly general and administrative expenses include $8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable.
• The company expects 30% of sales to be for cash and the remaining 70% on credit. Receivables are collected in full in the month following the sale (none is collected in the month of sale).
• All merchandise purchases are on credit, and no payables arise from any other transactions. One month’s purchases are fully paid in the next month.
• The minimum ending cash balance for all months is $50,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance.
• Dividends of $100,000 are to be declared and paid in February.
• No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter.
• Equipment purchases of $55,000 are scheduled for March.

Part A:

• Prepare budgets such that the pro-forma financial statements for the first quarter of 2018 may be prepared.
• Expected cash receipts from customers and the expected March 31 balance of accounts receivable.
• Expected cash payments for purchases and the expected March 31 balance of accounts payable.
• Cash buget
• Budgeted income statement.
• Budgeted statement of retained earnings.
• Budgeted balance sheet.

Show excel formula

Excel Project Instructions

Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but to also provide pro-forma financial statements for 2018. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows:

End of the year information:

Additional Information:

• Sales for December total 10,000 units. Each month’s sales are expected to exceed the prior month’s results by 5%. The product’s selling price is $25 per unit.
• Company policy calls for a given month’s ending inventory to equal 80% of the next month’s expected unit sales. The December 31 2017 inventory is 8,400 units, which complies with the policy. The purchase price is $15 per unit.
• Sales representatives’ commissions are 12.5% of sales and are paid in the month of the sales. The sales manager’s monthly salary will be $3,500 in January and $4,000 per month thereafter.
• Monthly general and administrative expenses include $8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable.
• The company expects 30% of sales to be for cash and the remaining 70% on credit. Receivables are collected in full in the month following the sale (none is collected in the month of sale).
• All merchandise purchases are on credit, and no payables arise from any other transactions. One month’s purchases are fully paid in the next month.
• The minimum ending cash balance for all months is $50,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance.
• Dividends of $100,000 are to be declared and paid in February.
• No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter.
• Equipment purchases of $55,000 are scheduled for March.

Part A:

• Prepare budgets such that the pro-forma financial statements for the first quarter of 2018 may be prepared.
• Expected cash receipts from customers and the expected March 31 balance of accounts receivable.
• Expected cash payments for purchases and the expected March 31 balance of accounts payable.
• Cash budget.
• Budgeted income statement.
• Budgeted statement of retained earnings.
• Budgeted balance sheet.

Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but to also provide pro-forma financial statements for 2018. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows:

End of the year information:

Additional Information:

• Sales for December total 10,000 units. Each month’s sales are expected to exceed the prior month’s results by 5%. The product’s selling price is $25 per unit.
• Company policy calls for a given month’s ending inventory to equal 80% of the next month’s expected unit sales. The December 31 2017 inventory is 8,400 units, which complies with the policy. The purchase price is $15 per unit.
• Sales representatives’ commissions are 12.5% of sales and are paid in the month of the sales. The sales manager’s monthly salary will be $3,500 in January and $4,000 per month thereafter.
• Monthly general and administrative expenses include $8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable.
• The company expects 30% of sales to be for cash and the remaining 70% on credit. Receivables are collected in full in the month following the sale (none is collected in the month of sale).
• All merchandise purchases are on credit, and no payables arise from any other transactions. One month’s purchases are fully paid in the next month.
• The minimum ending cash balance for all months is $50,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance.
• Dividends of $100,000 are to be declared and paid in February.
• No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter.
• Equipment purchases of $55,000 are scheduled for March.

Part A:

• Prepare budgets such that the pro-forma financial statements for the first quarter of 2018 may be prepared.
• Expected cash receipts from customers and the expected March 31 balance of accounts receivable.
• Expected cash payments for purchases and the expected March 31 balance of accounts payable.
• Cash budget.
• Budgeted income statement.
• Budgeted statement of retained earnings.
• Budgeted balance sheet.

Zylar Industries is a manufacturer of standard and custom-design bottling equipment. Early in December 20x9, Lyan company as Zylar to quote a price for a custom- designed bottling machine to be delivered in April, Lyan intends to make a decision on the purchase of such a machine by January 1, so Zylar would have the entire frit quarter of 20x1 to build the equipment.

Zylar’s pricing policy for custom-desighned equipment is 40 percent markup on absorption manufacturing cost. Lyan’s specification for the equipment hve been reviewed by Zylar’s Engineering and cost management departments which made the following estimates for direct material and labor

Direct material 258,000

Direct labor (12,600 hours at $18) 226,800

Manufacturing overhead is applied on the basis of direct-labor hours. Zylar normally plans to run its plant at a level of 17,000 direct labor hours per month and assigns overhead on the basis of 204,000 direct labor hours per year. The overhead application rate for 20x1 of $13 per hour is based on the following budgeted manufacturing overhead costs for 20x1.

Variable manufacturing overhead $1,591,200

Fix manufacturing overhead             1,060,800

Total Manufacturing overhead         2,652,000

Zylar’s production schedule calls for 13,800 direct-labor hours per month during the first quarter. If Zylar is awarded the contract for the Lyan equipment, production of one of its standard products would have to be reduced. This is necessary because production levels can only be increase to 17,000 direct labor hours each month on short notice. Furthermore Zylars employees are unwilling to work overtime.

Sales of the standard product for which the production schedule would be reduced has a unit sales price of $13,800 and the following cost structure.

Direct material                                                     $2700

Direct labor   (300 hours a $18)                           5400

Manufacturing over head (300 hours at $13)    3,900

Total cost                                                                  12,000

Lyan needs the custom designed equipment to increase its bottle-making capacity it will not have to buy bottles from an outside supplier. Lyan company requires 5,170,000 bottles annually. Its present equipment has a maximum capacity of 4,560,000 bottles with a direct traceable cash ourlay of 24 cents per bottle. Thus Lyan had to purchase 610,000 bottles from a supplier at a savings of 1 cents per bottle. Zylar estimates that Lyan annual bottle demand will continue to be 5,170,000 bottles over the next five years, the estimated life of the special- purpose equipment.

Zylar industries plans to submit a bid to Lyan Company for the manufacture of the special-purpose bottling equipment.

Calculate the bid Zylar would submit if it follows its standard pricing policy for the special purpose equipment

2. Calculate the minimum bid Zylar would be willing to submit on the Lyan equipment that would result in the same total contribution margin as planned for the first quarter of 20x1 (do not round intermediate calculations)

The first company: preparing the income statement in the short way to the company, and the data is below?

Here is the information for Al-Rafidain Company, which is required to prepare the short-term income statement:

400,000 net sales, 300,000 dividend income, 150,000 rental income, 75,000 consulting revenue, 100,000 cost of goods sold, 35,000 marketing expenses, 30,000 selling expenses, 20,000 administrative expenses, 10,000 maintenance expenses, 10,000 tax expenses

During its first year of operations, Motorola Company paid $11,860 for direct materials and $10,900 for production workers' wages. Lease payments and utilities on the production facilities amounted to $9,900 while general, selling, and administrative expenses totaled $3,600. The company produced 7,100 units and sold 4,400 units at a price of $7.10 a unit.

What is Motorola's cost of goods sold for the year?

A. $20,240

B. $26,360

C. $16,336

D. $32,660

Jones Co. started the year with no inventory. During the year, it purchased two identical inventory items at different times. The first purchase cost $1,170 and the other, $1,500. Jones sold one of the items during the year.

Required
Based on this information, how much product cost would be allocated to cost of goods sold and ending inventory on the year-end financial statements, assuming use of

a. FIFO?
b. LIFO?
c. Weighted average?

Pantanal, Inc., manufactures car seats in a local factory. For costing purposes, it uses a first-in, first-out (FIFO) process costing system. The factory has three departments: Molding, Assembling, and Finishing. Following is information on the beginning work-in-process inventory in the Assembling Department on August 1:

During August, 101,000 units were transferred in from the Molding Department at a cost of $2,121,000 and started in Assembling. The Assembling Department incurred other costs of $1,069,410 in August as follows:

At the end of August, 17,000 units remained in inventory that were 90 percent complete with respect to direct materials and 50 percent complete with respect to conversion.

Required:

Compute the cost of goods transferred out in August and the cost of work-in-process ending inventory.

1. What is the cost of goods transferred out?_______

2. Cost of WIP ending inventory? _______

Pantanal, Inc., manufactures car seats in a local factory. For costing purposes, it uses a first-in, first-out (FIFO) process costing system. The factory has three departments: Molding, Assembling, and Finishing. Following is information on the beginning work-in-process inventory in the Assembling Department on August 1:

During August, 106,000 units were transferred in from the Molding Department at a cost of $2,024,600 and started in Assembling. The Assembling Department incurred other costs of $954,505 in August as follows:

At the end of August, 20,000 units remained in inventory that were 80 percent complete with respect to direct materials and 40 percent complete with respect to conversion.

Required:

Compute the cost of goods transferred out in August and the cost of work-in-process ending inventory. (Do not round intermediate calculations.)

Company XYZ manufactures a tangible product and sells the product at wholesale. In its first year of operations, XYZ manufactured 1,200 units of product and incurred $246,000 direct material cost and $147,000 direct labor costs. For financial statement purposes, XYZ capitalized $102,000 indirect costs to inventory. For tax purposes, it had to capitalize $133,000 indirect costs to inventory under the UNICAP rules. At the end of its first year, XYZ held 120 units in inventory. In its second year of operations, XYZ manufactured 2,400 units of product and incurred $504,000 direct material cost and $312,000 direct labor costs. For financial statement purposes, XYZ capitalized $176,000 indirect costs to inventory. For tax purposes, it had to capitalize $230,000 indirect costs to inventory under the UNICAP rules. At the end of its second year, XYZ held 360 items in inventory. Compute XYZ’s cost of goods sold for book purposes and for tax purposes for second year assuming that XYZ uses the FIFO costing convention. Compute XYZ’s cost of goods sold for book purposes and for tax purposes for second year assuming that XYZ uses the LIFO costing convention.