Instructions
All information required for this assignment is provided below:
Don't use plagiarized sources. Get Your Custom Essay on
Quantitative Analysis Word and Excel
Just from $13/Page
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.
CaseStudy
Data
|
| Cash |
Is King
| Exhibit 1. Excel Data Input Section |
| Input Data (all currency in US$) |
|
| Budgeted
| Sales |
| Expected |
|
|
|
|
|
|
|
|
|
|
| April |
(units)
| 2,
| 5 |
00
|
|
|
|
|
|
|
|
|
|
| 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 |
5
|
| 5% |
| 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)
| Cost |
s
| 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 |
$15
| 4,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
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.
