Expert Answer:BUSS400 Regina Financial statement data for the Mu

  

Solved by verified expert:answer questions in the following word doc and for question number 3 answer and answer in excel file so calculation can be seen. excel templet attached
hw_3_bus_400_studypool_upload.xlsx

studypool_upload_hw_3.docx

Don't use plagiarized sources. Get Your Custom Essay on
Expert Answer:BUSS400 Regina Financial statement data for the Mu
Just from $10/Page
Order Essay

Unformatted Attachment Preview

Month
January
February
March
April
May
June
July
August
September
October
November
December
Minimum cash
requirement
$10,000
$10,000
$10,000
$10,000
$10,000
$10,000
$15,000
$15,000
$20,000
$20,000
$10,000
$10,000
$10,000
Cash Receipts
Cash
Disbursements
$100,000
$90,000
$80,000
$100,000
$120,000
$160,000
$200,000
$250,000
$250,000
$200,000
$140,000
$100,000
$100,000
$110,000
$110,000
$150,000
$180,000
$180,000
$180,000
$180,000
$150,000
$110,000
$100,000
$100,000
Monthly Net
inflow = Cash
Receipt – Cash
Disbursements
??
??
??
??
??
??
??
??
??
??
??
??
ending cash
without
financing
$10,000
??
??
??
??
??
??
??
??
??
??
??
??
difference between
minimum cash and
ending cash level
with financing (cash
need)
$0
??
??
??
??
??
??
??
??
??
??
??
??
Cash conversion cycles
inventory-to-sales
sale-to-cash
purchase-to-payment
cash conversion cycle
Inventories
Receivables
Payables
Accrued liabilities
Net sales
Cogs
sales per day
cogs per day
payable + accrued liabilities
inventory-to-sales
sales-to-cash
purchase-to-payment
Cash conversion cycle
<- avg inventory / cogs per day <- avg receivable / sales per day <- avg (payable + accrued liab.) / cogs per day <-- (inventory-to-sales) + (sales-to-cash) - (purchase-to-payment) 2012 2013 450,000.00 200,000.00 130,000.00 50,000.00 570,000.00 300,000.00 180,000.00 70,000.00 1,600,000.00 960,000.00 average average average average current current current current average ase-to-payment) ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Cash Budgeting Analysis 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 Instructions: 1) use excel formula to replicate the results for Oct and Nov 2) apply the formula for Dec-14. Initial Balance Sheet Cash Account receivable Inventories Total Current Assets Fixed Asset Total Asset Account payable Short-term notes Long-term debt cumulative additional borrowing Total liabilities Equity Total liabilities & Equity Sep-14 50 700 500 1250 750 2000 0 800 400 0 1200 800 2000 Information from the original questions Sep-14 700 50 500 800 400 10 750 0 700 0 Oct-14 1000 80 500 800 400 10 740 0 1000 0 Nov-14 1500 80 500 800 400 10 730 0 1500 0 Dec-14 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? Sales (S) Less: Cogs (80% of S) Gross Profit Less: Depreciation Other expense (7% of S) EBIT Sep-14 700.00 560.00 140.00 10.00 49.00 81.00 Oct-14 1,000.00 800.00 200.00 10.00 70.00 120.00 Nov-14 1,500.00 1,200.00 300.00 10.00 105.00 185.00 Dec-14 ?? ?? ?? ?? ?? ?? Less: Interest EBT Less: Tax (40%) Net Income 0.00 81.00 32.40 48.60 12.00 108.00 43.20 64.80 14.55 170.45 68.18 102.27 ?? ?? ?? ?? <- 80% of sales <- sales - cogs <- given <- 7% of sales <- Gross profit - Depr - Other expense <- previous total borrowing * monthly interest rate (1%), assume the same rate for both long and short-term borrowing <- EBIT - Interest <- tax rate * EBT <- EBT - Tax dividend addition to retained earnings beginning equity ending equity 0.00 48.60 800 0.00 64.80 800.00 864.8 0.00 102.27 864.80 967.1 ?? ?? ?? ?? <-- no dividend would be paid, or = 0 <-- net income - dividend <-- equity from last month <-- beginning equity + addition to retained earnings Cash Account receivable Inventories Total Current Assets Net Fixed Asset Total Asset Sep-14 50 700.00 500 1250 750 2,000.0 Oct-14 80 1,000.00 500 1580 740.0 2,320.0 Nov-14 80 1,500.00 500 2080 730.0 2,810.0 Dec-14 ?? ?? ?? ?? ?? ?? Account payable Short-term notes Long-term debt Total liabilities Common stock Cumulative retained earnings Total equity Total liabilities & Equity 0 800 400 1200 800.00 0.00 800.00 2,000.0 0.0 1,055.2 400.0 1,455.2 800.0 64.8 864.8 2,320.0 0.0 1,442.9 400.0 1,842.9 800.0 167.1 967.1 2,810.0 ?? ?? ?? ?? ?? ?? ?? ?? <-- given, = 0 <<-- balancing term, total asset - total equity - all other liabilities <-- given, no change <-- acc payable + short-term notes + long-term debt <-- remain the same, no issurance of new equity 1200 0 1,455.2 1,200.0 1,842.9 1,455.2 ?? ?? <-- short-term notes + long-term debt <-- total borrowing from prevoius month Oct-14 Nov-14 Dec-14 700.0 800.0 70.0 12.0 43.2 -225.2 50.0 -175.2 80.0 255.2 1,000.0 1,200.0 105.0 14.6 68.2 -387.7 80.0 -307.7 80.0 387.7 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? Statement of Cash Flow ($ in Thousands) Oct-14 Nov-14 Dec-14 Operating activities Net Income plus: Depreciation less: Increase In Acc Receivable less: Increase In Inventory Cash flow from Operation 64.80 10.00 300.00 0.00 -225.20 102.27 10.00 500.00 0.00 -387.73 ?? ?? ?? ?? ?? <-- from income statement <-- from income statement <-- change in Acc Receivable from previous month <-- change in inventory from previous month <-- net income + depr - ΔAR - Δinventory -10.0 10 0.0 -10.0 10 0.0 ?? ?? ?? <-- net fixed asset - previous net fixed assets <-- from income statement <-- from given 0.00 255.20 0.00 0.00 255.20 0.00 387.73 0.00 0.00 387.73 ?? ?? ?? ?? ?? <-- from additional financing 30.0 50.0 80.0 0.0 80.0 80.0 ?? ?? ?? <-- cash from from operation, investing, and financing <-- from previous month ending cash <- beginning cash balance + net cash flow Sales forecast Designed cash level Inventories Exisiting short-term notes existing long-term debt Depreciation Fixed asset dividend account receivable capital expenditure <-- collect information from the questions <-- all sales goe to account receivable Income statement Balance sheet <-- from given <-- from current monthly sales <-- given by inventory policy <-- cash + Acc receivable + Inventories <-- previous fixed asset - depreciation <-- Total current asset + Fixed asset <-- total asset (from balance sheet identity) Summary in financing Total borrowing previous total borrowings Cash Budget ($ in Thousands) Collection fom Recivables less: Purchases (cogs) less: Other Expense less: Interest Payments less: Payment of Taxes Net Monthly Cash Flow beginning cash ending cash without additional borrowing cash balance required additional borrowing (fund needed) Investing activities less: Change in fixed asset less: Depreciation Cash flow from investing Financing activities less: dividend plus: increase in short-term notes plus: increase in long-term debt plus: increase in common stock Net cash flow from Financing Summary Net Cash Flow Beginning Cash Balance Ending Cash Balance <-- previous sales would be collected this month <-- from income statement <-- from income statement <-- from income statement <-- from income statement <-- collection - cogs - other expense - Interest - tax <-- previous month cash <-- Net Monthly cash flow + beginning cash <-- from designed cash level <-- target cash - ending cash without borrowing Question 1 Assuming minimum cash on hand requirements are $10,000 a month through May, increase to $15,000 in June and July, increase further to $20,000 in August and September, and return to the $10,000 per month level beginning in October. Month Minimum cash requirement Cash Receipts Cash Disbursements $10,000 January $10,000 $100,000 $100,000 February $10,000 $90,000 $110,000 March $10,000 $80,000 $110,000 April $10,000 $100,000 $150,000 May $10,000 $120,000 $180,000 June $15,000 $160,000 $180,000 July $15,000 $200,000 $180,000 August $20,000 $250,000 $180,000 September $20,000 $250,000 $150,000 October $10,000 $200,000 $110,000 November $10,000 $140,000 $100,000 December $10,000 $100,000 $100,000 Questions: 1. 2. 3. Determine whether Itsar Products will have a cash need during the next year.
If Itsar Products has a cash need, indicate the month when the need will begin and determine the month and amount
when the maximum need will occur.
Determine whether the cash need (if any) can be repaid within the next year.
Question 2
[Cash Conversion Cycle]
Two years of financial statement data for the Munich Export Corporation are shown below.
1.
2.
Calculate the inventory-to-sale, sale-to-cash, and purchase-to-payment conversion periods for Munich Exports for
2013.
Calculate the length of Munich Exports’ cash conversion cycle for 2013.
inventory-to-sales = avg inventory / cogs per day
sale-to-cash = avg receivable / sales per day
purchase-to-payment = avg (payable + accrued liab.) / cogs per day
cash conversion cycle = (inventory-to-sales) + (sales-to-cash) – (purchase-to-payment)
Balance Sheet
2012
2013
Cash
$50,000
$50,000
Accounts Receivables
200,000
300,000
Inventories
450,000
570,000
700,000
920,000
300,000
380,000
$1,000,000
$1,300,000
Accounts Payable
130,000
$180,000
Accruals
50,000
70,000
Bank Loan
90,000
90,000
270,000
340,000
Long-Term Debt
400,000
550,000
Common Stock ($.05 par)
50,000
50,000
Additional Paid-in-Capital
200,000
200,000
Retained Earnings
80,000
160,000
$1,000,000
$1,300,000
2012
2013
Net Sales
$1,300,000
$1,600,000
Cost of Goods Sold
780,000
960,000
520,000
640,000
Marketing
130,000
160,000
General & Administrative
150,000
150,000
Depreciation
Total Current Assets
Fixed Assets, Net
Total Assets
Total Current Liabilities
Total liability. & Equity
Income Statement
Gross Profit
40,000
55,000
EBIT
200,000
275,000
Interest
45,000
55,000
155,000
220,000
Income Taxes
62,000
88,000
Net Income
$93,000
$132,000
Earnings Before Taxes
Cash Budgeting
Artero Corporation is a traditional toy products retailer that recently also started an Internet-based subsidiary that
sells toys online. A markup is added on goods the company purchases from manufacturers for resale. Swen Artero,
the company president, is preparing for a meeting with Jennifer Brown, a loan officer with First Banco Corporation, to
review year end financing requirements. After discussions with the company’s marketing manager Rolf Eriksson and
finance manager Lisa Erdinger, sales over the next three months were forecasted as follows.
Sales Forecast
Oct-14
Nov-14
Dec-14
1000
1500
3000
Balance sheet as of Sep 2014
Initial Balance Sheet
Sep-14
Cash
50
Account receivable
700
Inventories
500
Total Current Assets
1250
Fixed Asset
750
Total Asset
2000
Account payable
Short-term notes
Long-term debt
cumulative additional borrowing
Total liabilities
Equity
Total liabilities & Equity
0
800
400
0
1200
800
2000
All sales are made on credit terms of net 30 days and are collected the following month and no bad debts are
anticipated. The accounts receivable on the balance sheet at the end of September thus will be collected in
October. The October sales will be collected in November, and so on. Inventory on hand represents a minimum
operating level (or “safety” stock), which the company intends to maintain. Cost of goods sold average 80 percent of
sales. Inventory is purchased in the month of sale and paid for in cash. Other cash expenses average 7 percent of
sales. Depreciation is $10,000 per month. Assume taxes are paid monthly and the effective income tax rate is 40
percent for planning purposes.
The annual interest rate on outstanding long=term debt and bank loans (notes payable) is 12%. There are no
capital expenditures planned during the period, and no dividends will be paid. The company’s desired end-of-month
cash balance is $80,000. The president hopes to meet any cash shortages during the period by increasing the firm’s
notes payable to the bank. The interest rate on new loans will be 12%.
Template:
1 Prepare monthly pro forma income statements for October, November, and December, and for the quarter ending
December 31, 2014.
2 Prepare both a monthly cash budget and pro forma statements of cash flows for the October, November, and
December 2014.
3 Describe your findings and indicate the maximum amount of bank borrowing that is needed.
4 Excel instructions:
1
First, use excel formula to replicate the results for the first two months.
2
Then, extend the calculation to the third month.
_efu_hw3_survivalStage_excel.xlsx
To answer, please report
92
Statement of Cash Flow ($ in Thousands)
93
94
Operating activities
95
Net Income
96
plus: Depreciation
97
less: Increase In Acc Receivable
98
less: Increase In Inventory
99
Cash flow from Operation
100
101
Investing activities
102
less: Change in fixed asset
103
less: Depreciation
104
Cash flow from investing
105
106
Financing activities
107
less: dividend
108
plus: increase in short-term notes
109
plus: increase in long-term debt
110
plus: increase in common stock
111
Net cash flow from Financing
112
113
Summary
114
Net Cash Flow
115
Beginning Cash Balance
116
Ending Cash Balance
Find December 14 month
Oct-14
Nov-14
64.80
10.00
300.00
0.00
-225.20
102.27
10.00
500.00
0.00
-387.73
-10.0
10
0.0
-10.0
10
0.0
0.00
255.20
0.00
0.00
255.20
0.00
387.73
0.00
0.00
387.73
30.0
50.0
80.0
0.0
80.0
80.0

Purchase answer to see full
attachment

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

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