Excel solutions - 09_46_soln

Excel solutions - 09_46_soln - Case 09-48 Student Name:...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
Case 09-48 Student Name: Instructor Class: McGraw-Hil /Irwin FRAME-IT COMPANY Master Budget 1. Sales Budget: 20x0 20x1 4th 1st 2nd 3rd 4th Entire Quarter Quarter Quarter Quarter Quarter Year S frame unit sales 50,000 55,000 60,000 65,000 70,000 250,000 x S sales price $10 $10 $10 $10 $10 $10 S frame sales revenue $500,000 $550,000 $600,000 $650,000 $700,000 $2,500,000 L frame unit sales 40,000 45,000 50,000 55,000 60,000 210,000 x L sales price $15 $15 $15 $15 $15 $15 L frame sales revenue $600,000 $675,000 $750,000 $825,000 $900,000 $3,150,000 Total sales revenue $1,100,000 $1,225,000 $1,350,000 $1,475,000 $1,600,000 $5,650,000 Cor ect! Cor ect! Cor ect! Cor ect! Cor ect! Cor ect! Cash sales* $440,000 $490,000 $540,000 $590,000 $640,000 $2,260,000 660,000 735,000 810,000 885,000 960,000 3,390,000 Cor ect! Cor ect! Cor ect! Cor ect! Cor ect! Cor ect! *40% of total sales. †60% of total sales. 2. Cash receipts budget: 20x1 1st 2nd 3rd 4th Entire Quarter Quarter Quarter Quarter Year Cash sales $490,000 $540,000 $590,000 $640,000 $2,260,000 Cash col ections from credit sales made during cur ent quarter* 588,000 648,000 708,000 768,000 2,712,000 Cash col ections from credit sales 132,000 147,000 162,000 177,000 618,000 Total cash receipts $1,210,000 $1,335,000 $1,460,000 $1,585,000 $5,590,000 Cor ect! Cor ect! Cor ect! Cor ect! Cor ect! *80% of cur ent quarter's credit sales †20% of previous quarter's credit sales 3. Production budget: 20x0 20x1 4th 1st 2nd 3rd 4th Entire Quarter Quarter Quarter Quarter Quarter Year S frames: Sales (in units) 50,000 55,000 60,000 65,000 70,000 250,000 Add: Desired ending inventory 11,000 12,000 13,000 14,000 15,000 15,000 Total units needed 61,000 67,000 73,000 79,000 85,000 265,000 Less: Expected beginning inventory 10,000 11,000 12,000 13,000 14,000 11,000 Units to be produced 51,000 56,000 61,000 66,000 71,000 254,000 Cor ect! Cor ect! Cor ect! Cor ect! Cor ect! Cor ect! L frames: Sales (in units) 40,000 45,000 50,000 55,000 60,000 210,000 Add: Desired ending inventory 9,000 10,000 11,000 12,000 13,000 13,000 Total units needed 49,000 55,000 61,000 67,000 73,000 223,000 Less: Expected beginning inventory 8,000 9,000 10,000 11,000 12,000 9,000 Units to be produced 41,000 46,000 51,000 56,000 61,000 214,000 Cor ect! Cor ect! Cor ect!
Background image of page 1
This is the end of the preview. Sign up to access the rest of the document.
Ask a homework question - tutors are online