Quantitative Analysis Word And Excel
Instructions
All information required for this assignment is provided below:
Read the Cash Is King case study and complete the following requirements.
Quantitative Analysis:
- 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.
Qualitative Analysis:
In a 2 page report, based on the results of your quantitative analysis:
- Determine a credit recommendation for Kent Bank, to lend or not. Justify your credit decision.
- Explain why the cash budget is more important to a bank than the accounting net income when determining a credit decision.
Deliverables
- Quantitative Analysis (Excel Required): You are required to use the provided Excel workbook to complete the quantitative analysis for this assignment.
- Qualitative Analysis (Word Required): Prepare a 2-page summary addressing the required qualitative analysis, as noted in the Student Workbook. Your paper is required to be formatted according to APA requirements. Be sure to incorporate key concepts from this unit’s readings and properly cite your references according to APA requirements. Do NOT embed the results of your quantitative analysis in your Word document. You should only reference parts of your quantitative analysis in your written analysis. Your written responses to the qualitative prompts should not be presented in a question and answer format.
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.
Cash Is King
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"
