Please answer this question in the excel.

Fraser Corp. is a traditional retailer that recently also started an Internet-based subsidiary that sells its product online. Its sales in June 2016 were $700,000. Fraser, the company president, is preparing for a meeting with Tom Scott, a loan officer with Anchor Bank, to review quarter end financing requirements. After discussions with the company?s marketing and finance managers, sales over the next three months were forecasted as follows. Sales in July 2016: $1,250,000, sales in August 2016: $2,250,000 and sales in September 2016: $2,500,000.

Fraser?s balance sheet as of the end of June, 2016 was as follows.

____________________________________________________________________

Fraser Corporation

Balance Sheet as of June 30, 2016 (in $ Thousands)

____________________________________________________________________

Cash $ 60 Accounts payable $ 10

Accounts receivable 700 Notes payable 800

Inventories 600 Long-term debt 400

Net fixed assets 750 Total liabilities 1,210

Equity 900

Total assets $2,110 Total $2,110

____________________________________________________________________

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 June thus will be collected in July. The July sales will be collected in August, and so on. Inventory on hand represents the operating level which the company intends to maintain (i.e., not percentage of sales). 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 notes payable is 12% per annum. 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 borrowing (short term) from the bank at the end of the month. The interest rate on the new bank loans will be 12% per annum. All interest expenses are based on last month?s debt.

Prepare monthly pro forma cash budgets for July, August, and September 2016.

(6 marks).

Prepare monthly pro forma income statements for July, August, and September 2016.

(2 marks).

Prepare monthly pro forma balance sheets at the end of July, August, and September 2016.

(2 marks).

Use full decimals in your calculation and 2 decimal points for your reports.

Fraser Corp. is a traditional retailer that recently also started an Internet-based subsidiary that sells its product online. Its sales in June 2016 were $700,000. Fraser, the company president, is preparing for a meeting with Tom Scott, a loan officer with Anchor Bank, to review quarter end financing requirements. After discussions with the company's marketing and finance managers, sales over the next three months were forecasted as follows. Sales in July 2016: $1,250,000, sales in August 2016: $2,250,000 and sales in September 2016: $2,500,000. Fraser's balance sheet as of the end of June, 2016 was as follows. ____________________________________________________________________ Fraser Corporation Balance Sheet as of June 30, 2016 (in $ Thousands) ____________________________________________________________________ Cash $ 60 Accounts payable $ 10 Accounts receivable 700 Notes payable 800 Inventories 600 Long-term debt 400 Net fixed assets 750 Total liabilities 1,210 Equity 900 Total assets $2,110 Total $2,110 ____________________________________________________________________ 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 June thus will be collected in July. The July sales will be collected in August, and so on. Inventory on hand represents the operating level which the company intends to maintain (i.e., not percentage of sales). 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 notes payable is 12% per annum. 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 borrowing (short term) from the bank at the end of the month. The interest rate on the new bank loans will be 12% per annum. All interest expenses are based on last month's debt. A. Prepare monthly pro forma cash budgets for July, August, and September 2016. (6 marks). B. Prepare monthly pro forma income statements for July, August, and September 2016. (2 marks). C. Prepare monthly pro forma balance sheets at the end of July, August, and September 2016. (2 marks). Use full decimals in your calculation and 2 decimal points for your reports. Frasr Corp. Income Statement June.2016 July,2016 Aug.2016 Sales 700000.00 1250000.00 2250000.00 Cost of good sold(80% of sales) -560000.00 -1000000.00 -1800000.00 Gross profit 140000.00 250000.00 450000.00 Other expenses(7% of sales) -49000.00 -87500.00 -157500.00 Depreciation -10000.00 -10000.00 -10000.00 EBIT 81000.00 152500.00 282500.00 Interest(12% per annum,1%per month) -12100 Earning before taxes 68900.00 Taxes(40% of Earning before taxs -27560 Net income 41340.00 Note: Assumes interest is paid on monthly ending interest bearing debt. Balance Sheets Cash June.2016 July,2016 Aug.2016 60000.00 80000.00 80000.00 Accounts Receivables Inventories Current Assets Fixed assests,Net Total Assets 700000.00 600000.00 1360000.00 750000.00 2110000.00 1250000.00 600000.00 1930000.00 740000.00 2670000.00 2250000.00 600000.00 2930000.00 730000.00 3660000.00 Accounts Payable Notes Payable Additional bank borrowing Current liabilities Long term debt Equity Total Liabilities and Equity 10000.00 800000.00 0.00 810000.00 400000.00 900000.00 2110000.00 10000.00 800000.00 10000.00 800000.00 400000.00 400000.00 Actual Inventories Schedule Ending Inventories Cost of good sold Beginning inventories Purchases Cash Budget Beginning Cash Collect of receivables Purchases Other cash costs Payment of interest Payment of taxes Investment in Fixed Assets Net monthly cash folw Ending cash before borring Target ending cash Additional bank borrowing Projected Projected July Auguest 600000.00 600000.00 1000000.00 1800000.00 600000.00 600000.00 1000000.00 1800000.00 June.2016 July 60000.00 700000.00 -1000000.00 87500.00 Auguest 80000.00 1250000.00 -1800000.00 157500.00 Cumulative bank borrowing Statement of Cash Flows Operating: Net Income Depreciation Including in Accounts Receivables Including in Inventories Including in Account Payable Cash Flow from Operation Investing: Including in Gross Fixed Assets Financing: Including in bank borrowing Net Monthly Cash Flow Beginning Cash Ending Cash June.2016 July Auguest Sept.2016 2500000.00 -2000000.00 500000.00 -175000.00 -10000.00 315000.00 Sept.2016 80000.00 2500000.00 600000.00 3180000.00 720000.00 3900000.00 10000.00 800000.00 400000.00 Projected September 600000.00 2000000.00 600000.00 2000000.00 September 80000.00 2250000.00 -2000000.00 175000.00 September