Account question spreadsheet

MEM-1
Empty.xlsx
Home>Business & Finance homework help>Accounting homework help>Account question spreadsheet

Instructions (Fin Acctg)

74540

Information about Dak's Cowboy Emporium is provided below. The company needs you to help estimate the external financing needs based on their target growth rate. Assume that only costs and assets vary in proportion to sales, and the firm maintains a constant plowback ratio. As you work each step, you must link to cells within the worksheet. Do not type numbers into your equations/answers.   Use the given information to: 1. Construct the company's current balance sheet. I made the yellow cells in the balance sheet slightly lighter so it is easier to tell where all you need to enter a value.   2) Calculate the financial ratios listed in the spreadsheet. Use the Name Manager to point the named cell NetIncome (currently pointing to sales revenue) to the net income amount for the current year provided in Column D. Use the name to reference this cell in any equations (NOTE: do not name cells unless instructed). Name the corresponding financial ratios in Column D with the following names: TAT, PM, DE, ROA, ROE, SGR, and IGR. Use these names to reference these cells in any equations, but do not name other cells. Calculate the financial ratios listed in Column D. Use the DuPont Identity to calculate ROA and ROE.   3) Construct a pro forma income statement using the company's sustainable growth rate as the projected growth in sales. Use the MAX function in the equation to prevent negative taxes.   4) Construct a pro forma balance sheet based on the company's sustainable growth rate. Only assume that costs and assets grow proportional to sales. 5) Use the pro forma balance sheet to calculate the external financing needs. Based on the this growth rate, what is the total growth in assets? How much is financed by internal equity? What is the external financing needed? Based on the assumptions of the SGR, what is the amount of external debt financing? 6) Assume the company wants to grow at this rate (the SGR you calculated), but without using any external financing. What dividend ($ amount) would allow the company to grow at this rate this year, but without using any external financing? What would the new payout ratio be based on that dividend? Assume the ROE does not change but the payout policy does. Given the new payout ratio, what would the new SGR be? Hint: SGR will be higher than before.

FinancialAccounting

Current Year
Sales $ 207,000
Total Costs (COGS & Admin.) 168,000
Common stock & Paid-in Capital 15,000
Retained earnings 352,620
Dividends Paid 12,600
Accounts payable 7,800
Notes payable 17,100
Long-term debt 84,000
Net income 30,810
Cash 9,420
Accounts receivable 12,600
Inventory 19,500
Net plant and equipment 435,000
Tax rate 21%
Output areas:
Dak's Cowboy Emporium
Current Balance Sheet
Problem 1 Assets Liabilities and Owners' equity
Current assets Current liabilities
Cash Accounts payable
Accounts Receivable Notes payable
Inventory Total
Total
Long-term debt
Total Liabilities
Fixed assets
Net plant and equipment Owners' equity
Common stock & Paid-in Capital
Retained earnings
Total Equity
Total assets Total liabilities and Owners' equity
Dak's Cowboy Emporium
Financial Ratios
Problem 2 Payout ratio
Plowback ratio
TAT
Profit margin
Debt-equity ratio
ROA
ROE (Answer about 8.4%)
Sustainable growth rate
Internal growth rate
Dak's Cowboy Emporium
Pro forma income statement
Problem 3 Sales
Costs
Taxable income
Taxes
Net income (Answer about $32,400)
Dividends
Add. To Ret. Earnings
Dak's Cowboy Emporium
Pro Forma Balance Sheet
Assets Liabilities and Owners' equity
Problem 4 Current assets Current liabilities
Cash Accounts payable
Accounts Receivable Notes payable
Inventory Total
Total
Long-term debt
Total Liabilities
Fixed assets
Net plant and equipment Owners' equity
Common stock & Paid-in Capital
Retained earnings
Total Equity
Total assets (Answer about $501,300) Total liabilities and Owners' equity
Problem 5 Growth in assets (Answer = $24,800)
Internal equity financing
External financing needed
External debt (excludes A/P)
Problem 6 New dividend (Answer about $7,500)
New payout ratio
New SGR
74540
39929

Instructions (Retire)

74540

As you work the problems, keep in mind that information in NPER, RATE, and PMT all need to be on the same time frame (if someone saves monthly, then the payment, the interest rate, and the number of periods must be monthly).   The required steps are listed at the bottom of the spreadsheet Retirement Problem. WARNING: You are welcome to play around with inputs to see how the answers change (in case you want to put in numbers for yourself). Just be sure to use the original values when you submit the assignment as ExPrep is checking the acuracy of the answers based on the original inputs!

Retirement Problem

Problem 1 1500.00 How much money do you plan to save each period?
0.08 Expected return on your savings before retirement (this is an EAR)
0.05 Expected return on your savings during retirement (this is an EAR)
25 Years until your retirement
25 Years you plan to be in retirement (how long your money needs to last)
12 How frequently do you save money each year? Annually (1), quarterly (4), or monthly (12 times each year)?
Problem 2 Expected return on your savings during retirement (this is an APR)
Problem 3 Expected return on your savings before retirement (this is an APR)
Problem 4 Amount you'll have in your account at retirement based on the amount you save each period (answer should be about $1.36 million).
Problem 5 Amount you could spend each period during your retirement
200000 How much money do you currently have in savings?
Problem 6 Amount you'll have in your account at retirement based on the amount you save each period plus the amount already in savings
Problem 7 Amount you could spend each period during your retirement (answer should be about $15,000).
15000 How much money would you like to have in spend each period in retirement?
Problem 8 How much money will you need to have at retirement based on the amount above in cell C20 and the years you plan to be in retirement? (answer should be about $2.6 million).
Problem 9 How much money would you need to save, starting today, to hit that target?
Time Value of Money Review 50 Points
Use the information provided in the cells to construct a spreadsheet to help someone see what to expect for retirement. Format all cells with dollar amounts or percentages as Currency or Percentage, respectively.
Start by restricting the numbers that can be used as inputs.
#1 Use Data Validation in cells C2 to C4 to be a decimal larger than zero. When a negative number is entered, the cell should return the error message "Do not enter negative numbers"
Use Data Validation in cells C5 to C7 to be a whole number larger than zero. When a negative number is entered, the cell should return the error message "Do not enter negative numbers"
We have been give EARs, but it is more convenient to work with APRs when the annuity payments are not annual. Calculate the APRs.
#2 Use the NOMINAL function to convert the effective annual return in cell C4 to an APR. Be sure your formula links to cell C7 since the compounding frequency must match the savings frequency.
#3 Use the NOMINAL function to convert the effective annual return in cell C3 to an APR. Be sure your formula links to cell C7 since the compounding frequency must match the savings frequency.
Base on the information given, calculate what will be in savings at retirement and what annuity that could purchase.
#4 Use the FV function to calculate how much will be in savings at retirement.
#5 Use the PMT function to calculate how much can be spent each period while in retirement (use the same number of periods in a year as specified in cell C7).
Many people already have money in a retirement account. We will adjust our calculations to include any current savings.
#6 Re-calculate the amount that will be in savings at retirement (cell C11) if there is already $200,000 in the retirement account. Use the optional [pv] argument in the FV function in problem #4.
#7 Use the PMT function to calculate the amount that can be spent each period in retirement given the amount you calculate cell C16.
Now we want the user to enter the amount they would like to spend each period in retirement, e.g. $15,000. Given the previous assumptions, including the current savings of $200,000, calculate the following:
#8 Calculate how much must be in the retirement account for this desired annuity in retirement. Use the PV function.
#9 Calcualte how much money must be saved each period, starting today, to hit this target amount. Use the PMT function.
Change the formatting to make the spreadsheet easier to read.
#10 Format cells with percentages using the Percentages format, and cells with dollar amounts using the Currency format. In both cases, display two decimals.
74540
39929