Question

I need this answered in Microsoft Excel please

GBCoffee Budget Project

The GBCoffee Company roasts and sells high quality certified sustainable coffee beans. The final one pound bags of roasted whole coffee beans has two direct materials – coffee beans and packaging. GBCoffee is preparing budgets for the fourth quarter ending December 31, 2019. For each requirement below prepare budgets by month for October, November, and December, and a total budget for the quarter.

1. The previous year’s sales for the corresponding period were:

October 50,000 bags

November 55,000 bags

December 90,000 bags

January 75,000 bags

February 60,000 bags

The company expects the above volume of coffee sales to increase by 8% for the period October 2019 – February 2020. The budgeted selling price for 2019 is $19.50 per bag. The company expects 35% of its sales to be cash (COD) sales. The remaining 65% of sales will be made on credit. Prepare a Sales Budget for GBCoffee.

2. The company desires to have finished goods inventory on hand at the end of each month equal to 10 percent of the following month's budgeted unit sales. On September 30, 2019, GBCoffee expects to have 5,350 bags of roasted coffee on hand. Prepare a Production budget.

3. The final product (bags of roast coffee) require two direct materials: green coffee beans and packaging material. 1.25 pounds of green coffee beans are required for each one pound bag of roasted coffee beans. During the roasting process residual moisture, some oils, and chaff (the silver skin) are removed, which lowers the weight of the final product. Management desires to have materials on hand (i.e., green beans) at the end of each month equal to 20 percent of the following month's green coffee bean needs. The beginning materials inventory, October 2019, is expected to be 13,500 pounds. The green coffee beans cost $7.00 per pound.

Packaging material is purchased by the roll and 100 coffee bags are produced from each roll. The packaging material is made from biodegradable, organic plant fiber that extends the shelf life of the coffee beans while preserving its freshness. Management desires to have packaging on hand at the end of each month equal to 10 percent of the following month's production needs. The beginning inventory of packaging (i.e., rolls of packaging material), October 2019, is expected to be 54 rolls. Packaging is expected to cost $24 per roll.

Note, budgeted production in January is required in order to complete the direct materials budget for December. Also, use the @ROUNDUP function to round up to the nearest whole number the pounds of green beans and number of rolls of packaging material to purchase. Prepare a Direct Materials budget. Also because two direct materials are required for production – green beans and rolls of packaging material - you will need a separate schedule for each direct material.

4. Each bag of coffee produced requires 0.25 hours of direct labor. GBCoffee pre-washes the green beans and then uses a collection of hand-cranked coffee roasters to achieve the perfect ‘old school’ roast. Each hour of direct labor costs the company $20. Prepare a Direct Labor budget.

5. GBCoffee budgets indirect materials (e.g., water used in the pre-wash phase) at $0.10 per bag. GSO treats indirect labor and utilities as mixed costs. The variable components are $0.50 per bag for indirect labor and $0.20 per bag for utilities. The following fixed costs per month are budgeted for indirect labor, $26,500, utilities, $16,000, and other, $41,500. Prepare a Manufacturing Overhead budget.

6. Variable selling and administrative expenses are $1.20 per bag of coffee sold. Fixed selling and administrative expenses are $60,000 per month. These costs are not itemized, i.e., the budget has only two line items – variable operating expenses and fixed operating expenses. Prepare an Operating Expenses budget.

7. Prepare a Budgeted Manufacturing Cost per unit budget. Refer to exhibit 9-11 for guidance. To calculate FMOH/unit calculate total FMOH for the year and divide this by budgeted production for the year. The total production volume for the year is budgeted at 720,000 bags.

8. Prepare a Budgeted Income Statement for the quarter for GBCoffee. Assume interest expense of $0, and income tax expense of 30% of income before taxes.

Directions:

Refer to Chapter 9 (The Master Budget) for guidance in setting up your budgets and schedules. Adapt your schedules for the specific details outlined in the requirements above. Prepare your budgets using Excel. Use formulas and cell references so that any change you make in one budget is carried through to all the budgets. There should be no hard keyed numbers in your formulas. For example, if you change the ‘sales volume increase’ from 7% to 10% you should see effects of that change throughout the other budgets. Likewise, if the budgeted selling price changes from $19.50 to $20.00 your spreadsheet model should be able to quickly and easily accommodate this change, i.e., change the input cell for budgeted selling price and see the effect on income.

The spreadsheet will be graded on presentation, correctness, and quality of your spreadsheet model (i.e., does it update correctly for changes in input variables). See the grading rubric on Canvas. You should approach this assignment as if you are the Management Accountant at the GBCoffee Company and you are going to present these budgets in a meeting to the CEO, CFO, and other management personnel.

Some general principles to follow in constructing your Excel spreadsheet model:

1. Prepare an input area in which you enter all input variables – e.g., selling price, budgeted volume increase, pounds per bag, ending inventory percentage, etc. You may use the “Assumptions” tab of the sample spreadsheet or a designated area within your budget spreadsheet, as long as the input area is clearly labeled and neatly organized.

2. Each schedule should refer to the input area for each constant data value (see sample spreadsheet file). To the extent possible, keep all constant values together in one area of the worksheet. An important principle of good spreadsheet design is to keep just one copy of each constant value. That is, enter a constant value in only one location in the worksheet. Then if you use the value in another cell, use a cell reference that refers to the constant value's unique location.

Example (hypothetical): You enter the constant value of 6% for sales tax in cell E5. When you write a formula in your worksheet that requires sales tax, reference E5 in the formula instead of "hard coding" in the 6% value.

Do: =subtotal*E5

Don't: =subtotal*6%

3. Use cell references for constant data values and to calculate formulas within your spreadsheet. There should be no hard-keyed numbers in your formulas. For example, the formula to determine current period sales in units should reference an input cell with last year’s sales volume and a cell with the volume percentage increase.

4. Label and format appropriately – e.g., use $ to format dollar amounts, format cells for decimal places, etc…

Answer 1