OBJECTIVE The purpose of this assignment is to apply Excel skills to a budgeting

Question

OBJECTIVE The purpose of this assignment is to apply Excel skills to a budgeting problem in Chapter 22 and to provide a schedule which management can use to perform sensitivity analysis DATA SET Heartland Inc. makes specialty apple cider sold in one-gallon containers. Sales units for the next four months are expected to be Gallons October November December 41,000 38,000 50,000 51,000 Janua Additional information for the company is as follows: The average selling price is $6 per gallon. Heartland's policy is to have 10% of the next month's sales in ending inventory On September 30, it is expected that 5,000 gallons of cider will be on hand Each gallon of cider requires 0.25 direct labor hours The average wage per hour is $8. REQUIREMENTS 1. Set up a data table either in a separate worksheet tab or at the beginning of your Excel worksheet with the expected sales in units and any other facts you will need for your formulas 2. For the fourth quarter, by month and total (October-December), prepare a sales budget, a production budget, and a direct labor budget in good form using Excel formulas. There should be no direct inputs in the budget schedules. Every cell of the budget should be a reference to the data table or a calculation of other cells in the spreadsheet The sales budget, production budget and direct labor budget may be completed on one worksheet and must be clearly labeled Use the January sales data for the December ending inventory calculation. You should not prepare a budget column for January 4. If Heartland Inc. revised its November sales forecast from 38,000 gallons to 48,000 gallons, what would the total sales dollars and direct labor hours be for the quarter? What management decisions would be impacted by the increase in gallons sold? Record your responses to requirement 4 in a separate worksheet tab within your Excel file

Explanation / Answer

1)

2)

Sales Budget

Production Budget

Direct Labour Budget

4)

Sales Budget

Budgted labour hours Budget

Month October November December January Sales (gallons) (a) 41000 38000 50000 51000 selling Price($) (b) 6 6 6 Ending Inventory (10% of next month's sales) © 3800 5000 5100 Beginning Inventory (d) 5000 3800 5000 Labour hours required per gallon (e) 0.25 0.25 0.25 Wage Rate per hour $ (f) 8 8 8

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.