Quantitative Analysis Word and Excel

 

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
Order Essay

Read the

Cash Is King

case study and complete the following requirements.

Quantitative Analysis: 

  1. 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:

  1. Determine a credit recommendation for Kent Bank, to lend or not. Justify your credit decision.
  2. 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.

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.

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

April May June

(units)

Little Annin Flagmakers

April May June Quarter

Accounts receivable

Little Annin Flagmakers

April May June Quarter July August
Budgeted sales

Little Annin Flagmakers

April May June Quarter

Add: Desired ending inventory
Total needs
Less: Beginning inventory

of Raw Materials

Little Annin Flagmakers

April May June Quarter

Accounts payable

Beginning balance

Little Annin Flagmakers

April May June Quarter

to be produced

Direct labor hours per unit

Little Annin Flagmakers

April May June Quarter

MOHD rate

Little Annin Flagmakers

(US$)

Cost

Direct labor
Unit Product Cost

Little Annin Flagmakers

Units Cost/unit Total Cost

Little Annin Flagmakers

April May June Quarter

Less: Depreciation

Little Annin Flagmakers

April May June Quarter

collections

Little Annin Flagmakers

Sales
Net Sales
Little Annin Flagmakers

Ending June 30

Cash
Finished goods inventory
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

What Will You Get?

We provide professional writing services to help you score straight A’s by submitting custom written assignments that mirror your guidelines.

Premium Quality

Get result-oriented writing and never worry about grades anymore. We follow the highest quality standards to make sure that you get perfect assignments.

Experienced Writers

Our writers have experience in dealing with papers of every educational level. You can surely rely on the expertise of our qualified professionals.

On-Time Delivery

Your deadline is our threshold for success and we take it very seriously. We make sure you receive your papers before your predefined time.

24/7 Customer Support

Someone from our customer support team is always here to respond to your questions. So, hit us up if you have got any ambiguity or concern.

Complete Confidentiality

Sit back and relax while we help you out with writing your papers. We have an ultimate policy for keeping your personal and order-related details a secret.

Authentic Sources

We assure you that your document will be thoroughly checked for plagiarism and grammatical errors as we use highly authentic and licit sources.

Moneyback Guarantee

Still reluctant about placing an order? Our 100% Moneyback Guarantee backs you up on rare occasions where you aren’t satisfied with the writing.

Order Tracking

You don’t have to wait for an update for hours; you can track the progress of your order any time you want. We share the status after each step.

image

Areas of Expertise

Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.

Areas of Expertise

Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.

image

Trusted Partner of 9650+ Students for Writing

From brainstorming your paper's outline to perfecting its grammar, we perform every step carefully to make your paper worthy of A grade.

Preferred Writer

Hire your preferred writer anytime. Simply specify if you want your preferred expert to write your paper and we’ll make that happen.

Grammar Check Report

Get an elaborate and authentic grammar check report with your work to have the grammar goodness sealed in your document.

One Page Summary

You can purchase this feature if you want our writers to sum up your paper in the form of a concise and well-articulated summary.

Plagiarism Report

You don’t have to worry about plagiarism anymore. Get a plagiarism report to certify the uniqueness of your work.

Free Features $66FREE

  • Most Qualified Writer $10FREE
  • Plagiarism Scan Report $10FREE
  • Unlimited Revisions $08FREE
  • Paper Formatting $05FREE
  • Cover Page $05FREE
  • Referencing & Bibliography $10FREE
  • Dedicated User Area $08FREE
  • 24/7 Order Tracking $05FREE
  • Periodic Email Alerts $05FREE
image

Our Services

Join us for the best experience while seeking writing assistance in your college life. A good grade is all you need to boost up your academic excellence and we are all about it.

  • On-time Delivery
  • 24/7 Order Tracking
  • Access to Authentic Sources
Academic Writing

We create perfect papers according to the guidelines.

Professional Editing

We seamlessly edit out errors from your papers.

Thorough Proofreading

We thoroughly read your final draft to identify errors.

image

Delegate Your Challenging Writing Tasks to Experienced Professionals

Work with ultimate peace of mind because we ensure that your academic work is our responsibility and your grades are a top concern for us!

Check Out Our Sample Work

Dedication. Quality. Commitment. Punctuality

Categories
All samples
Essay (any type)
Essay (any type)
The Value of a Nursing Degree
Undergrad. (yrs 3-4)
Nursing
2
View this sample

It May Not Be Much, but It’s Honest Work!

Here is what we have achieved so far. These numbers are evidence that we go the extra mile to make your college journey successful.

0+

Happy Clients

0+

Words Written This Week

0+

Ongoing Orders

0%

Customer Satisfaction Rate
image

Process as Fine as Brewed Coffee

We have the most intuitive and minimalistic process so that you can easily place an order. Just follow a few steps to unlock success.

See How We Helped 9000+ Students Achieve Success

image

We Analyze Your Problem and Offer Customized Writing

We understand your guidelines first before delivering any writing service. You can discuss your writing needs and we will have them evaluated by our dedicated team.

  • Clear elicitation of your requirements.
  • Customized writing as per your needs.

We Mirror Your Guidelines to Deliver Quality Services

We write your papers in a standardized way. We complete your work in such a way that it turns out to be a perfect description of your guidelines.

  • Proactive analysis of your writing.
  • Active communication to understand requirements.
image
image

We Handle Your Writing Tasks to Ensure Excellent Grades

We promise you excellent grades and academic excellence that you always longed for. Our writers stay in touch with you via email.

  • Thorough research and analysis for every order.
  • Deliverance of reliable writing service to improve your grades.
Place an Order Start Chat Now
image

Order your essay today and save 30% with the discount code Happy