- Information For Lemon Squeezer Budgets Lemon Ltd Produces Fruit Squeezers The Squeezers Sales For The Next 5 Months 1 (33.24 KiB) Viewed 34 times
- Information For Lemon Squeezer Budgets Lemon Ltd Produces Fruit Squeezers The Squeezers Sales For The Next 5 Months 2 (22.5 KiB) Viewed 34 times
- Information For Lemon Squeezer Budgets Lemon Ltd Produces Fruit Squeezers The Squeezers Sales For The Next 5 Months 3 (36.93 KiB) Viewed 34 times
- Information For Lemon Squeezer Budgets Lemon Ltd Produces Fruit Squeezers The Squeezers Sales For The Next 5 Months 4 (45.12 KiB) Viewed 34 times
- Information For Lemon Squeezer Budgets Lemon Ltd Produces Fruit Squeezers The Squeezers Sales For The Next 5 Months 5 (37.69 KiB) Viewed 34 times
Information for Lemon Squeezer Budgets. Lemon Ltd. produces fruit squeezers. The squeezers' sales for the next 5 months are projected to be: January 60,000 February 90.000 March 96,000 April 84,000 May 104,000 The following information pertains to the budget assumptions. a. Finished goods inventory on January 1 is expected to be 20000 units. The desired ending FGI for any month (except for the Dec 31 inventory as noted previously) is expected to be 20% of the following month's sales. b. Data for materials used are: metal 1.5 ounces per unit $17.00 per ounce plastic 0.75 ounces per unit $9.00 per ounce Raw materials endine inventorv is alwavs budeeted to equal 15% of the followine month's
Raw materials ending inventory is always budgeted to equal 15% of the following month production needs. On March 31, the ending inventory of metal parts is expected to be 18,000 ounces and for plastic parts is expected to be 12,000 ounces. c. Direct labor used: Machine operator: 1 hour at a rate of $12 per hour. Assembler: a half an hour at a rate of $9 per hour.
d. Overhead each month is estimated at: estimates Fixed portion (per month) Supplies Power Maintenance 25000 Supervision 28000 Depreciation 90000 Taxes 8600 Other 86000 variable cost (sper Du(sum of Machine Operator plus Assembler)) 09 0.75 1 1.4 variable cost (per unit sold (cost driver) e. Selling and Admin is estimated to be: Selling and admin per month Fixed portion (per month) Salaries 60000 Commissions Depreciation 10000 Shipping Other 26000 13 25 0.8 f. Selling price per squeezer is $75.00. g. Sales are all on account. 60% of sales are collected in the month of sale and 40% in the month following the sale. Accounts receivable on January 1 is $150,000. h. January is a planned purchase of equipment for $1,500,000 1. Borrowings are at 3% per year interest, and borrowings are assumed to be at the beginning of the month required and at the end of the month of repayment. January 1 beginning cash is
REQUIRED: Please prepare the following budgets in an Excel spreadsheet for each month of the first quarter and for the quarter in total. The first six (light green highlighted) budgets are to be on one tab, separate from the original data, and the two (light blue highlighted) budgets are to be on a third, separate, tab. All Excel budget sheets should be nothing but formulae and cell references: there should be no Numbers directly keyed into a cell of your budgets. All the cells should have algebraic formulae (eg use "sum" function), or the cells would have a cell reference to the data sheet (eg use"=data!C5" to refer to the January sales volume). Good organization, presentation, and formatting for the budgets should be followed and will be graded. As well, making the budgets easy for the reader to read will also count toward the grade. DUE MONDAY April 25, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Lemon Excel Project DROPBOX IN FOLIO. a 1. Sales budget 4. Direct labor budget 2 Production budget 3. Direct materials purchases budget 5. Overhead budget 6. Selling and Admin budget
DUE MONDAY April 25, mianignt. PLACE YOUR SPREADSHEET SOLUTION IN THE Lemon Excel DROPBOX IN FOLIO. 1. Sales budget 4. Direct labor budget 2. Production budget 3. Direct materials purchases budget 5. Overhead budget 6. Selling and Admin budget 7. Cash Budget 8. Income Statement