I need the excel completed, please read the BODIE Case and follow the instructions & hints on the instructions! Cashflow, pro forma, etc. Finance majors only!

00
On April 29, 2022April 29, 2022By In To Get Answers: WhatsApp/Text +16469781313

I need the excel completed, please read the BODIE Case and follow the instructions & hints on the instructions! Cashflow, pro forma, etc. Finance majors only!

FI 4020

Submission Date:

Take home exam will be based on the case: Bodie Industrial Supply Inc.

At end of your analysis, you will address the following question:

Question: Should Mr. MacDougall provide the loan of $200,000 to BIS for the warehouse extension that the company was planning to undertake by the beginning of November, 2006? Provide your reasoning. (Include your response in the excel spreadsheet at the designated area, where the question is provided). 

Following files are available in for the exam:

  1. Bodie Industrial Supply Inc. case write-up (pdf): print the file
  2. Financial Statement for 2004 – 2006 : excel data file “BIS_Financial_Statement.xls”
  3. Detailed guidelines for the exam: this file.

The following provide the lists of tasks that you have to complete:

Based on Financial Statements for Bodie Industrial Supply Inc. for the years 2004 – 2006 construct and compute the following:

  1. Common Size Income Statement and Balance Sheet (2 points)
  2. Construct the Cash Flow Statements : CFO, CFI, and CFF. (8 points)
  3. Compute the Financial Ratios (Use Basis Sets of Financial Ratios provided and discussed in the class):The Excel data set provides the list of financial ratios that you have to compute (5 points)
  4. Construct Pro forma solution for the fiscal year ending at January 2007 for Bodie: Use the following guidelines: (85 points)
    1. Make assumptions of each Income Statement and Balance Sheet items for the year 2007 based on the items for the prior years.  (20 points)
    1. Project Income Statement and Balance Sheet based on the assumptions for the year 2007. (50 points)
    1. Based on your Proforma analysis address the question whether Mr. MacDougall should provide the loan of $200K to BIS for the extension planned on November, 2006. (15 points)

PROFORMA GUIDELINES:Read the instructions below carefully:

The case write-up provides some estimates for some of the measures for the pro forma solution for FY ending on January 31, 2007. Your assumptions should be based on those estimates. For the ones there are no guidelines, you can have your own assumptions based on the prior year data.

The following list provides assumptions provided in the case and some additional insights for the estimates for FY ending January 31, 2007:

ASSUMPTIONS:

Income Statement Projection for FY ending January 31, 2007:

  1. Projected Sales: $2.8 mill
  2. COGS, Wages and Commissions, Provision for Doubtful Account, General Selling Exp., General Admin Exp.: same as % of sales in 2006
  3. Consider Rent to be zero with the purchase of land and building
  4. Amortization expense: $ Amortization in 2006, then add depreciation expense for the new expansion. (Consider 20 years straight line depreciation for the new expansion in Oct 2006, depreciated for three months till Jan 2007).
  5. Interest payments: Annualinterest payment for 2007 on the outstanding liabilities (read page3 of the case to get this value) and add three months payments of annual interests of $13,000 for the new loan (Nov 2006 – Jan 2007)

Balance Sheet Projection for FY ending January 31, 2007:

  1. Cash: construct the logical statement using PLUG to compute cash to balance the Balance Sheet
  2. AR: useACP (from assumption) and Net Sales to compute AR. Consider ACP days as same as 2006.
  3. Inventory: useInventory Days (from assumption) and COGS to compute Inventory. Consider Inv. Days as same as 2006
  4. Prepaid Expense, Goodwill and Deferred Charges: same $ as 2006
  5. GFA: All But Building same $ as of 2006. For Building: $same as 2006 + $Warehouse expansion end of 2006.
  6. Accumulated Amortization =Previous + New Amortization Expense (from income statement)
  7. New LOC: (NEW ITEM): construct the logical statement using PLUG to compute if BIS would need any additional LOC (Line of Credit) to manage the liquidity need and to balance the Balance Sheet
  8. AP: use AP Days (from assumption) and COGS to compute AP. Use AP Days equal 60 days, as owner wanted to reduce it.
  9. Other CL: same $ as 2006
  10. Current Maturity of Long-Term Debt(NEW ITEM): Loan repayment (Principal Payment) of $40,000 starting Feb 1, 2007 for the new loan of $200,000and principal payment of $71,924 for FY 2008 for all the existing long-term debts.
  11. Long Term Debt:
    1. Total LTD (OLD) : Consider total existing LTD (add all existing LTD in 2006) and then subtract the following:
      1. Transportation loan in 2006, as loan was repaid in April 2006 (footnote 2)
      1. Principal payment for 2007 for the existing long-term debt (get this values from the case)
      1. Principalpayment for 2008 for the existing long-term debt (get this value from the case write-up): this is accounted in the current portion.
    1. New LTD (NEW ITEM) for the construction: Consider the New Loan of $200,000 and then subtract the current portion of principal payment of the new loan starting at Feb 2007 (get this value from the case write-up).
  1. Common Stock: same $ as 2006
  2. Ending Retained Earnings (2007)=Beginning RE (2006) + NI (2007) – Dividend.

DELIVERABLES:

  1. Complete the Common Size Income Statement, Common Size Balance Sheet, Cash Flow Statement, Financial Ratios, Proforma Analysis and your answer to the main question regarding the loan  in the designated part in the excel spreadsheet provided.Note: There are three pages in the excel data file:
    1. Financial Statements: provide Common Size Income Statement and Balance Sheet, Financial Ratios, and Cash Flow Statement in this page. (Scroll down to see where to provide solutions).(15 points)
    1. Proforma Analysis: Provide proforma Asssumptions, Income Statement and Balance Sheet  in this page(70 points)
    1. Answer to the Question: Provide your answer to the main question in this page.(15 points)
  2. Based on your proforma solution, respond to the following questions:
    1. Question: Should Mr. MacDougall provide the loan of $200,000 to BIS for the warehouse extension that the company was planning to undertake by the beginning of November, 2006? Provide your reasoning. Give a detailed discussion. (Include your response in the excel spreadsheet at the designated area, where the question is provided).
  3. Submit the completed excel file (with your solution) in iCollege Final Exam Submission Tab by the due date.

ADDITIONAL NOTES

  • The Final Exam is individual assignment. Your submission has to be your own independent work completely. You are not allowed to discuss or share any material with your fellow classmates regarding solutions or during working on the final exam. You can seek help from the professor. You can look into old cases solutions and notes as needed while solving the final exam.  
  • In order to prepare for the exam, I would suggest that you review the prior cases: California Choppers, Cartwright Lumber, Chang Dental, More Vino, and Be Our Guest.
  • Please note that I will be using Turnitin to check the similarity pattern across submitted files for plagiarism.

If you need answers to this assignment, WhatsApp/Text to +1 646 978 1313  

or send us an email to admin@shrewdwriters.com and we will reply instantly. We provide original answers that are not plagiarized. Please, try our service. Thanks

Leave a Reply

Your email address will not be published.