Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You project is to create a model for a production cost report using the weighted average method for the month of May. Following good Excel design techniques, you should have an input area in which you put the department information for the month, and an output area that calculates the production cost report. As always, you should have only formulas or references in your output area. You should also include directions (in a text box) for users.
After completion of the production cost report using the weighted average method, create a new worksheet and label the tab “FIFO”. Create a production cost report using the same information using the FIFO method.
Department Data:
|
Information about units: |
|
|
Units in beginning WIP |
21,000 |
|
Started during the month |
240,000 |
|
Units in Ending WIP |
20,000 |
|
Percentage of Completion: |
|
|
Beginning Inventory: |
|
|
Direct materials |
40% |
|
Conversion |
70% |
|
Ending Inventory: |
|
|
Direct Materials |
30% |
|
Conversion |
60% |
|
Costs in Beginning WIP: |
|
|
Direct materials |
$210,000 |
|
Conversion |
$135,000 |
|
Costs incurred during the month: |
|
|
Direct Materials |
$1,150,000 |
|
Direct Labor |
$45,000 |
|
Overhead |
$605,000 |
Process Cost Project – non-Excel
Answer questions 1 – 4. Use your Excel spreadsheet to answer questions 5 - 9
|
Possible Points |
Earned Points |
|
|
1. Describe how process costing is different than job costing. Identify at least three differences. |
3 |
|
|
2. Describe the two basic differences between the weighted average and the FIFO method. |
2 |
|
|
3. What is the purpose of a production cost report? |
2 |
|
|
4. Describe the concept of an equivalent unit and how to compute equivalent units. |
2 |
|
|
5. What is the total cost to be accounted for? |
1 |
|
|
6. How much cost will you transfer to the next WIP department using the FIFO method? |
2 |
|
|
7. What is the value of materials in beginning inventory at the start of NEXT month for the current process department using weighted average? |
2 |
|
|
8. Draw the WIP process T accounts for the two departments, mixing and baking, and show the cost flow from one to the other (do this for both methods – 4 T accounts) |
4 |
|
|
9. Do the journal entry transferring costs from one process to the next under both methods. |
2 |
|
|
Total |
20 |
In: Accounting
Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You project is to create a model for a production cost report using the weighted average method for the month of May. Following good Excel design techniques, you should have an input area in which you put the department information for the month, and an output area that calculates the production cost report. As always, you should have only formulas or references in your output area. You should also include directions (in a text box) for users.
After completion of the production cost report using the weighted average method, create a new worksheet and label the tab “FIFO”. Create a production cost report using the same information using the FIFO method.
Department Data:
|
Information about units: |
|
|
Units in beginning WIP |
21,000 |
|
Started during the month |
240,000 |
|
Units in Ending WIP |
20,000 |
|
Percentage of Completion: |
|
|
Beginning Inventory: |
|
|
Direct materials |
40% |
|
Conversion |
70% |
|
Ending Inventory: |
|
|
Direct Materials |
30% |
|
Conversion |
60% |
|
Costs in Beginning WIP: |
|
|
Direct materials |
$210,000 |
|
Conversion |
$135,000 |
|
Costs incurred during the month: |
|
|
Direct Materials |
$1,150,000 |
|
Direct Labor |
$45,000 |
|
Overhead |
$605,000 |
Process Cost Project – non-Excel
Answer questions 1 – 4. Use your Excel spreadsheet to answer questions 5 - 9
|
Possible Points |
Earned Points |
|
|
1. Describe how process costing is different than job costing. Identify at least three differences. |
3 |
|
|
2. Describe the two basic differences between the weighted average and the FIFO method. |
2 |
|
|
3. What is the purpose of a production cost report? |
2 |
|
|
4. Describe the concept of an equivalent unit and how to compute equivalent units. |
2 |
|
|
5. What is the total cost to be accounted for? |
1 |
|
|
6. How much cost will you transfer to the next WIP department using the FIFO method? |
2 |
|
|
7. What is the value of materials in beginning inventory at the start of NEXT month for the current process department using weighted average? |
2 |
|
|
8. Draw the WIP process T accounts for the two departments, mixing and baking, and show the cost flow from one to the other (do this for both methods – 4 T accounts) |
4 |
|
|
9. Do the journal entry transferring costs from one process to the next under both methods. |
2 |
|
|
Total |
20 |
In: Finance
Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You project is to create a model for a production cost report using the weighted average method for the month of May. Following good Excel design techniques, you should have an input area in which you put the department information for the month, and an output area that calculates the production cost report. As always, you should have only formulas or references in your output area. You should also include directions (in a text box) for users.
After completion of the production cost report using the weighted average method, create a new worksheet and label the tab “FIFO”. Create a production cost report using the same information using the FIFO method.
Department Data:
|
Information about units: |
|
|
Units in beginning WIP |
21,000 |
|
Started during the month |
240,000 |
|
Units in Ending WIP |
20,000 |
|
Percentage of Completion: |
|
|
Beginning Inventory: |
|
|
Direct materials |
40% |
|
Conversion |
70% |
|
Ending Inventory: |
|
|
Direct Materials |
30% |
|
Conversion |
60% |
|
Costs in Beginning WIP: |
|
|
Direct materials |
$210,000 |
|
Conversion |
$135,000 |
|
Costs incurred during the month: |
|
|
Direct Materials |
$1,150,000 |
|
Direct Labor |
$45,000 |
|
Overhead |
$605,000 |
Process Cost Project – non-Excel
Answer questions 1 – 4. Use your Excel spreadsheet to answer questions 5 - 9
|
Possible Points |
Earned Points |
|
|
1. Describe how process costing is different than job costing. Identify at least three differences. |
3 |
|
|
2. Describe the two basic differences between the weighted average and the FIFO method. |
2 |
|
|
3. What is the purpose of a production cost report? |
2 |
|
|
4. Describe the concept of an equivalent unit and how to compute equivalent units. |
2 |
|
|
5. What is the total cost to be accounted for? |
1 |
|
|
6. How much cost will you transfer to the next WIP department using the FIFO method? |
2 |
|
|
7. What is the value of materials in beginning inventory at the start of NEXT month for the current process department using weighted average? |
2 |
|
|
8. Draw the WIP process T accounts for the two departments, mixing and baking, and show the cost flow from one to the other (do this for both methods – 4 T accounts) |
4 |
|
|
9. Do the journal entry transferring costs from one process to the next under both methods. |
2 |
|
|
Total |
20 |
In: Finance
Dave Merrill vacationed at Lake Tahoe last winter. Unfortunately, he broke his ankle while skiing and spent two days at the Sierra University Hospital. Merrill's insurance company received a $4,400
bill for his two-day stay.
One item that caught Merrill's attention was an $11.75 charge for a roll of cotton. Merrill
is a salesman for Johnson & Johnson and knows that the cost to the hospital of the roll of cotton is between $2.15 to $2.95. He asked for a breakdown of the $11.75 charge. The accounting office of the hospital sent him the followinginformation:
|
a. |
Invoiced cost of cotton roll |
$2.35 |
|
b. |
Cost of processing paperwork for purchase |
0.58 |
|
c. |
Supplies-room management fee |
0.75 |
|
d. |
Operating-room and patient-room handling costs |
1.59 |
|
e. |
Administrative hospital costs |
1.14 |
|
f. |
University teaching-related costs |
0.60 |
|
g. |
Malpractice insurance costs |
1.25 |
|
h. |
Cost of treating uninsured patients |
2.89 |
|
i. |
Profit component |
0.60 |
|
Total |
$11.75 |
Merrill believes the overhead charge is outrageous. He comments, "There was nothing I could do about it. When they come in and dab your stitches, it's not as if you can say, 'Keep your cotton roll. I brought my own.'"
|
1. |
Compute the overhead rate Sierra University Hospital charged on the cotton roll. Begin by determining the formula, then calculate the overhead rate. |
|
2. |
What criteria might Sierra use to justify allocation of the overhead items b-i in the preceding list? Examine each item separately and match it to the appropriate criteria. |
|
3. |
What should Merrill do about the $11.75 charge for the cotton roll? |
In: Accounting
You have been asked to forecast the additional funds needed (AFN) for Houston, Hargrove, & Worthington (HHW), which is planning its operation for the coming year. The firm is operating at full capacity. Data for use in the forecast are shown below. However, the CEO is concerned about the impact of a change in the payout ratio from the 10% that was used in the past to 50%, which the firm's investment bankers have recommended. Based on the AFN equation, by how much would the AFN for the coming year change if HHW increased the payout from 10% to the new and higher level? All dollars are in millions.
|
Last year's sales = S0 |
$300.0 |
Last year's accounts payable |
$50.0 |
|
Sales growth rate = g |
40% |
Last year's notes payable |
$15.0 |
|
Last year's total assets = A0* |
$500.0 |
Last year's accruals |
$20.0 |
|
Last year's profit margin = PM |
20.0% |
Initial payout ratio |
10.0% |
|
a. |
$31.9 |
|
b. |
$33.6 |
|
c. |
$35.3 |
|
d. |
$37.0 |
|
e. |
$38.9 |
In: Finance
| investment | poor | average | good |
| A | 50 | 75 | 20 |
| B | 80 | 15 | 40 |
| C | -100 | 300 | 50 |
Maximax, Maximin, Minimax Regreat, Hurxicz 0.4, Equal liklihood
In: Operations Management
Periodic inventory by three methods; cost of goods sold The units of an item available for sale during the year were as follows: Jan. 1 Inventory 40 units at $124 Mar. 10 Purchase 50 units at $136 Aug. 30 Purchase 20 units at $142 Dec. 12 Purchase 90 units at $144 There are 60 units of the item in the physical inventory at December 31. The periodic inventory system is used. Determine the ending inventory cost and the cost of goods sold by three methods. Round interim calculations to one decimal and final answers to the nearest whole dollar. Cost of Ending Inventory and Cost of Goods Sold Inventory Method Ending Inventory Cost of Goods Sold First-in, first-out (FIFO) $ $ Last-in, first-out (LIFO) Weighted average cost PreviousNext
In: Accounting
Periodic Inventory by Three Methods; Cost of Merchandise Sold
The units of an item available for sale during the year were as follows:
| Jan. 1 | Inventory | 50 units @ $100 |
| Mar. 10 | Purchase | 40 units @ $112 |
| Aug. 30 | Purchase | 10 units @ $116 |
| Dec. 12 | Purchase | 100 units @ $122 |
There are 80 units of the item in the physical inventory at December 31. The periodic inventory system is used.
Determine the inventory cost and the cost of merchandise sold by three methods. Round interim calculations to one decimal and final answers to the nearest whole dollar.
| Cost of Merchandise Inventory and Cost of Merchandise Sold | ||
| Inventory Method | Merchandise Inventory | Merchandise Sold |
| First-in, first-out (FIFO) | $ | $ |
| Last-in, first-out (LIFO) | ||
| Weighted average cost | ||
In: Accounting
Periodic inventory by three methods; cost of goods sold
The units of an item available for sale during the year were as follows:
| Jan. 1 | Inventory | 50 units at $118 |
| Mar. 10 | Purchase | 60 units at $128 |
| Aug. 30 | Purchase | 20 units at $132 |
| Dec. 12 | Purchase | 70 units at $136 |
There are 40 units of the item in the physical inventory at December 31. The periodic inventory system is used.
Determine the ending inventory cost and the cost of goods sold by three methods. Round interim calculations to one decimal and final answers to the nearest whole dollar.
| Cost of Ending Inventory and Cost of Goods Sold | ||
| Inventory Method | Ending Inventory | Cost of Goods Sold |
| First-in, first-out (FIFO) | $ | $ |
| Last-in, first-out (LIFO) | ||
| Weighted average cost | ||
In: Accounting
Can you show me how to do questions d, i, ii, iii and iv .
You have following liability: 29-year bond, 6.5% annual coupon, market interest rate is 5%.
a. What is the present value of this liability? PV = 1227.12
b. What is the duration of this liability? Duration = 15.12
d. You want to consider immunizing the liability using 8-year and 30- year zero coupon-bonds.
i. What are the investment weights needed for the two bonds?
ii. What are the present values of the two bonds needed to immunize the liability?
iii. What are the face values of the two bonds needed to immunize the liability?
iv. Build a sensitivity table showing the results of changes in interest rates:
|
Weight |
3% |
4% |
5% |
6% |
7% |
|
|
Liablility |
||||||
|
Bond (8 yrs) |
||||||
|
Bond (30 yrs) |
||||||
|
Portfolio sum |
In: Finance