Assignment - Using Excel to Prepare Budgets
Using the information below, complete the required elements using Excel per the instructions:
Heartland Inc. makes specialty apple cider sold in one-gallon containers. Sales units for the next four months are expected to be:
|
|
Gallons
|
|
October
|
41,000
|
|
November
|
38,000
|
|
December
|
50,000
|
|
January
|
51,000
|
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.
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 whenever possible. Use Times New Roman 11.
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.
The Sales Budget, Production Budget and Direct Labor Budget should be completed on one worksheet and must be clearly labeled and properly formatted. Formatting includes the use of dollar signs where necessary and lines to clearly delineate sub-totals and totals.
Make sure your spreadsheets are also formatted for easy printing by me. If you don't know how to do this, go to the Page Layout tab and select Print Area.