Accounting

1_MBA2.xlsx

Home >Business & Finance homework help >Accounting homework help >Accounting

InstructionsProject5

Name: Type your name here
Project 5	Save your file using your first initial, last name, and name of project.
Upload to project 5 link.
This project covers material in Modules 21 and 22. I have extensive budget demonstrations in the Module 22 folder.
Project 5 Objectives:
1. Develop cost-based approach to pricing.
2. Develop operating budgets
3. Analyze the cash flow of the company.
4. Analyze how changes in cost impact the budgets.
Grading Rubric:
Part 1
Each question worth 2 pts. each		10
Part 2
Budget 1	1
Budget 2	2
Budget 3	3
Budget 4	3
Budget 5	3
Budget 6	3
Budget 7	3
Question 8	1
Question 9	1
Total pts. possible for Part 2		20
Total points possible for project 5		30
Please note that I will deduct 10 pts. if you do not use cell referencing on your budget solution by referencing the Part 2 worksheet.

Part 1

Part 1 Relates to Module 21
Technology Inc. predicted 2017 variable and fixed costs are as follows:
		Variable costs	Fixed costs
	Manufacturing	480,000	315900
	Selling and Administrative	216,000	60500
	Total	696,000	376,400
Technology Inc. produces a wide variety of computer interface devices. Per unit
manufacturing cost information about one of these products, a high-capacity flash drive is as follows:
	Direct material	$10
	Direct labor	9
	Variable Manufacturing Overhead	7
	Fixed Manufacturing Overhead	9
	Total manufacturing costs	$35
The following is the variable selling and administrative costs for the flash drive:				$6
Management has set a 2017 target profit on the flash drive of:				$250,000
Required:
1. Determine the markup percentage on variable costs required to earn the desired profit
2. Use the variable cost markup to determine a suggested selling price for a flash drive. You are determining selling price per unit)
3. For the flash drive, break the markup on variable costs into separate parts for fixed costs and profit.
4. Explain what the minimum unit selling price a company would use in special order decision, if the company had excess capacity.
5. In the long run, what would be the lowest unit selling price the company would sell for? Explain your answer.

Part 2

Part 2 relates to Module 22 especially the Review 22-4
Computer Accessories assembles a computer networking device from kits of imported components.
You have been asked to develop a quarterly and annual operating budget and a pro-forma income statements for the year ending December 31, 2017.
You have obtained the following information:
I have a template set up on the Part2BudgetSolution worksheet that you should use to complete the required budgets.
You need to use cell references in the development of your budgets.
You must use this worksheet to reference the data that is being inputted onto the budgets on the budget worksheet.
If you type in any numbers in the solution, I will take off 10 pts., since we use Excel so that we can update budgets or do what if analysis without retyping numbers.
You should use this worksheet as your data field and only use cell references and formulas in your budgets.
Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete.
The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you
have used cell references and formulas throughout.
Beginning-of-year balances
	Cash			$50,000
	Accounts receivables (previous quarter's sales)			$61,200
	Raw materials			653	Kits
	Finished Goods			510	Units
	Accounts payable			$33,255
Desired end-of-year inventory balances
	Raw materials			500	kits
	Finished goods			270	units
Desired end-of-quarter balances
	Raw materials as a portions of the following quarter's production			20%
	Finished goods as a portion of the following quarter's sales			15%
Manufacturing costs other than raw materials are paid in month incurred unless it is an noncash expense
	Variable Standard cost per unit	Unit of input		Unit price per input	Total cost per unit
	Raw materials	1	kit	$50	$50
	Direct labor hours at rate	0.8	hour	$25	$20
	Variable overhead/labor hour	0.8	hour	$10	$8
	Total Variable Standard cost per unit				$78
	Fixed overhead cost per quarter used cash			$50,000
	Manufacturing Depreciation per quarter			$10,000
Selling and administrative costs are paid in month incurred unless it is an noncash expense
	Variable cost per unit			$6
	Fixed selling and administrative cost per quarter used cash			$25,000
	Selling and administrative depreciation per quarter			$5,000
Additional information: All cash payments except purchases are made quarterly as incurred.
Portion of sales collected
	Collected in the quarter of sale			75%
	Subsequent quarter			24%
	Bad debts			1%
Portion of purchases paid
	Paid in the quarter of purchases			70%
	Subsequent quarter			30%
Unit selling price				$150
Sales forecast
	Quarter	First	Second	Third	Fourth
	Unit sales	3,400	2,500	3,000	4,100
Required: Prepare and answer the following. Make sure you use cell referencing
1. A sales budget for each quarter and the year.
2. A production budget for finished goods of units each quarter and the year.
3. A purchases budget for raw material of kits each quarter and the year.
4. A manufacturing cost budget for each quarter and the year.
5. A selling and administrative expense budget for each quarter and the year.
6. A cash budget for each quarter and the year.
7. A pro-forma contribution income statement for each quarter and the year.
Hint: You will need to compute Variable Cost of Goods Sold for each quarter, which is unit sold times total Variable Standard cost per unit.
8. Using your information from #7, compute the Breakeven in dollars for the year. Make sure you are using cell references.
Hint: Compute the annual contribution margin ratio.
9. What if the company is able to lower the fixed Manufacturing overhead costs that uses cash per quarter from $50,000 to $45,000. Which budgets will change and what will be the new annual income?
You should only have to change the fixed manufacturing overhead costs that uses cash on this worksheet and all the appropriate budgets will change on the solution worksheet if you have
set up your cell references correctly. Please make sure you return the Fixed manufacturing overhead costs that uses cash back to the original number before you submit your solution.

Part2BudgetSolution

Budget #1			Computer Accessories
			Sales Budget
			For the year ending December 31, 2017
		First Quarter	Second Quarter	Third Quarter	Fourth Quarter	Annual Total
	Units	3,400 Cynthia Nye: Guidance: I have filled in a couple of cells to get you started. Please make sure you use cell references using the Part 2 worksheet information.
	Sales price	$150
	Sales Budget	$510,000
Budget #2			Computer Accessories
			Production Budget
			For the year ending December 31, 2017
		First Quarter	Second Quarter	Third Quarter	Fourth Quarter	Annual Total
	Unit sales	3,400
	Desired Ending Inventory
	Total Requirements
	Less Beginning Inventory
	Budgeted Production in units
Budget #3			Computer Accessories
			Purchases Budget
			For the year ending December 31, 2017
		First Quarter	Second Quarter	Third Quarter	Fourth Quarter	Annual Total
	Budgeted production in units
	Desired ending inventory of kits
	Total Requirements
	Less Beginning Inventory Kits
	Purchase requirements in kits
	Cost per kit
	Total purchases of kits in dollars
Budget #4			Computer Accessories
			Manufacturing Cost Budget
			For the year ending December 31, 2017
		First Quarter	Second Quarter	Third Quarter	Fourth Quarter	Annual Total
	Direct Materials:
	Budgeted production in units
	Cost per kit
	Direct material cost
	Direct labor:
	Budgeted production in units
	Direct labor cost per unit
	Direct labor cost
	Variable Manufacturing Overhead:
	Budgeted production in units
	Variable Overhead cost per unit
	Variable Manufacturing Overhead
	Fixed Manufacturing Overhead:
	Total Manufacturing Costs
Budget #5			Computer Accessories
			Selling and Administrative Expenses Budget
			For the year ending December 31, 2017
		First Quarter	Second Quarter	Third Quarter	Fourth Quarter	Annual Total
	Budgets Sales Units from Budget #1	3,400
	Budgets Sales Dollars from Budget #1
	Variable selling and admin. expenses:
	Bad Debts
	Variable selling and admin. expenses
	Total variable selling and admin. Expenses
	Fixed Selling and administrative Expenses
	Total selling and administrative expenses
Budget #6			Computer Accessories
			Cash Budget
			For the year ending December 31, 2017
		First Quarter	Second Quarter	Third Quarter	Fourth Quarter	Annual Total
	Cash Balance, Beginning
	Collection on sales:
	Collected in current quarter
	Collected in subsequent quarter
	Total collection on sales
	Cash available for operations
	Cash Disbursements:
	Purchases:
	Paid in Current quarter
	Paid in subsequent quarter
	Direct labor
	Variable Manufacturing Overhead
	Fixed Manufacturing Overhead
	Variable selling and administrative expenses
	Fixed selling and administrative expenses
	Total Disbursements
	Cash Balance, Ending
Budget #7	Hint: You will need to compute Variable Cost of Goods Sold for each quarter, which is unit sold times total Variable Standard cost per unit.
			Computer Accessories
			Pro Forma Contribution Margin
			For the year ending December 31, 2017
		First Quarter	Second Quarter	Third Quarter	Fourth Quarter	Annual Total
	Sales Revenue
	Less Variable Costs:
	Cost of Goods sold
	Selling and Administrative expenses
	Total Variable costs
	Contribution margin
	Less Fixed Costs:
	Manufacturing Overhead
	Selling and Administrative Expenses
	Total Fixed Costs
	Net Income
8. Using your information from #7, compute the Breakeven in dollars for the year. Make sure you are using cell references.
Hint: Compute the annual contribution margin ratio.
Contribution margin ratio
Breakeven in Dollars
9. What if the company is able to lower the fixed Manufacturing overhead costs that uses cash per quarter from $50,000 to $45,000. Which budgets will change and what will be the new annual income?
You should only have to change the fixed manufacturing overhead costs that uses cash on this worksheet and all the appropriate budgets will change on the solution worksheet if you have
set up your cell references correctly. Please make sure you return the Fixed manufacturing overhead costs that uses cash back to the original number before you submit your solution.
The following budgets would change:
New net income: