ChocoAlmo Budget Project

ACC 202 - Spring 2021

Due Sunday, April 18

The ChocoAlmo Company produces and sells chocolate bars filled with almond pieces.

The 6-oz chocolate bar has two direct materials: 100% organic cacao beans and 100%

organic almonds. The production process includes creating the chocolate from scratch

using the cacao beans and chopping the almonds into the pieces that fill the chocolate bar.

Indirect materials include very minimal amounts of sugar, milk, and salt, in addition to

some packaging materials. ChocoAlmo is preparing budgets for the 2nd quarter ending

June 30, 2021. For each requirement below prepare budgets by month for April, May and

June, and a total budget for the quarter.

1. The previous year's sales (2020) for the corresponding period were:

April

May

June

July

August

35,000 chocolate bars

44,000 chocolate bars

37,000 chocolate bars

45,000 chocolate bars

42,000 chocolate bars

The company expects the above volume of bar sales to increase by 7% for the period

April 2021 - August 2021. The budgeted selling price for 2021 is $9.00 per chocolate

bar. The company expects 85% of its sales to be cash (COD) sales. The remaining 15%

of sales will be made on credit. Make a Sales Budget for ChocoAlmo.

2. The company desires to have finished goodsinventory on hand at the end of each month

equal to 14 percent of the following month's budgeted unit sales. On March 31, 2020,

the company expects to have 5,243 chocolate bars on hand. (Note: an estimate of sales

in July is required in order to complete the production budget for June). Use the

@ROUND function to round to the nearest whole number the number of

chocolate bars desired in ending inventory. make a Production budget.

3. The chocolate bars require two direct materials: Cacao Beans and Almonds.

Cacao Beans

Each chocolate bar requires 0.45 pounds (lbs) of Cacao beans. Management desires to

have materials on hand at the end of each month equal to 11 percent of the following

month's chocolate bar production needs. Use the @ROUND function to round to the

nearest whole number the number of pounds of cacao beans desired in ending

inventory. The beginning inventory of cacao beans, in April 2021, is expected to be

1,921 pounds. Cacao beans are expected to cost $8 per pound. (Note: budgeted

production in July is required in order to complete the direct materials budget for June. Our supplier only allows purchases in whole pounds, so use the @ROUND

function to round to the nearest whole number the number of pounds to

purchase).

Almonds

Each chocolate bar also requires 0.10 pound of almonds. Management desires to have

almonds on hand at the end of each month equal to 15 percent of the following month's

production needs. Use the @ROUND function to round to the nearest whole

number the number of pounds of almonds desired in ending inventory. The

beginning inventory, in April 2021, is expected to be 582 pounds of almonds. Almonds

are expected to cost $7 each. (Note: budgeted production in July is required in order to

complete the direct materials budget for June. Use the @ROUND function to round

to the nearest whole number the number of pounds of almonds to purchase.

make a Direct Materials budget. Also, because two direct materials are required

for production - cacao beans and almonds - you will need a separate schedule for each

direct material.

4. Each chocolate bar requires 0.05 hours of direct labor. Direct labor costs the company

$20 per hour. Make a Direct Labor budget.

5. ChocoAlmo budgets indirect materials (e.g., sugar, salt, packaging materials) at $0.20

per chocolate bar. Other variable components are $0.12 per bar for indirect labor and

$0.15 per bar for utilities. The following fixed costs per month are budgeted for indirect

labor, $4,000, depreciation, $9,000, and other, $2,000. Make a Manufacturing

Overhead budget.

6. Variable selling and administrative expenses consist of outward freight ($200 per 1,000

chocolate bars) and sales commission (4 percent of the selling price per bar). Fixed

selling and administrative expenses include administration ($30,000 per month) and

marketing ($40,000 per month). Make an Operating Expenses budget.

7. Make 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 500,000 chocolate bars.

8. Make a Budgeted Income Statement for the quarter for ChocoAlmo. Assume

interest expense of $0, and income tax expense of 18% of income before taxes.

ChocoAlmo's goal for the quarter is to make its net income greater than 8% of its sales

revenue. To determine whether the company achieves the goal, use @IF function. In

the IF function, you need to label "Achieved" if it achieves the goal (if the condition is

met) or "Not Achieved" if it does not achieve (if the condition is not met). Use the

CELL right next to 'Net Income' cell to make the IF function that returns one of the

labels based on whether the condition (net income > sales revenue*8%) is met or not.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.

Make 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 9%, then you should see effects of that change throughout the other budgets.

Likewise, if the budgeted selling price per bar changes from $9.00 to $10, then 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 ChocoAlmo 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. Make an input area in which you enter all input variables - e.g., selling price,

budgeted volume increase, pounds per bar, 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 wr ite 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

*** the attached pictures are a sample of what the sheet should look like when completed

The Copy BIUS A v Merge & Center Format Painter Clipboard Font Alignment X A B C D 1 ChocoAlmond Company 2 2020 Budget Assumptions 3 Prepared by ] I 4 5 Sales Budget Assumptions Unit Price 15.00 Percentage of Cash Sale 35% Percentage of Credit sale 65% Expected sales increase 8% 10 11 2019 Sales 12 April 50,000 bags 13 May 55,000 bags 14 June 90,000 bags 15 July 75,000 bags 16 August 60,000 bags 17 18 19 Production Budget Assumptions 20 21 22 23 Direct Materials Budget Assumptions 24 25 26 27 Direct Labor Budget Assumptions Assumptions Budgets + Type here to search O HiCut Arial 12 A A E 2 Wrap Text In Copy Format Painter Merge & Center Clapboard Fort Almoment X A B C E 19 Production Budget Assumptions 20 21 22 23 Direct Materials Budget Assumptions 24 25 26 27 Direct Labor Budget Assumptions 28 29 30 31 Manufacturing Overhead Budget Assumptions 32 33 34 35 Operating Expense Budget Assumptions 36 37 38 39 Budgeted Manufacturing Cost Per Unit Budget Assumptions 40 41 47 43 Budgetd Income Statement Assumptions 44 45 Assumptions Budgets Enter Type here to search O RI m #Covitional Forput at Cell Fogy Lithe Styes x y f E F H K 1 GBCoffee Company 2020 Budget Assumptions Prepared by: Student Name April May June Quarter July August April May June Quarter July August 8 Sales Budget 19 Unit Sales 54.000.00 59,400.00 97,200.00 210 600.00 81,000.00 64,800.00 10 Unit Price 15/00 5 15.00 $ 15.00 5 15.00 15.00 5 15.00 11 Sales Revenue 810.000.00 $ 891,000.00 $ 1 458,000.00 $3,159.000.00 $ 1.215.000.00 1 5 972,000.00 12 13 Cash Sale 35% 3 283,500.00 $ 311,850.00 $ 510,300.00 $1,105.850.00 $ 425.250.00 5 340,200.00 14 Credit Sale 659% 526,500.00 579 150.00 947.700.00 2.053 350.00 780.750.00 831,800.00 15 Total 810 000.00 5 801.000.00 $ 1,458,000.00 $3.159.000.00 $ 1 215,000.00 $ 972,000.00 16 17 April May June Quarter July August 18 Production Budget 19 Unit sales Desired dnding inventory 21 Total needed 27 Beginning inventory 123 24 25 April May June Quarter July August Assumpoon Budgets Type here to search a hpFoursat Inkey A C 25 June Quarter July August 26 April May 27 Direct Material Budget 30 April May June Quarter July August 31 32 Direct Labor Budget May June Quarter July August 36 April 37 MOH Budget 40 April June Quarter July August 47 Operating Expense Budget 43 44 August 45 April May June Quarter July 47 Budget Manufacturing Cost per Unit 49 50 51 Rudnet Income Statement Masumptions Budgets Type here to searchFormal Pairiced BS A C TH 76 Apri Mary June Quarter July August 27 Direct Material Budget 30 31 April May June Quarter July August 32 Direct Labor Budget 3:3 34 35 April May June Quarter July August 37 MOH Budget 30 41 April May June Quarter August 42 Operating Expense Budget 43 14 45 Mary June Quarter July August April 47 Budget Manufacturing Cost per Unit 49 50 51 Budget Income Statement Asumptions Budgeti Q Type here to search