Accounting Excel
student06
Servicelearningcosttemplate.xlsxfunctional
| Data | |
| Candy Packets - Sales Price | 40 |
| Donations | 5 |
| Units: | |
| Sales Packet | 20 |
| Donation Units | 10 |
| Direct Material: Candy Packet - QTY | 1 |
| Direct Material: Candy Packet - Price | 2 |
| Direct Labor: Candy Packet - Time | 0.15 |
| Direct Labor: Candy Packet - Rate | 2 |
| Overhead: | |
| Candy Prizes - Qty | 0.2 |
| Candy Prizes - Rate | 25 |
| Candy Kits - Variable QTY | 2 |
| Candy Kits - Variable Rate | 1 |
| Amazing Race Costs | 500 |
| Holiday Around the World Costs | 500 |
| Selling and Administrative Costs | 100 |
| Total Units | 20 |
| Candy Packet in Units | 20 |
| Donations in Units | 10 |
| Total Units | 30 |
| Total Sales - Packets | |
| Total Sales - Donations | |
| Total Sales | |
| Cost of Goods Sold: | |
| Direct Materials - Packets | |
| Direct Labor - Packets | |
| Overhead - Packets | |
| Total Cost of Goods Sold | |
| Gross Profit | |
| Selling and Administrative Costs | |
| Net Income | |
CM Style
| Data | |||
| Candy Packets - Sales Price | 40 | ||
| Donations | 5 | ||
| Direct Material: Candy Packet - QTY | 1 | ||
| Direct Material: Candy Packet - Price | 2 | ||
| Direct Labor: Candy Packet - Time | 0.15 | ||
| Direct Labor: Candy Packet - Rate | 2 | ||
| Overhead: | |||
| Candy Prizes - Qty | 0.2 | ||
| Candy Prizes - Rate | 25 | ||
| Candy Kits - Variable QTY | 2 | ||
| Candy Kits - Variable Rate | 1 | ||
| Amazing Race Costs | 500 | ||
| Holiday Around the World Costs | 500 | ||
| Selling and Administrative Costs | 100 | ||
| Total Units | 20 | ||
| Candy Packet in Units | 20 | ||
| Donations in Units | 10 | ||
| Total Units | 30 | ||
| Label Cost Fixed or Variable | Label Cost Product or Period | ||
| Total Sales - Packets | $ 800 | ||
| Total Sales - Donations | $ 50.00 | ||
| Total Sales | $ 800.00 | ||
| Cost of Goods Sold: | |||
| Direct Materials - Packets | $ 40.00 | ||
| Direct Labor - Packets | $ 6.00 | ||
| Overhead - Packets | $ 1,140.00 | ||
| Total Cost of Goods Sold | $ 1,186.00 | ||
| Gross Profit | $ (386.00) | ||
| Selling and Administrative Costs | $ 100.00 | ||
| Net Income | $ (486.00) | ||
| High/Low Calculation: | |||
| Contribution Margin Format Income Statement | |||
| 40 Combined Teams | Break Even | ||
| Total Units | 20 | 40 | |
| Candy Packet in Units | 20 | 40 | |
| Donations in Units | 10 | 20 | |
| Total Units | 30 | 60 | |
| Total Sales - Packets | $ 800 | ||
| Total Sales - Donations | $ 50.00 | ||
| Total Sales | $ 800.00 | ||
| Variable Costs | |||
| Direct Materials - Packets | $ 40.00 | ||
| Direct Labor - Packets | $ 6.00 | ||
| Overhead - Packets - Variable | $ 140.00 | ||
| Total Variable Costs | $ 186.00 | ||
| Contribution Margin | $ 614.00 | ||
| Fixed Costs - OH | $ 1,000.00 | ||
| Fixed Costs - S&A | $ 100.00 | ||
| Total Fixed Costs | $ 1,100.00 | ||
| Net Income | $ (486.00) | ||
| Use CVP analysis for multiple products to create a Revenue Equation for the project and determine the breakeven point. | |||
| Calculate the safety margin in units and dollars, calculate the degree of Operating leverage, and calculate the weighted average contribution margin at 40 combined teams. | |||
| Using the DOL, if sales increase by 10% what impact would that have on net income? Show Proof of your answer by creating the income statements at the two levels. | |||
Process Costing
| Process Costing | ||||
| We are making 50 candy kits to be given to the children | ||||
| Material added at the beginning of the prcoess | 100% | |||
| Ending units are 50% complete | 50% | |||
| Data: | ||||
| Beginning WIP | 10 | |||
| Started | 100 | |||
| Completed | 50 | |||
| Ending Units | Find the ending units | |||
| Costs | ||||
| Beginning Material costs | $ 20.00 | |||
| Current Material Costs | $ 200.00 | |||
| Beginning Conversion Costs | $ 50.00 | |||
| Current Conversion Costs | $ 150.00 | |||
| Total | $ 420.00 | |||
| Step 1: Flow | WIP | |||
| Completed | ||||
| Beg | ||||
| Units Started | ||||
| Ending | ||||
| Step 2: Calculate EU | DM | CC | ||
| Completed | ||||
| Ending | ||||
| EU | ||||
| Step 3: Costs | DM | CC | ||
| Beginning Costs | ||||
| Current Costs | ||||
| Total Costs | ||||
| Step 4 Costs per EU | DM | CC | Total | |
| Total Costs | ||||
| EU | ||||
| Costs per EU | ||||
| Step 5: Cost Assignment | Completed | |||
| Completed | ||||
| DM | ||||
| CC | ||||
| Total | ||||
| or | ||||
| Completed | ||||
| Ending units | ||||
| DM | ||||
| CC | ||||
| Total | ||||
Overhead
| Overhead Calculation | |||||
| Overhead: | |||||
| Candy Prizes - Qty | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 |
| Candy Prizes - Rate | 25 | 25 | 25 | 20 | 25 |
| Candy Kits - Variable QTY | 2 | 2 | 2 | 3 | 2 |
| Candy Kits - Variable Rate | 1 | 1 | 1 | 1 | 1 |
| Amazing Race Costs | 500 | 500 | 500 | 400 | 500 |
| Holiday Around the World Costs | 500 | 500 | 500 | 500 | 500 |
| Master | Actual | Flexible | |||
| Total Units | 20 | 30 | 40 | 42 | 42 |
| Candy Packet in Units | 20 | 30 | 40 | 42 | 42 |
| Donations in Units | 10 | 15 | 20 | 21 | 21 |
| Total Units | 30 | 45 | 60 | 63 | 63 |
| Total Sales - Packets | $ 800 | $ 1,200 | $ 1,600 | $ 1,680 | $ 1,680 |
| Total Sales - Donations | $ 50.00 | $ 75.00 | $ 100.00 | $ 105.00 | $ 105.00 |
| Total Sales | $ 800.00 | $ 1,200.00 | $ 1,600.00 | $ 1,680.00 | $ 1,680.00 |
| Variable Costs | |||||
| Direct Materials - Packets | $ 40.00 | $ 60.00 | $ 80.00 | $ 63.00 | $ 84.00 |
| Direct Labor - Packets | $ 6.00 | $ 9.00 | $ 12.00 | $ 16.80 | $ 12.60 |
| Overhead - Packets - Variable | $ 140.00 | $ 210.00 | $ 280.00 | $ 294.00 | $ 294.00 |
| Total Variable Costs | $ 186.00 | $ 279.00 | $ 372.00 | $ 373.80 | $ 390.60 |
| Contribution Margin | $ 614.00 | $ 921.00 | $ 1,228.00 | $ 1,306.20 | $ 1,289.40 |
| Fixed Costs - OH | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 900.00 | $ 1,000.00 |
| Fixed Costs - S&A | $ 100.00 | $ 100.00 | $ 100.00 | $ 100.00 | $ 100.00 |
| Total Fixed Costs | $ 1,100.00 | $ 1,100.00 | $ 1,100.00 | $ 1,000.00 | $ 1,100.00 |
| Net Income | $ (486.00) | $ (179.00) | $ 128.00 | $ 306.20 | $ 189.40 |
| 1. We only allocate fixed overhead | |||||
| 2. Amazing Race Costs Allocated Based on Direct Labor Costs | |||||
| 3. Holiday Around the World Based on Direct Material Costs | |||||
| 3. Assume 30 Teams or packets | |||||
| Fixed OH - Amazing Race | 500 | ||||
| Fixed OH - Holidays Around the World | 500 | ||||
| Total | 1000 | ||||
| Cost Driver - AR - Direct Labor | $ 9.00 | ||||
| Cost Driver - HAW - Direct Materials | $ 60.00 | ||||
| Amazing Race | Holidays Around the World | ||||
| Overhead Costs | |||||
| Cost Driver | |||||
| Rate | |||||
| If you have a job making 30 packets how much overhead is applied | |||||
| DL Costs | $ 9.00 | ||||
| DM Costs | $ 60.00 | ||||
| Applied OH | |||||
| If you have a job making 20 packets how much overhead is applied | |||||
| DL Costs | $ 6.00 | ||||
| DM Costs | $ 40.00 | ||||
| Applied OH |
