Budgeting

Case Study

Data

Cash Is King
Exhibit 1. Excel Data Input Section
Input Data (all currency in US$)
Budgeted Sales	Expected
April (units)	2,500
May (units)	6,000
June (units)	3,000
July (units)	2,500
August (units)	2,000
Selling Price/Unit	$120.00
Cash Collection Pattern
Month of sale	40%
Following month	55%
Uncollectible	5%
Cash Payments for Materials
Month of purchase	40%
Following month	60%
Production Requirements
Raw material per unit (lb.)	5
Raw material cost per lb.	$7.00
Direct labor hours per unit	0.5
Direct labor rate per hour	$40.00
Variable MOHD per direct labor hour	$10.00
Fixed MOHD per month	$57,950
Depreciation in fixed MOHD	$20,000
Selling & Administrative (S&A) Costs
Variable S&A cost per unit sold	$1.25
Fixed S&A cost per month	$63,000
Depreciation in fixed S&A cost	$10,000
Other Cash Outflows
Cash dividends paid each month	$15,000
Equipment purchases May	$47,820
Equipment purchases June	$154,600
Desired Ending Inventory
Finished goods	20%
Raw materials	40%
Cash	$30,000
Beginning Account Balances as of March 31
Cash	$37,745
Accounts receivable	$132,000
Finished goods inventory	$30,750
Finished goods cost per unit	$75.00
Finished goods inventory (units)	410
Raw materials inventory	$32,200
Raw materials (lb.)	4,600
Accounts payable	$55,000
Land	$520,000
Buildings and equipment	$1,800,000
Accumulated depreciation	($750,000)
Common stock	$500,000
Retained earnings	$1,247,695
Exhibit 2. Sales at Different Levels
Decreased by
Budgeted Sales	Expected	2%	5%	10%
April (units)	2,500	2,450	2,375	2,250
May (units)	6,000	5,880	5,700	5,400
June (units)	3,000	2,940	2,850	2,700
July (units)	2,500	2,450	2,375	2,250
August (units)	2,000	1,960	1,900	1,800

Student Template

Yellow - You may only use cell references to data & formulas. NO HARD-KEYING!	Little Annin Flagmakers
Blue - you may hard-key numbers in these cells	Sales Budget (US$)
April	May	June	Quarter
Budgeted sales (units)
Selling price per unit
Total Sales
Little Annin Flagmakers
Schedule of Expected Cash Collections (US$)
April	May	June	Quarter
Accounts receivable
Beginning balance
April sales
May sales
June sales
Total Cash Collections
Accounts Receivable as of June 30
Little Annin Flagmakers
Production Budget
April	May	June	Quarter	July	August
Budgeted sales
Add: Desired ending inventory
Total needs
Less: Beginning inventory
Required Production
Little Annin Flagmakers
Direct Materials Budget (US$)
April	May	June	Quarter
Required production in units
Raw materials per unit (lbs.)
Production needs (lbs.)
Add: Desired ending inventory
Total needs
Less: Beginning inventory
Raw materials to be purchased
Cost of raw materials
Total Cost of Raw Materials
Little Annin Flagmakers
Schedule of Expected Cash Disbursements for Material (US$)
April	May	June	Quarter
Accounts payable
Beginning balance
April purchases
May purchases
June purchases
Total Cash Disbursements for Materials
Accounts Payable as of June 30
Little Annin Flagmakers
Direct Labor Budget (US$)
April	May	June	Quarter
Units to be produced
Direct labor hours per unit
Total direct labor hours needed
Direct labor cost per hour
Total Direct Labor Cost
Little Annin Flagmakers
Manufacturing Overhead Budget (US$)
April	May	June	Quarter
Budgeted direct labor hours
Variable MOHD rate
Total variable MOHD
Fixed MOHD expense
Total MOHD expense
Less: Depreciation
Cash Disbursements for MOHD
MOHD rate	/direct labor hour
Little Annin Flagmakers
Unit Product Cost (US$)
Absorption cost per unit	Quantity	Cost	Cost/unit
Direct materials
Direct labor
Manufacturing overhead
Unit Product Cost
Little Annin Flagmakers
Cost of Goods Sold Budget (USD)
Cost of Goods Sold (FIFO)	Units	Cost/unit	Total Cost
Beginning finished goods inventory
Add: Cost of goods manufactured
Good available for sale
Less: Ending finished goods inventory
Cost of Good Sold
Little Annin Flagmakers
Selling and Administrative Expense Budget (US$)
April	May	June	Quarter
Budgeted sales in units
Variable S&A per unit
Total variable S&A
Total fixed S&A
Total S&A expense
Less: Depreciation
Cash Disbursements for S&A
Little Annin Flagmakers
Cash Budget (US$)
April	May	June	Quarter
Beginning Cash Balance
Add: Receipts
Cash collections
Total Cash Available
Less disbursements
Direct materials
Direct labor
Manufacturing overhead
Selling and administrative
Dividends
Equipment purchases
Total Disbursements
Excess (deficiency) of cash available
Financing
Borrowing
Repayments
Interest
Total Financing
Ending Cash Balance
Little Annin Flagmakers
Budgeted Income Statement (US$)
Quarter Ending June 30
Net sales
Less: Cost of goods sold
Gross margin
Less: S&A expenses
Net operating income
Less: Interest expense
Net income
Computation of Net Sales
Sales
Less uncollectible amounts
Net Sales
Little Annin Flagmakers
Budgeted Balance Sheet (US$)
Ending March 31	Ending June 30
Current assets
Cash
Accounts receivable
Raw materials inventory
Finished goods inventory
Plant and equipment
Land
Buildings and equipment
Accumulated depreciation
Total Assets
Liabilities
Accounts payable
Stockholder's equity
Common stock
Retained earnings
Total Liabilities and Stockholder's Equity

Adapted from IMA

IMA EDUCATIONAL CASE JOURNAL VOL. 11, NO. 4, ART. 4, DECEMBER 2018 ISSN 1940-204X

Cash Is King: Master Budgets to Inform a Credit Decision

Anne M.A. Sergeant, CMA, PhD Seidman College of Business Grand Valley State University Grand Rapids, MI Neal VandenBerg, CPA, PhD Seidman College of Business Grand Valley State University Grand Rapids, MI

MANUFACTURING AND SG&A COSTS

The flags are made in one plant, which has a capacity of 6,200 units per month. LAF budgets have 20% of next month’s sales in finished goods inventory at the end of each month. There is plenty of storage space for finished goods.

Fabric is the only direct material and each flag requires five pounds of fabric at US$7 per pound. LAF plans to have 40% of next month’s fabric needs on hand at the end of the month. Fabric is purchased on credit with 40% paid in the month of purchase and 60% paid the next month. The standard direct labor hours to manufacture one flag is 0.50 hours at US$40 per hour. For simplicity, direct labor costs are budgeted as if they were paid when incurred. Manufacturing overhead rates are computed quarterly and applied based on direct labor hours. Fixed manufacturing overhead costs are estimated to be US$57,950 per month, of which US$20,000 is property, plant, and equipment (PPE) depreciation. Variable manufacturing overhead, including indirect materials, indirect labor, and other costs, is estimated at US$10 per direct labor hour.

The selling and administrative expenses include variable selling costs (primarily

shipping) of US$1.25 per unit and fixed costs of US$63,000 per month, of which US$10,000 is depreciation of the administrative office building and equipment.

FINANCIAL STATEMENT DETAILS AND CASH PLANNING

LAF uses first in, first out (FIFO) inventory valuation. As of March 31, the expected finished goods inventory is 410 units, valued at US$75 per unit. The company expects to have 4,600 pounds of fabric on hand, valued at US$7 per pound. Other expected account balances include accounts payable at US$55,000, accounts receivable at 132,000, cash at US$37,745, land at US$520,000, and building and equipment at US$1,800,000 with accumulated depreciation of US$750,000. LAF has no long-term debt; common stock is valued at US$500,000 and is not expected to change during the quarter; expected retained earnings as of March 31 are US$1,247,695.

LAF budgets for US$30,000 ending cash balance each month and is requesting a line of credit that will allow it to adjust for its cash needs. The dividends of US$15,000 are paid each month. During the quarter, LAF planned to purchase equipment in May and June for US$47,820 and US$154,600, respectively. This equipment is being

purchased to increase capacity and is not expected to come on line until after the quarter, thus not affecting the manufacturing overhead costs.

LOAN DETAILS

LAF has requested a line of credit of US$60,000 to cover production costs during the seasonal increase in business. Kent Bank uses the following terms on its lines of credit. All borrowing is done at the beginning of the month in whole dollar increments. All repayments are made at the end of the month in whole dollar increments. The full line of credit is expected to be paid off by the end of the quarter with all the interest repaid at the end of the quarter. The interest rate on this loan is 16% per year.

ASSIGNMENT REQUIREMENTS:

1. Quantitative Analysis: a. Using the data input provided (Exhibit 1), prepare LAF’s master budgets in

Excel. Do not hard-code numbers into the spreadsheet, except where permitted in the financing section of the cash budget.

2. Qualitative Analysis:

In a 2-3 page report, based on the results of your quantitative analysis: a. Determine a credit recommendation for Kent Bank, to lend or not. Justify

your credit decision. b. Explain why the cash budget is more important to a bank than the

accounting net income when determining a credit decision.