The Excel worksheet form that follows is to be used to recreate the Review Problem related to Mynor Corporation. Download the workbook containing this form at from Connect. On the website you will also receive instructions about how to use this worksheet form.

You should proceed to the requirements below only after completing your worksheet.
Required:
1. 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 errors in your worksheet and correct them. Have the total cash disbursements for the year changed? Why or why not?
2. The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget:

a. What are the total expected cash collections for the year under this revised budget?
b. What is the total required production for the year under this revised budget?
c. What is the total cost of raw materials to be purchased for the year under this revised budget?
d. What are the total expected cash disbursements for raw materials for the year under this revised budget?
e. After seeing this revised budget, the production manager cautioned that due to the limited availability of a complex milling machine, the plant can produce no more than 90,000 units in any one quarter. Is this a potential problem? If so, what can be done about it?

Transcribed Image Text:

A. Chapter 9:Applying Excel 3 Data 5 Budgatad unit salca 71-Selling price perut Yoar 2 Quarter Year 3 Quarfer 40,000 60,000 00070,000 80000 8 per unit 8Accounts receivable, beginning belance $65,000 Sales collected in the quarter sales are made 0 Sales collected in the querter ater eales are made 1 Desired ending tinished goods nventory is 12 Finished goods inventury, beginning 13 Raw matenials required to produce ane unit 4 Desirod anding inmentory of raw matcrialsa is 75% 25% 30% of the budgeted unit sales orthe next quarter 2,000 units 5 pounds 10% of the next quarters production needs 1au manids ameantary eginming 23,000 poundh Raw matenial costs 17 Raw mstenals purchases are paid 18 and 19 Accounts payable for raw matenials, beginnng balance s81.500 S0.80 per pound 60% n the quarter the purchases are made 40s in the quarter following purchase 20 21 Enter a fomule to esch of the cela marked Nih 22 Revievr Problem: Budget Schedules ? belo 23 24 Construct the salas budgor Yoar 2 Quarter Year 3 Quarte 25 Budgeted unt sales 27Selling prce per unit 28 Total sales 29 30 Construct the schedule of expected cash collections 21 32 Accounts receiable, beginning balance 33 Firat-quarter slas 24 Sacand-quarter salas 5 Third-quarter salss 36 Fourth-quarter sales 37 Total cash colections 28 9 Construct the proderction budget 40 1 Budgatad unit salas 2 Add desirad inished goods inventory 43 Total needs 44 Less beginning inventory 45 Required production 46 47 Construct the raw materials purchases burdget Year 2 Quarter Year 2 Quarter Year 3 Quater Year 2 Quarter Year3 Quarter 49 Raquired production (units S0 Raw materials raquirad to producs ons unit 51 Praduction neads ipaunds 52 Add desired anding invetory of raw materals (poundsl 53 Total needs (pounds) 54 Less beginning inventory of raw materials (pounds) 55 Rawmaterials to be purchased 56 Cost of ra materials per pound 57 Cost of raw materials to be purchased 39 Construct the schedula af axpecied cash payrmonts 60 61 Accounts payable. beginning balance 62 First-quarter purchases 63 Second-quarter purchases 64 Thrd-quarter puchases 65 Fourth-quaiter purchases 66 Total cash dsbursements Yoar 2 Quarter Year Year 2 Quarter Year 3 Quarter 2 3 4 2 Data 50,000 70,000 120,000 80,000 90,000 100,000 Selling price per unlt.. $7