Question

Directions: Complete requirements 1-3 below. The primary purpose of this assignment is to apply your basic Excel skills to the topics covered in Chapters 8. In every cell that requires an entry, you are required to use a cell reference or formula. Do not just enter an amount into a cell. Your grade will be based on the use of cell references and formulas, as well as the accuracy of your answers. Requirement 1 Using the Excel worksheet that appears in Sheet 2, recreate the Review Problem related to Mynor Corporation Jon pages 362-363. In Sheet 2, you are required to enter a cell reference or formula in every cell with a "?". Do Inot just enter an amount, as that will defeat the purpose of this Excel assignment. Your answers in Sheet 2 should match the answers on pages 362 - 363 before you proceed to the next requirement. Requirement 2 Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The total expected cash collections for the year should now be $2,085,000. If you do not get this answer, find the Jerrors in your worksheet and correct them. Requirement 3 The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price per unit from $8 to $7. The marketing manager would like to use the following projections in the budget:

Answer 1

Numerical solution

Sales Budget
			Year 2 quarter				Year 3 quarter
Particulars			1	2	3	4	1	2
Sales			40000	60000	100000	50000	70000	80000
Selling price per unit			$              8.00	$              8.00	$              8.00	$              8.00	$                 8.00	$             8.00
Total sales			$ 320,000.00	$ 480,000.00	$ 800,000.00	$ 400,000.00	$     560,000.00	$ 640,000.00
Schedule of expected cash collections
			Year 2 quarter
Particulars			1	2	3	4	Total
A/R, beginning balance			$    65,000.00				$       65,000.00
Q1 sales			$ 240,000.00	$    80,000.00			$     320,000.00
Q2 sales				$ 360,000.00	$ 120,000.00		$     480,000.00
Q3 sales					$ 600,000.00	$ 200,000.00	$     800,000.00
Q4 sales						$ 300,000.00	$     300,000.00
Total cash collections			$ 305,000.00	$ 440,000.00	$ 720,000.00	$ 500,000.00	$ 1,965,000.00
Production Budget
			Year 2 quarter				Year 3 quarter
Particulars			1	2	3	4	1	2
Budgeted unit sales			40000	60000	100000	50000	70000	80000
Add: Deired ending inventory			18000	30000	15000	21000	24000
Total needs			58000	90000	115000	71000	94000
Less: beginning inventory			12000	18000	30000	15000	21000
Required production			46000	72000	85000	56000	73000
Raw material purchase budget
			Year 2 quarter				Year 3 quarter
Particulars			1	2	3	4	1	2
Required production			46000	72000	85000	56000	73000
R.M req. to produce one unit			5	5	5	5	5
Production needs (pounds)			230000	360000	425000	280000	365000
Add: Desired ending inv. Of R.M			36000	42500	28000	36500
Total needs (pounds)			266000	402500	453000	316500
Less: Beginning inv. Of R.M			23000	36000	42500	28000
R.M to be purchased			243000	366500	410500	288500
Cost of R.M per pound			$              0.80	$              0.80	$              0.80	$              0.80
Cost of R.M to be purchased			$ 194,400.00	$ 293,200.00	$ 328,400.00	$ 230,800.00
Schedule of expected cash payments
			Year 2 quarter
Particulars			1	2	3	4	Year
A/P, beginning balance			$    81,500.00				$       81,500.00
Q1 purchase			$ 116,640.00	$    77,760.00			$     194,400.00
Q2 purchase				$ 175,920.00	$ 117,280.00		$     293,200.00
Q3 purchase					$ 197,040.00	$ 131,360.00	$     328,400.00
Q4 purchase						$ 138,480.00	$     138,480.00
Total cash disbursements			$ 198,140.00	$ 253,680.00	$ 314,320.00	$ 269,840.00	$ 1,035,980.00

Formulas used:

Sales Budget Year 2 quarter Year 3 quarter 40000 60000 100000 50000 70000 80000 Particulars Sales Selling price per unit Total sales =E6*E7 =F6*F7 =G6*G7 =H6*H7 =16*17 =J6*17 Schedule of expected cash collections Year 2 quarter 65000 =E8*75% Particulars A/R, beginning balance Q1 sales Q2 sales Q3 sales Q4 sales Total cash collections =E8*25% =F8*75% =F8*25% =G8*75% Total =SUM(E14:H14) =SUM(E15:H15) =SUM(E16:H16) =SUM(E17:H17) =SUM(E18:H18) =SUM(E19:H19) =G8*25% =H8*75% =SUM(H14:H18) =SUM(E14:518) =SUM(F14:F18) =SUM(G14:G18) Production Budget Year 2 quarter Year 3 quarter =16 =J6 Particulars Budgeted unit sales Add: Deired ending inventory Total needs Less: beginning inventory Required production -E6 =F25*30% =SUM(E25:E26) =E25*30% =E27-E28 =F6 =G25*30% =SUM(F25:F26) =F25*30% =F27-F28 -G6 =H25*30% =SUM(G25:326) =G25*30% =G27-628 =H6 =125*30% =SUM(H25:H26) =H25*30% =H27-H28 =J25*30% =SUM(125:126) =125*30% =127-128

Raw material purchase budget Year 2 quarter Year 3 quarter =E29 =F29 =G29 =H29 =129 =135*136 Particulars =B29 R.M reg. to produce one unit Production needs (pounds) Add: Desired ending inv. Of R.M Total needs (pounds) Less: Beginning inv. Of R.M R.M to be purchased Cost of R.M per pound Cost of R.M to be purchased =E35*E36 =F37*10% =E37+E38 =E37*10% =E39-E40 0.8 =E41*E42 =F35*F36 =G37*10% =F37+F38 =F37*10% =F39-F40 =G35*G36 =H37*10% =G37+G38 =G37*10% =G39-G40 =H35*H36 =137*10% =H37+H38 =H37*10% =H39-H40 0.8 =H41*H42 0.8 0.8 =F41*F42 =G41*G42 Schedule of expected cash payments Year 2 quarter 81500 =E43*60% Particulars A/P, beginning balance Q1 purchase Q2 purchase Q3 purchase Q4 purchase Total cash disbursements =E43*40% =F43*60% =F43*40% =G43*60% Year =SUM(E49:449) =SUM(E50:H50) =SUM(E51:451) =SUM(E52:H52) =SUM(E53:H53) =SUM(E54:H54) =G43*40% =H43*60% =SUM(H49:H53) =SUM(E49:E53) =SUM(F49:F53) =SUM(G49:G53)