Please solve the following problem using Excel commands

User Generated

cnqneb1975

Business Finance

Description

Please see the attached 3 documents: the first one is the problem you will work on. The second one is the step by step instructions, and the third one is the solution of the same typical problem solvedproblem to be solved.docx step by step instruction.pdf example typical problem solved.pdf 

Unformatted Attachment Preview

Huron chalk company manufacturers side walk chalk which it sells online by the box at $25 per unit. Huron uses an actual costing system, which means that the actual costs consist of direct material, direct labor , and manufacturing overhead are entered into work-in-process inventory. The actual application rate of manufacturing overhead is computed each year; actual manufacturing over head is divided by actual production (in units) to compute the application rate. Information on Huron’s first two years of operations is as follows: year 1 year 2 Sales (in units) 2500 2500 Production in units 3000 2000 Production costs: Variable manufacturing costs $ 10500 $7000 Fixed manufacturing overhead 21000 21000 12500 12500 10000 10000 Selling administrative expenses: Variables Fixed Question 1 : what was huron’s total operating income across both years under absorption costing and under variable costing? Question 2: what was the total sales revenue across both years under absorption costing and under variable costing? Question 3: what was the total of all costs expensed on the operating income statements across both years under absorption costing and under variables costing? Ginn Tips on Activity 6.4 Dr. Ginn’s Tips for Doing Case 8-43 2, 3, 4 A. Please check the data file in the questions and answers discussion forum. Make sure you are using the right data to solve the problem. B. Consider the fundamental concepts you are expected to demonstrate by working this problem. One major concept that is revealed in this problem is that there is a tradeoff between having a more accurate balance sheet or a more accurate income statement. The fact is that the balance sheet and the income statement articulate, so improvements to one may be to the detriment of the other. Most agree that the balance sheet is forward looking because it reflects the accumulated results of operations over the years. In contrast, the income statement is very short term oriented because it focuses only on what happened in that period. The purpose of this case is to show that, over time, absorption cost and variable cost give the same results. However, it demonstrates that the matching of revenues and expenses under absorption costing leads to temporary differences in operating income. Absorption costing might produce better inventory valuations for the balance sheet, but this causes somewhat distorted calculations of net income. Thus, this case reinforces the concept of matching revenues and expenses with use of inventory as a buffer to control recognition of expenses. C. Find a model in the chapter that provides an example of the application of both absorption costing and variable costing. You might even find a model on the internet. Often there are videos on the internet. D. Lay out the problem. Your task is to assemble the data in examples of both absorption and variable costing. E. Use Excel to lay out the data. Begin by opening an Excel file. Label a worksheet as “data given” and enter the data given in the text in the worksheet. F. Analyze the calculations in both absorption costing and variable costing. To do this, you need to compare the physical flow of goods depicted by volume with the financial flow depicted by the data given to you. What you will find is calculations of both absorption costing rates and variable costing rates that can be applied to the volume to make income statements based on either cost assumptions. G. Using Excel Make a data given worksheet. Add the variable costs and fixed costs, then divide that total by the number of units produced to get the absorption cost rate for each of the two years. H. Calculate the Income statements for two years using both absorption costing and variable costing. I. Using Excel Copy the data from the data given worksheet onto a new worksheet labeled “operating income.” Add one new column in each of the two years under absorption costing and variable costing reflecting actual volume for each revenue or expense to assist you in calculating operating income under absorption costing. The reason you are doing this is so you can visualize the actual volume of physical flow and enter them in a column adjacent to the values column. Remember that volume multiplied by the absorption cost rate (or the variable cost rate) gives you the dollar accounting values. One concept that you to demonstrate is that you start with zero beginning inventory in year one because it is their first year of operations. You can see that they produce 3000 units in year one but only sell 2500. So what was the ending inventory of year 1? That same ending inventory becomes the beginning inventory of year 2 where they sell 2500 units but only produce 2000. So what was the ending inventory for year two? Thus, you need to first enter or calculate the physical volumes for sales revenue, cost of goods sold, cost of good manufactured, cost of goods available for sale, ending inventory, and cost of goods sold. Next you calculate the dollar value of all these accounts by multiplying by the relevant cost rate (i.e. absorption or variable). For example, calculate the sales values by multiplying sales volume by sales price for each year. J. Complete the analysis even if you have doubts. K. Take a break and allow your brain to synthesize. L. Note that the text provides a check figure of $13,750 for operating income using absorption costing for year 1. Also the check figure for absorption costs is $104,500 expensed across both years. Check to see if total sales revenues for two years are the same under absorption costing and variable costing. Check to see if total expenses for two years are the same under absorption costing and variable costing. M. You need to review and amend your work until you get these results. If you cannot do so, you can still turn it in for partial credit if you have laid out the data and finished the analysis. Hudson Hornet Replicas Year 1 Year 2 Sales (in units) Production (in units) 3500 4000 3500 3000 Beginning Inventory Ending Inventory 0 1000 1000 0 Production costs: Variable manufacturing costs Fixed manufacturing overhead $ $ 15,500.00 25,000.00 $ $ 12,000.00 25,000.00 Selling and administrative expenses Variable Fixed $ $ 12,500.00 14,000.00 $ $ 12,500.00 14,000.00 Sales Price Per Unit $ 25.00 $ 25.00 Actual costs of direct material, direct labor, and manufacturing overhead are entered into work in processing. Application rate is actual manufacturing overhead divided by actual production. Physical Flow Hudson Hornet Replicas Absorption Costing Sales price per unit Absorption rate per unit Sales (in units) Production (in units) Beginning Inventory Cost of Goods Manufactured Cost of Goods Available for Sale Ending inventory Cost of Goods Sold Hudson Hornet Replicas Variable Costing Sales price per unit Variable cost rate per unit Year 1 Year 2 3500 3500 Sales Revenue 4000 3000 Less: Cost of Goods Sold 0 1000 Beginning inventory 4000 3000 Cost of goods manufactured 4000 4000 Cost of goods available for sale 1000 0 Ending inventory 3000 4000 Cost of goods sold Year 1 Year 2 3500 3500 4000 3000 0 1000 4000 3000 4000 4000 500 0 3500 4000 Production (in units) Production costs: Variable manufacturing costs Fixed manufacturing overhead 4000 3000 $ 15,500.00 $ 12,000.00 $ 25,000.00 $ 25,000.00 Actual costs of direct material, direct labor, and manufacturing overhead are entered into WIP. Application rate is actual manufacturing overhead divided by actual production. absorption cost rate $ 10.13 $ 12.33 Production (in units) Production costs: Variable manufacturing costs Fixed manufacturing overhead 4000 3000 $ 15,500.00 $ 7,000.00 $ 25,000.00 $ 25,000.00 Actual costs of direct material, direct labor, and manufacturing overhead are entered into work in processing. Application rate is actual manufacturing overhead divided by actual production. variable cost rate $ 3.88 $ 2.33 2) What was Hudson Hornet Replicas total operating income across both years under absorption costing and under variable costing? Hudson Hornet Replicas Absorption Costing Sales price per unit Absorption rate per unit Sales (in units) Production (in units) Beginning Inventory Cost of Goods Manufactured Cost of Goods Available for Sale Ending inventory Cost of Goods Sold Gross Margin Selling and administrative expenses: Variable Fixed Operating Income Year 1 3500 4000 0 4000 4000 1000 2500 Year 2 $ 0 $ $ $ $ $ - $ 12,500.00 $ 14,000.00 3500 3000 1000 3000 4000 0 4000 $ - $ - $ $ $ - $ 3,000.00 $ $ - $ $ 12,500.00 $ 14,000.00 $ 25,000.00 28,000.00 0 d under variable costing? Hudson Hornet Replicas Variable Costing Sales price per unit Variable cost rate per unit Year 1 Sales Revenue Less: Cost of Goods Sold Beginning inventory Cost of goods manufactured Cost of goods available for sale Ending inventory Cost of goods sold Variable selling and administrative expenses Total Variable Costs Contribution margin less Fixed costs Manufacturing Selling and Administrative Total Fixed Costs Operating income Year 2 3500 $ 4000 $ 3000 3000 $ 500 $ 2500 $ $ 12,500.00 $ 12,500.00 $ (12,500.00) $ 25,000.00 $ 14,000.00 $ 39,000.00 3500 $ $ 3000 $ 3000 4000 $ $ 0 $ $ 4000 $ $ 12,500.00 $ 12,500.00 $ (12,500.00) $ 25,000.00 $ 14,000.00 $ 39,000.00 - - 2) What was total operating income across both years under absorption costing and under variable costing? Hudson Hornet Replicas Absorption Costing Sales price per unit $ 25.00 $ Absorption rate per unit $ 10.13 $ Year 1 Sales (in units) Production (in units) Beginning Inventory Cost of Goods Manufactured Cost of Goods Available for Sale Ending inventory Cost of Goods Sold Gross Margin Selling and administrative expenses: Variable Fixed Operating Income 25.00 12.33 Year 2 3500 4000 0 4000 4000 1000 2500 $ 87,500.00 $ $ $ 40,500.00 40,500.00 $ 10,125.00 $ $ 30,375.00 57,125.00 $ $ $ 3500 3000 1000 3000 4000 0 4000 $ $ 87,500.00 $ 175,000.00 10,125.00 $ $ $ $ $ 37,000.00 $ 47,125.00 47,125.00 40,375.00 80,500.00 12,500.00 14,000.00 $ $ 12,500.00 $ 14,000.00 $ 25,000.00 28,000.00 30,625.00 $ 13,875.00 $ 44,500.00 Hudson Hornet Replicas Variable Costing Sales price per unit Variable cost rate per unit $ $ 25.00 3.88 $ 87,500.00 $ $ $ 15,500.00 15,500.00 $ 3,875.00 $ $ $ $ 11,625.00 12,500.00 24,125.00 63,375.00 Manufacturing Selling and Administrative Total Fixed Costs $ $ $ Operating income $ Year 1 Sales Revenue Less: Cost of Goods Sold Beginning inventory Cost of goods manufactured Cost of goods available for sale Ending inventory Cost of goods sold Variable selling and administrative expenses Total Variable Costs Contribution margin less Fixed costs $ $ 25.00 2.33 $ 87,500.00 $ $ 3,875.00 $ $ $ $ $ $ $ 12,000.00 15,875.00 $ $ 15,875.00 12,500.00 28,375.00 59,125.00 25,000.00 14,000.00 39,000.00 $ $ $ 25,000.00 14,000.00 39,000.00 24,375.00 $ 20,125.00 Year 2 3500 4000 0 3000 3000 1000 2500 3500 3000 1000 3000 4000 0 4000 175,000.00 31,375.00 3,875.00 $ 44,500.00 3) What was the total sales revenue across both years under absorption costing and under variable costing? Absorption Costing Hudson Hornet Replicas price per unit $ 25.00 $ 25.00 Year 1 Sales in dollars Sales (in units) Production (in units) Year 2 $ 87,500.00 3500 4000 $ 87,500.00 3500 3000 $ 175,000.00 Variable Costing Hudson Hornet Replicas price per unit $ 25.00 Year 1 Sales in dollars Sales (in units) Production (in units) $ 25.00 Year 2 $ 87,500.00 3500 4000 $ 87,500.00 3500 3000 $ 175,000.00 4) What was total costs across both years under absorption costing and under variable costing? Huron Chalk Company Absorption Costing Sales price per unit $ 25.00 $ 25.00 Year 1 Sales (in units) 3500 $ 87,500.00 Production (in units) 4000 rate $ 11.57 Cost of Sales $ 40,500.00 Production costs: Variable manufacturing costs $ 15,500.00 Fixed manufacturing overhead $ 25,000.00 Total manufacturing expenses $ 40,500.00 Selling and administrative expenses: Variable $ 12,500.00 Fixed $ 14,000.00 Operating Income $ 20,500.00 total costs expensed Year 2 $ 3500 $ 87,500.00 $ 3000 8.00 $ 28,000.00 $ 175,000.00 68,500.00 $ 7,000.00 $ 21,000.00 $ 28,000.00 $ 12,500.00 $ $ 10,000.00 $ $ 37,000.00 $ 25,000.00 24,000.00 57,500.00 $ 117,500.00 Huron Chalk Company Variable Costing Sales price per unit $ 25.00 Year 1 $ 25.00 Year 2 Sales (in units) Production (in units) Variable manufacturing costs Variable selling and administrative expenses Total Variable Expenses Contribution Margin 3500 $ 87,500.00 3500 $ 87,500.00 $ 4000 3000 $ 15,500.00 $ 7,000.00 $ 12,500.00 $ 12,500.00 $ 28,000.00 $ 19,500.00 $ $ 59,500.00 $ 68,000.00 $ Fixed manufacturing overhead Fixed Selling and administrative expenses Total Fixed Expenses $ 25,000.00 $ 14,000.00 Operating Income 175,000.00 47,500.00 127,500.00 $ 21,000.00 $ 10,000.00 $ 39,000.00 $ 31,000.00 $ 70,000.00 $ 20,500.00 $ 37,000.00 $ 57,500.00 $ 117,500.00
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer


Anonymous
I was stuck on this subject and a friend recommended Studypool. I'm so glad I checked it out!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags