FI 4020 - Financial Analysis and Loan Structuring

Assignment for Quiz One

Given the assumptions below concerning the Quick Growth Company, construct income statements, balance sheets and cash inflow-outflow statements for the company for January through December.

Be sure to view Chapter Two-Three Video Lecture (posted on iCollege) BEFORE attempting to solve this problem. Use Excel for this assignment. See the next page for a suggested format for your spreadsheet.

ASSUMPTIONS:

Initial investment (made on January 1 when company begins operations) = $6,000,000

Selling price = $120 per unit; COGS = variable cost = $90 per unit; Fixed costs = $100,000 per month.

Credit policy = net 30 days (assume all sales are on credit)

Inventory policy = 15 day supply (Note that this means inventory at the end of the month will be 1/2 of sales for that month. Thus, given that January sales = 5,000 units (see below), January ending inventory will be 2,500 units); Inventory carried at cost (i.e., at $90 per unit)

All costs are paid immediately. No discounts apply.

Dividends = 20% of monthly net income; There are 30 days in every month

All revenues, costs, cash inflows and cash outflows are evenly distributed through the month

The minimum cash balance is $0. If additional funds are needed, these will be in the form of debt (i.e., bank loans). The company will borrow money if cash < $0 on the balance sheet. The amount borrowed will be what is necessary to balance the balance sheet with cash = $0.

Round and record ALL values as whole numbers (that is, no decimal places)

Monthly sales:

o January = 5,000 units

o February = 10,000 units

o March = 20,000 units

o April = 40,000 units

o May = 80,000 units

o June = 140,000 units

o July = 170,000 units

o August = 190,000 units

o September = 200,000 units

o October = 210,000 units

o November = 210,000 units

o December = 210,000 units

The first month of my spreadsheet for this problem (i.e., the solution key) looks as follows. I included the subsequent months of February and March (but blank) to show you what my 12 month spreadsheet looks like (think of below repeating through December).

If you input correct formulas in each cell, to manage all 12 months on one spreadsheet. I suggest to make a similar formatted spreadsheet (but for all 12 months). Use the first month as a guide for your formulas. Be sure that net cash flow in each month corresponds with the change in cash from the prior month. For example, this company began operations on January 1 with $6,000,000 in cash. According to the January cash flow statement, they had net cash flow in January of -$785,000, meaning the ending cash balance should be $6,000,000 - $785,000 = $5,215,000. This value corresponds with Cash listed on the Balance Sheet (that I derived as a plug figure to ensure that Total Assets = Total L&E).

i need to know net income, cash, debt, net cash flow for all month ,

feb total asset, feb collections of cash.