Develop appropriate formulas to automatically change sheet

Assignment Help Financial Accounting
Reference no: EM131330770

Assignment: Intermediate Accounting for Finance Working Capital Case Study

Your job is to make ABC cash flow in years 2 and 3 (positive ending cash balances); which means you must balance ABC's cash conversion cycle days with ABC's increase in sales volume for years 2 and 3. YOU MUST DETERMINE ABC'S SALES for years 2 and 3 and the RELEVANT days in accounts receivables, days in inventory and days in accounts payable in order for ABC to cash flow. You will probably have to try several different combinations until ABC cash flows. Other than that, use the other information given in the case below.

ABC Company is a start-up medical equipment manufacturing company. ABC developed a blood testing device that accurately diagnoses the Ebola virus in 15 minutes or less. The financing of ABC consisted of founders' equity investment totaling $369,074 and a $4,000,000 bank loan.

In year 1 ABC generated a small net profit of $4,718 on total sales of $3,000,000, a gross margin of 35% and operating expenses of 25%. Also in year 1 ABC generated a cash deficiency totaling $4,369,074, which means they spent all of their financing in year 1. ABC's year 1 cash conversion cycle totaled 51 days, which consisted of days in accounts receivable of 45 days, days in inventory of 51 days and days in accounts payable of 45 days. Unfortunately, the founders have no more cash to invest in the company and the company does not qualify for additional debt financing.

ABC believes it can reasonably achieve a 37% gross margin for years 2 and 3 and reduce operating expenses to 19% and 14% of sales for years 2 and 3, respectively. Furthermore, in order to increase sales ABC must invest $75,000 and $950,000 in capital expenditures in years 2 and 3, respectively. Also, ABC must pay its debt principal and interest obligations in years 2 and 3.

Attached are schedules of ABC's additional information, balance sheets, income statements and cash flows. Based on the given information you must determine ABC's increase in sales and days in accounts receivable, days in inventory and days in accounts payable. Based on those days you must also compute ABC's accounts receivable, inventory and accounts payable for years 2 and 3 in order to compute ABC's cash flows. Additionally, based on your results, you must complete ABC's balance sheets, income statements and cash flows for years 2 and 3.

I recommend you begin with the attached excel spreadsheets, but develop the appropriate formulas to automatically change the balance sheet, income statement and cash flows when you change your inputs. For example, you should design your spreadsheet so that when you change days, excel will compute accounts receivable, inventory and accounts payable on the balance sheet and cash flows. Furthermore, I suggest you design your spreadsheet so that when you change the percentage increase in sales it will also automatically update the sales line in the income statement and net income or loss line on the cash flows (this is what we did in our entire semester balance sheet, income statement and statement of cash flow assignments earlier in the semester). The attached schedules can be used as a guide to design your spreadsheets.

No handwritten answers will be accepted.

Attachment:- Working_Capital_Case.pdf

Reference no: EM131330770

Questions Cloud

What is the key in this system : What is the key in this system? Why doesn't it make sense to use the initial contents of the LFSR as the key or as part of the key?
What dream have you had about your life : Kushner writes about becoming less to become more and that becoming less means giving up "the dream." What dream have you had about your life that you have had or may have to give up? Answer in 200 Words
Identify a real person from history : Identify a real person from history whose personality or actions seem to mimic any of the characters about whom we have read - How does he or she exhibit any of the characteristics of the mythological characters we have studied?
Discuss about the internal disaster plans : Review the elements required for a disaster plan addressing an internal disaster. Based on your review, create a 2- to 3-page report in a Microsoft Word document covering the hospital's (internal) disaster plan for how the staff reacts and carries..
Develop appropriate formulas to automatically change sheet : ACCT 321- I recommend you begin with the attached excel spreadsheets, but develop the appropriate formulas to automatically change the balance sheet, income statement and cash flows when you change your inputs.
Calculate the forecast for period : The next two questions involve The Grand Bakery which produces 60 special sourdough rolls every day. Any rolls that are not sold each day are given to the employees. They have collected sales data from the past week:
Engaged in other long-term client projects : A company provides IT services to companies that need additional temporary IT support. Currently, the company has about 90% of its staff engaged in other long-term client projects.
Discuss about the globalization and outsourcing : The purpose of this short research paper is to ensure proper understanding and application of APA style as required in all Ashford courses.Using the Ashford Online Library, develop an annotated bibliography on one of the following factors that ha..
Case study-multiperiod production planning : Attached is the case study. This needs to be completed in Excel and any diagrams need to be able to edited, they cannot be cut and pasted into the excel spreadsheet. ALSO please show your work in excel, utilizing solver. I need to be able to see h..

Reviews

Write a Review

Financial Accounting Questions & Answers

  Construction of a new warehouse

On January 1, 2016, Dreamworld Co. began construction of a new warehouse. The building was finished and ready for use on September 30, 2017. Expenditures on the project were as follows: January 1, 2016 $307,000 September 1, 2016 $459,000 December 31,..

  Budgeted contribution margin income statement-alternatives

Jordan Company, a wholesale electrical supply company, uses independent sales agents to market the company’s products throughout the West Coast region.  Prepare a budgeted contribution margin income statement for the two alternatives. Calculate the c..

  Compute a full years depreciation expense for new equipment

Compute a full year's depreciation expense for the new equipment, using each of the two sets of estimates. How would cash outflows for income taxes be affected by the estimates?

  Uses standard costing-labor rate variance

The Collins Corporation uses standard costing and has established the following direct material and direct labor standards for each unit of the single product it makes:

  What is browns breakeven point in units

Brown Appliances Store sells microwaves for $350 each which cost Brown $200 each. Brown pays $2,570 for the store rent. Brown pays a commission of 10% of the sales price for each microwave sold to its salesman. What is Brown’s breakeven point in unit..

  Record the incurrence of actual manufacturing overhead costs

During February, Irving Corporation incurred $92,000 of actual Manufacturing Overhead costs. During the same period, the Manufacturing Overhead applied to Work in Process was $85,000. The journal entry to record the incurrence of the actual Manufactu..

  The investigative approach for a forensic accountant

The investigative approach for a forensic accountant is based on which of the following? (Points : 2) Methods following generally accepted auditing standards Methods following the Sarbanes Oxley Act Methods to determine who is responsible Methods to ..

  Total profit or loss at the end of the trading period

You are short 25 gasoline futures contracts, established at an initial settle price of $2.46 per gallon, where each contract represents 42,000 gallons. Over the subsequent four trading days, gasoline settles at $2.42, $2.47, $2.50, and $2.56, respect..

  What is mark to market accounting

What is mark to market accounting? Which stakeholder do you think will be most affected in mark to market accounting? Identify two critical accounts that will be most affected by the suspension of mark to market.

  About the numbers and not subjective criteria

Balanced scorecard is one of those management fads that will disappear several years in the future. In addition, managerial accounting should be about the numbers and not subjective criteria. Please support or refute the above statements.

  Bonds instead of an all-equity capital structure

Continental Corporation has $1,000,000 in common stock and $1,000,000 in 7 percent 10-year bonds in its capital structure. How much does Continental save in taxes in current dollars over the 10 years the bonds are outstanding by having these bonds in..

  Classify the charges associated with cellular phone service

Assume that Jana will use, on average, 45 minutes per month in local calling.  Classify the charges associated with the cellular phone service as (a) committed resources or (b) flexible resources.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd