GB519 Unit 6 Master Budget and Cash Plan Example

A Year by Quarter for a Made-to-Order Lab: A Unit 6 Master Budget and the Credit Line It Requires

[Student Name]

Purdue University Global

GB519: Measurement and Decision Making

Unit 6 Master Budget

[Instructor Name]

[Date]

The lab and every figure are composite and illustrative; the sources cited are real.

Cover Memo

To: Owner, Fernhill Orthotic Lab

Subject: Next year's master budget and the credit facility

The budget plans $4.2 million of revenue and about $404,000 of operating income. Cash is the constraint. Year-end demand, hospital systems paying on 60-day terms and $384,000 of deposits on the replacement mill push the cash balance below our $90,000 floor in the third quarter, and borrowing peaks near $258,000 at year-end. If hospitals pay at 75 days, the peak rises to about $322,000. I recommend arranging a $350,000 revolving line before the third quarter, and confirming the mill's payment schedule with the supplier now.

Tab 1: Assumptions

Every driver sits on one tab, with its origin, so that changing one updates every schedule:

Volume: about 21,800 devices, up 4 percent, from clinic order history and the hospital systems' stated plans.

Revenue by quarter: $900,000, $1,000,000, $1,040,000 and $1,260,000; the fourth quarter is highest because insured patients whose deductibles are already met want their devices before January resets them.

Channel mix: independent clinics 60 percent, on 30-day terms; hospital systems 30 percent, on 60-day terms; Fernhill's own fitting clinic 10 percent, with copays collected at the visit and insurance paying in about 45 days.

Materials: 15 percent of revenue, bought about three weeks ahead and paid within the quarter.

Technician labor: 30 percent of revenue, plus $60,000 of seasonal technicians in the fourth quarter.

Overhead: $270,000 a quarter of fixed cash cost plus 3 percent of revenue; depreciation of $160,000 for the year.

Selling and administrative: $120,000 a quarter.

Other cash items: $45,000 of term loan principal and $40,000 of owner distributions each quarter, $25,000 of estimated taxes each quarter, and mill payments of $192,000 in each of the third and fourth quarters.

Cash policy: a minimum balance of $90,000, with borrowing at 8 percent a year.

Tab 2: Sales by Channel

Revenue is budgeted by channel because each pays on a different schedule. In the fourth quarter, for example, clinics bring $756,000, hospital systems $378,000 and the fitting clinic $126,000. Blending them would hide the timing that causes the year-end squeeze.

Tab 3: Production

Every device is made to order, so production equals sales and there is no finished inventory. This is the budget's hard constraint: the lab cannot build braces in the spring to sell in December. The fourth quarter's roughly 6,500 devices must be met with seasonal technicians and a Saturday shift, which the overhead tab prices.

Tabs 4 to 7: Materials, Labor, Overhead and Selling Costs

Materials run from $135,000 in the first quarter to $189,000 in the fourth. Labor runs from $270,000 to $438,000, including the seasonal staff. Overhead in cash runs from $297,000 to $308,000. Selling and administrative costs stay at $120,000 a quarter. Each tab draws only on Tab 1 and the tabs before it, so a change to volume flows through without manual edits.

Tab 8: The Cash Budget

Collections combine each quarter's in-quarter receipts, about 56 percent of its revenue, with the 44 percent carried over from the quarter before. The year opens with $528,000 of receivables and $120,000 of cash.

First quarter: collections $1,032,000; payments $932,000; ending cash $220,000.

Second quarter: collections $956,000; payments $980,000; ending cash $196,000.

Third quarter: collections $1,022,000; payments $1,191,000 including the first mill payment; cash before borrowing $27,000; borrow $63,000 to restore the floor.

Fourth quarter: collections $1,163,000; payments about $1,358,000 including the second mill payment and seasonal labor; cash before borrowing about negative $105,000; borrow $195,000; line balance about $258,000.

The fourth quarter is the lab's best quarter for sales and its worst for cash, because the rush is billed in December and paid in the new year.

Year-end receivables of about $554,000 are collected in the following first quarter, which retires the line. Garrison et al. (2021) note that the cash budget is where a profitable plan meets its financing needs, and this tab is where Fernhill's does.

Tabs 9 and 10: Pro Forma Statements

Income statement: revenue $4,200,000; materials $630,000; technician labor $1,320,000; variable overhead $126,000; contribution margin $2,124,000; fixed overhead $1,080,000; depreciation $160,000; selling and administrative $480,000; operating income $404,000; interest about $1,000; estimated taxes $100,000; net income about $303,000.

Balance sheet changes: cash at the $90,000 floor; receivables up to $554,000; $384,000 of mill deposits recorded as equipment not yet in service; the term loan down $180,000; the credit line at $258,000.

Sensitivity and the Facility Size

Hospitals pay at 75 days: peak borrowing about $322,000.

Fourth-quarter revenue 10 percent weaker: peak about $268,000, lower revenue but also lower seasonal costs.

Both together: peak about $325,000.

A $350,000 line covers the combined case with a small margin. Datar and Rajan (2021) recommend sizing financing to a plausible downside rather than to the base case, because the base case is the forecast least likely to occur exactly.

Decisions the Budget Makes Visible

Three choices follow. First, arrange the line before the third quarter, while the lab's balance sheet is strongest. Second, ask the mill supplier whether the second payment can move to January, which would cut the year-end peak by about $192,000. Third, offer hospital systems a small discount for 30-day payment. Hope and Fraser (2003) argue that budgets serve best when they prompt choices like these rather than serving only as fixed targets.

References

Datar, S. M., & Rajan, M. V. (2021). Horngren's cost accounting: A managerial emphasis (17th ed.). Pearson.

Garrison, R. H., Noreen, E. W., & Brewer, P. C. (2021). Managerial accounting (17th ed.). McGraw Hill.

Hope, J., & Fraser, R. (2003). Beyond budgeting: How managers can break free from the annual performance trap. Harvard Business School Press.