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.
CaseStudy
Data
Is King
(units)
00
(units)
(units)
(units)
(units)
5%
40%
5
hours per unit
s
40%
(units)
4,600
,000
5%
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$) | |||||||||||
| Quarter | ||||||||||||
| Budgeted sales | ||||||||||||
| Selling price per unit | ||||||||||||
| Total Sales | ||||||||||||
| Schedule of Expected Cash Collections (US$) | ||||||||||||
| Beginning balance | ||||||||||||
| April sales | ||||||||||||
| May sales | ||||||||||||
| June sales | ||||||||||||
| Total Cash Collections | ||||||||||||
| Accounts Receivable as of June 30 | ||||||||||||
| Production Budget | ||||||||||||
| Add: Desired ending inventory | ||||||||||||
| Total needs | ||||||||||||
| Less: Beginning inventory | ||||||||||||
| Required Production | ||||||||||||
| Direct Materials Budget (US$) | ||||||||||||
| Required production in units | ||||||||||||
| Raw materials per unit (lbs.) | ||||||||||||
| Production needs (lbs.) | ||||||||||||
| Raw materials to be purchased | ||||||||||||
| Cost of raw materials | ||||||||||||
| Total Cost | ||||||||||||
| Schedule of Expected Cash Disbursements for Material (US$) | ||||||||||||
| April purchases | ||||||||||||
| May purchases | ||||||||||||
| June purchases | ||||||||||||
| Total Cash Disbursements for Materials | ||||||||||||
| Accounts Payable as of June 30 | ||||||||||||
| Direct Labor Budget (US$) | ||||||||||||
| Units | ||||||||||||
| Total direct labor hours needed | ||||||||||||
| Direct labor cost per hour | ||||||||||||
| Total Direct Labor Cost | ||||||||||||
| Manufacturing Overhead Budget (US$) | ||||||||||||
| Budgeted direct labor hours | ||||||||||||
| Variable | MOHD rate | |||||||||||
| Total variable MOHD | ||||||||||||
| Fixed MOHD expense | ||||||||||||
| Total MOHD expense | ||||||||||||
| Less: Depreciation | ||||||||||||
| Cash Disbursements for MOHD | ||||||||||||
| /direct labor hour | ||||||||||||
| Unit Product Cost | ||||||||||||
| Absorption cost per unit | Quantity | Cost/unit | ||||||||||
| Direct materials | ||||||||||||
| Manufacturing overhead | ||||||||||||
| Cost of Goods Sold Budget (USD) | ||||||||||||
| Cost of Goods Sold (FIFO) | ||||||||||||
| Beginning finished goods inventory | ||||||||||||
| Add: Cost of goods manufactured | ||||||||||||
| Good available for sale | ||||||||||||
| Less: Ending finished goods inventory | ||||||||||||
| Cost of Good Sold | ||||||||||||
| Selling and Administrative Expense Budget (US$) | ||||||||||||
| Budgeted sales in units | ||||||||||||
| Variable S&A per unit | ||||||||||||
| Total variable S&A | ||||||||||||
| Total fixed S&A | ||||||||||||
| Total S&A expense | ||||||||||||
| Cash Disbursements for S&A | ||||||||||||
| Cash Budget (US$) | ||||||||||||
| Beginning Cash Balance | ||||||||||||
| Add: Receipts | ||||||||||||
| Cash | ||||||||||||
| 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 | ||||||||||||
| 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 | |||||||||||
| Less uncollectible amounts | ||||||||||||
| Budgeted Balance Sheet (US$) | ||||||||||||
| Ending March 31 | ||||||||||||
| Current assets | ||||||||||||
| Accounts receivable | ||||||||||||
| Raw materials 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.