Complete the balance sheet budget

.

Introduction

Early one morning in March, Jordan Buford was preparing his daily work when his boss, Olivia Anton, approached him and announced, Little Annin Flagmakers (LAF) has submitted an application for a line of credit (LOC) for April through June. I want you to prepare budgeted financial statements similar to the ones you prepared for our last LOC application. I need this by 3 p.m. today for the 4 p.m. credit committee meeting. Be prepared to make a loan recommendation and to address questions from the credit committee. I have cleared your schedule. Let me know if you need anything. Kent Bank is a state bank with multiple branches that offers a variety of services for personal and commercial needs. The bank has been serving the local community for more than 110 years and prides itself on its personalized approach to provide financial services, local management, long-term stability, and a full range of deposit and lending products and services. Commercial credit decisions at Kent Bank are made by the Commercial Credit Committee, which consists of the senior commercial credit analyst and two vice presidents. Buford was recently hired by Kent Bank as a commercial analyst to provide analysis for commercial loan applications. During his undergraduate studies, he studies accounting and finance, and shortly after graduation passed the CMA (Certified Management Accountant) examination. Buford reports directly to Anton, the senior commercial credit analyst who has been with Kent Bank for 10 years. As Buford began the work, he recalled his last LOC analysis and how well received it was. He had taken the information provided by the company and developed master budgets in Excel that used an input section with numbers that could be changed for assessing different scenarios. The committee had specifically asked about the effect of a sales reduction of 2%, 5%, and 10% on the applicants cash needs. He wanted to be prepared for these types of questions.

Little Annin Flagmakers Background

LAF manufactures one product, a large durable 812 American flag, which it sells for $120. Because of the large size of the flag, this product is not sold in stores; rather it is sold through a relatively small number of online retailers. Each quarter, retailers estimate sales for the upcoming five months, revising proximate sales as necessary. In general, the retailers are reasonably good at estimating their sales needs, but some variation in demand does occur, and the retailers expect to be able to adjust orders as needed. LAF allows retailers to adjust each months purchases to 80% to 120% of the estimated sales levels. Flags are shipped to retail customers using JIT distribution so that the online retailers do not have to store inventory. Typical sales for the flag are 1,800 units per month with seasonal increases in April through August. Sales estimates are 2,500 units in April, 6,000 units in May, 3,000 units in June, 2,500 units in July, and 2,000 units in August. Customers historically have paid 40% of their purchases in the month of the sale, 55% in the following month, and the remaining 5% is uncollectible.

Manufacturing and SG & A costs

The flags are made in one plant, which has a capacity of 6,200 flags per month. LAF budgets have 20% of next months 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 materials and each flag requires five pounds of fabric at $7 per pound. LAF plans to have 40% of next months 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 $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 $57,950 per month, of which $20,000 is property, plant, and equipment (PPE) depreciation. Variable manufacturing overhead, including indirect materials, indirect labor, and other costs, is estimated at $10 per direct labor hour. The selling and administrative expenses include variable selling costs (primarily shipping) of $1.25 per unit and fixed costs of $63,000 per month, of which $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 $75 per unit. The company expects to have 4,600 pounds of fabric on hand, valued at $7 per pound. Other expected account balances include accounts payable at $55,000, accounts receivable at $132,000, cash at $37,745, land at $520,000, and building and equipment at $1,800,000 with accumulated depreciation of $750,000. LAF has no long-term debt; common stock is valued at $500,000 and is not expected to change during the quarter; expected retained earnings as of March 31 are $1,247,695. LAF budgets for $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 $15,000 are paid each month. During the quarter, LAF planned to purchase equipment in May and June for $47,820 and $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 $60,000 to cover production costs during the seasonal increase in business. Kent Bank uses the following terms of 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.

Requirements:

1. Using the data input provided (Exhibit 1), prepare LAFs master budgets in Excel. Do not hard-code numbers into the spreadsheet, except in the financing section of the cash budget.

2. Conduct a sensitivity analysis, decreasing sales 2%, 5%, and 10% for April through August. New sales levels are provided in Exhibit 2. Adjust the financing and cash needs at these new sales levels.

3. Determine a credit recommendation for Kent Bank, to lend or not.

------------------------------------

QUESTION:

Complete balance sheet budget

Exhibit 1. Excel Data Input Section B B A Input Data (USS) 30 1 2 31 32 A Selling & Administrative (S&A) Costs Variable S&A cost per unit sold Fixed S&A cost per month Depreciation in fixed S&A cost 3 US$1.25 US$63,000 US$10,000 4 33 5 34 Budgeted Sales April (units) May (units) June (units) July (units) August (units) Expected 2,500 6,000 3,000 2,500 2,000 6 35 36 7 Other Cash Outflows Cash dividends paid each month Equipment purchases May Equipment purchases June 8 37 38 US$15,000 US$47,820 US$154,600 9 10 Selling price/unit US$120.00 39 11 40 12 41 Desired Ending Inventory Finished goods Raw materials Cash 13 Cash Collection Pattern Month of sale Following month Uncollectible 42 20% 40% US$30,000 40% 55% 5% 14 43 15 44 16 45 17 46 18 Cash Payments for Materials Month of purchase Following month 40% 47 19 60% 48 20 49 Beginning Account Balances on March 31 Cash Accounts receivable Finished goods inventory (at US$75/unit) Finished goods cost per unit Finished goods inventory (units) Raw materials (at US$7.00/1b.) Raw materials (lb.) Accounts payable US$37,745 US$132,000 US$30,750 US$75.00 410 US$32,200 4,600 US$55,000 50 21 22 51 23 52 53 24 5 US$7.00 0.5 US$40.00 US$10 Production Requirements Raw material per unit (Ib.) Raw material cost per lb. Direct labor hours per unit Direct labor rate per hour Variable manufacturing overhead rate per direct labor hour Fixed manufacturing overhead cost per month Depreciation in Fixed manufacturing overhead 25 54 26 55 56 27 57 US$57,950 US$20,000 Land Buildings and equipment Accumulated depreciation Common stock Retained earnings US$520,000 US$1,800,000 (US$750,000) US$500,000 US$1,247,695 28 58 29 59 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 Exhibit 1. Excel Data Input Section B B A Input Data (USS) 30 1 2 31 32 A Selling & Administrative (S&A) Costs Variable S&A cost per unit sold Fixed S&A cost per month Depreciation in fixed S&A cost 3 US$1.25 US$63,000 US$10,000 4 33 5 34 Budgeted Sales April (units) May (units) June (units) July (units) August (units) Expected 2,500 6,000 3,000 2,500 2,000 6 35 36 7 Other Cash Outflows Cash dividends paid each month Equipment purchases May Equipment purchases June 8 37 38 US$15,000 US$47,820 US$154,600 9 10 Selling price/unit US$120.00 39 11 40 12 41 Desired Ending Inventory Finished goods Raw materials Cash 13 Cash Collection Pattern Month of sale Following month Uncollectible 42 20% 40% US$30,000 40% 55% 5% 14 43 15 44 16 45 17 46 18 Cash Payments for Materials Month of purchase Following month 40% 47 19 60% 48 20 49 Beginning Account Balances on March 31 Cash Accounts receivable Finished goods inventory (at US$75/unit) Finished goods cost per unit Finished goods inventory (units) Raw materials (at US$7.00/1b.) Raw materials (lb.) Accounts payable US$37,745 US$132,000 US$30,750 US$75.00 410 US$32,200 4,600 US$55,000 50 21 22 51 23 52 53 24 5 US$7.00 0.5 US$40.00 US$10 Production Requirements Raw material per unit (Ib.) Raw material cost per lb. Direct labor hours per unit Direct labor rate per hour Variable manufacturing overhead rate per direct labor hour Fixed manufacturing overhead cost per month Depreciation in Fixed manufacturing overhead 25 54 26 55 56 27 57 US$57,950 US$20,000 Land Buildings and equipment Accumulated depreciation Common stock Retained earnings US$520,000 US$1,800,000 (US$750,000) US$500,000 US$1,247,695 28 58 29 59 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