Question

Case: Big Batch Crisp Company is a small business that makes & sells individual jars of cherry crisp. The company sells to grocers, coffee shops, and sandwich shops locally and in the surrounding area. They also take a limited amount of large quantity orders directly from customers. The information below pertains to the company’s budgeting process during their busiest time of year.

• The cherry crisp is sold in individual jars for $4.75 per unit (jar).

• The budgeted sales in units (jars) are as follows:

• October          50,000 units
• November      72,000 units
• December      68,000 units
• January          51,000 units
• February        58,000 units

• All sales are sold on account. The collection pattern is as follows:

• 60% of sales in the month of sale

• 40% collected in the following month

• The company aims to have jars of crisp (finished goods inventory) on hand in refrigeration at the end of each month equal to 5% of the next month’s unit sales. On September 30^th, the company had 2,500 jars on hand.

• .2 pounds of cherries are required for each jar produced. The company’s goal is to have cherries (raw material) on hand at the end of each month that is equal to 15% of the next month’s production needs. On September 30^th, the company had 1,533 pounds of cherries on hand.

• The cherries costs $2.60 per pound from a local farm. Big Batch Crisp’s payment pattern is as follows:

• 50% of the month’s purchase are paid for in the month of purchase
• 50% is paid for in the following month

• Because the crisp is made in large batches, it only requires 3 minutes (.05 hours) of labor on average to produce one jar (unit of finished good). The hourly employees are paid $17.00 an hour. Wages are paid in the month incurred.

• Variable manufacturing overhead is $1.40 per jar.

• Fixed manufacturing overhead is $9,000 per month including $6,000 in depreciation that is not a current cash outflow.

• All cash disbursements for manufacturing overhead are paid in the month incurred.

• Variable selling and administrative expenses are $0.80 per unit sold.

• Fixed selling and administrative expense is $8,000 per month including $4,500 in depreciation that is not a cash outflow of the current month.

• All cash disbursement for selling and administrative costs are paid in the month incurred.

• Big Batch Crisp Company spent $12,000 at the beginning of October (October 1^st) updating and upgrading their kitchen equipment.

• Big Batch Crisp borrowed a short term note of $150,000 on September 30^th to support 4^th quarter sales and growth activities. Interest payments of $625 are made on the last day of each month.

• Big Batch Crisp is able to repay the short term note of $150,000 on December 31^st and still keep their minimum cash balance of $200,000 on hand.

• Big Batch Crisp Company uses variable costing on its budgeted income statement and balance sheet.

Big Batch Crisp has the following balance sheet as of September 30^th, 2020:

Big Batch Crisp Co.
Balance Sheet
As of September 30th, 2020
Assets			Liabilities & Equities
Cash	225,200		Accounts Payable	13,840
Accounts Receivable	87,000		Notes Payable	150,000
Raw Materials Inventory	3,986		Interest Payable	0
Finished Goods Inventory	6,925		Total Liabilities	163,840
PP&E, net	96,000
			Retained Earnings	255,271
			Total Equities	255,271
Total Assets	419,111		Total Liabilities & Equities	419,111

Requirements

1. Enter your name at the top of the INPUTS tab.

2. Prepare a master budget for the quarter ended December 31^st, 2020 including: Finished Goods Inventory Cost per Unit, Sales Budget, Schedule of Expected Cash Collections, Production Budget, Raw Materials Purchases Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling & Administrative Expense Budget, Cash Budget, Budgeted Income Statement, and Budgeted Balance Sheet (total of 11 schedules.) Each schedule should be on a separate worksheet as it is laid out in your template.

There is at template provided to you in the assignments link in the Excel Project module with an input page that you must use.

Complete the shaded areas of the template only.

All of your spreadsheets must be formula driven from the input tab. This means that every cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values.

3. Big Batch Crisp Company is interested in purchasing three new delivery vehicles in October costing approximately $65,000 total. The company prefers to pay cash for these vehicles rather than having to finance them, while still maintaining a minimum cash balance of $200,000 at all times. The existing budget does not show that they will be able to do this without making a change to sales or cost. Using Goal Seek, determine the sales price necessary to allow for the purchase of these vehicles in October.

HINT: To find Goal Seek, click on the Data tab and under Data Tools click on the What-If Analysis. After finding your answer, manually enter your answer on the Goal Seek tab and then change the sales price back to $4.75 on the INPUTS tab.

4. After comparing your file against the project rubric below, upload your completed Excel file to Canvas via the project link to submit. You may only submit once so make sure this is your final version.

*As you work, be sure to periodically check the figures below to confirm that you are on the right track!

Good luck!

Check Figures/Evaluation Rubric:

These figures will be used to grade your project.

Have correct answers that match the following check figures: Total Sales for the Quarter = $902,500 Cash Collections in October = $229,500 Desired Units of FG Ending Inventory for the Quarter = 2,550 jars Raw Material Purchases in December = $33,686 Total Overhead Disbursements for the Quarter = $275,070 October Ending Cash Balance = $252,667 Net Income for the Quarter = $171,325 Total Assets at the End of the Quarter = $443,439 Goal Seek Sales Price = $5.16	Possible 1 1 1 1 1 1 1 1 2	Your Score
All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets)	15 points
Your worksheet passes our tests of your formulas. Be sure that you only make one change at a time (and then change back to the original value before moving on to the next test). On your input tab we will:   Change the units sold in October to 100,000 jars and net income should become $230,325. Change raw material purchase payments to 80% in month of purchase and 20% in the following month and the ending cash balance for the quarter on the cash budget is $216,564 Change units sold in December to 100,000 and the total assets (and total liabilities & equities) on the balance sheet becomes $487,917	5 5 5
Total Score	40
Comments

Answer 1

Budgets