UW Farmcy Inc Excel Spreadsheet

User Generated

Wnpxznatng

Business Finance

the university of winnipeg

Description

Take on the role of a consultant to Moira Lafort, and complete the following analysis using the excel templates* provided: *Cells that require your input are highlighted in green. Do not modify any of the non-green cells.

1. On the “Schedules” worksheet:

a) Determine the total investment required to start the Harbourfront Pharmacy by filling in the information per the template provided.

b) Determine the amount of additional debt financing Lafort needs to cover the investment (cell B11).

c) Fill in the variable cost as a % of sales schedule with the information as provided in the case.

d) Fill in the fixed cost schedule. Use straight line depreciation/amortization for the fixed assets and franchise fee.

Unformatted Attachment Preview

BUS2010 Farmcy Inc. Group Members that contributed to the assignment are: First Name Last Name 1 2 3 4 5 6 Initial Investment Required Variable Cost Schedule as a % of Sales Franchise Fee Inventory Cash Float Fixed Assets Total less Equity Infustion OTC Inventory Prescription Inventory Royalty Cash Short Credit Card Bank Debt $ - $ 120,000 $ (120,000) Total 0.0% Fixed Cost Schedule Year 1 Year 2 Factors 3% 3% 3% 3% 3% Rent Utilities Insurance Marketing Phone and Internet Legal Owners Salary Depreciation - fixed assets Amortization - franchise fee Interest Total Fixed Costs Given 6 15 6% $ - $ - increase for year 2 increase for year 2 increase for year 2 increase for year 2 increase for year 2 year useful life year amortization period interest rate Base Number 2013 October September OTC Sales Dispensary Fee Revenue Prescription Revenue Total Revenue $ $ - Inflows: OTC Collections Dispensary Fee Collections Prescription Collections Equity Infusion Bank Loan Total Inflows $ 120,000.00 $ (120,000.00) $ $ - Outflows: Variable Costs OTC COGS Prescription COGS Royalty Cash Short Credit Card fees Fixed Costs Rent Utilities Insurance Marketing Phone and Internet Legal Owners Salary Interest Dividends Tax Investments: Franchise Fee Inventory Cash Float Fixed Asset Investment Loan Repayment Total Outflows Net Cash Flow - 0.00% 0.00% 0.00% 0.00% 0.00% $ $ $ $ $ $ $ $ - $ $ $ $ - $ $ - $ $ - Beginning Cash Balance Ending Cash Balance $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 2013 2014 November December January February March $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 2014 April May June July August $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 $ $ 7,000.00 7,000.00 $ $ $ $ 2014 Totals - $ $ $ $ 120,000.00 $ (120,000.00) $ - $ $ $ $ $ $ - $ $ $ $ $ $ $ $ $ $ - $ $ $ $ $ $ $ - $ $ 7,000.00 7,000.00 Base Number 2014 October September OTC Sales Dispensary Fee Revenue Prescription Revenue Total Revenue $ - $ - Inflows: OTC Collections Dispensary Fee Collections Prescription Collections Total Inflows $ - $ - Outflows: Variable Costs OTC COGS Prescription COGS Royalty Cash Short Credit Card fees Fixed Costs Rent Utilities Insurance Marketing Phone & Internet Legal Owners Salary Interest Dividends Tax Loan Repayment Total Outflows Net Cash Flow Beginning Cash Balance Ending Cash Balance 0.00% 0.00% 0.00% 0.00% 0.00% $ $ $ $ $ $ $ $ $ - $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 2014 2015 November December January February March $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 2015 April May June July August $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 7,000.00 7,000.00 $ $ $ $ 2015 Totals - $ $ $ $ - $ $ $ $ $ $ - $ $ $ $ $ $ $ $ $ $ - $ $ $ $ $ 7,000.00 7,000.00 Projected Income Statement For Fiscal 2014 and Fiscal 2015 2014 Sales Less: Variable Costs OTC COGS Prescription COGS Royalty Cash Short Credit Card fees $ $ $ $ $ - Contribution Margin Fixed Costs Rent Utilities Insurance Marketing Phone & Internet Legal Owners Salary Depreciation - Fixed Assets Amortization - Franchise Fee Total Fixed Costs Operating Income before Interest and Income Tax Less: Interest Net Income before Tax Less: Tax (25%) Net Income after Tax $ $ $ $ $ $ $ $ $ - 2014 $ $ $ $ $ $ $ $ - $ $ - - - $ $ $ $ $ $ $ $ $ 2015 $ - $ - $ - $ $ $ $ $ - $ $ $ $ $ - Projected Statement of Financial Position As at August 31, 2014 and August 31, 2015 Fiscal 2014 Assets Cash Accounts Recievable Inventory Fixed Assets Less: Accumulated Depreciation Franchise Fee, net $ $ - Total Assets Liabilities and Equity Liabilities: Accounts Payable Dividend Payable Tax Payable Bank Loan Total Liabilities Equity: Common Stock Retained Earnings Total Equity Total Liabilities and Equity $ $ $ $ (120,000.00) $ $ 120,000.00 - Fiscal 2014 $ $ $ $ $ $ Fiscal 2015 7,000.00 - $ $ $ $ $ 7,000.00 $ 120,000.00 - 7,000.00 (120,000.00) (120,000.00) $ $ $ $ $ $ $ $ $ $ $ - 7,000.00 - $ (120,000.00) $ 120,000.00 120,000.00 - $ - Return Analysis Return on Assets Fiscal 2014 Net income after Tax $ Opening Assets Closing Assets Average Assets $ $ $ ROA 7,000.00 3,500.00 Fiscal 2015 $ $ $ $ 0.0% 7,000.00 7,000.00 7,000.00 0.0% Return on Equity Net income after Tax $ Opening Equity Closing Equity Average Equity $ $ $ ROE 120,000.00 120,000.00 120,000.00 0.0% $ $ $ $ 120,000.00 120,000.00 120,000.00 0.0%
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

View attached explanation and answer. Let me know if you have any questions.

BUS2010 Farmcy Inc.

Group Members that contributed to the assignment are:
First Name Last Name

1
2
3
4
5
6

Initial Investment Required

Variable Cost Schedule as a % of Sales

Franchise Fee
Inventory
Cash Float
Fixed Assets
Total
less
Equity Infustion

$ 300,000.00
$
60,000
$
7,000
$
210,000
$
577,000

OTC Inventory
Prescription Inventory
Royalty
Cash Short
Credit Card

30.0%
37.0%
1.5%
0.5%
2.3%

$

120,000

Total

71.3%

Bank Debt

$

457,000

Fixed Cost Schedule
Rent
Utilities
Insurance
Marketing
Phone and Internet
Legal
Owners Salary
Depreciation - fixed assets
Amortization - franchise fee
Interest

Year 1
$ 120,000.00
$ 108,000.00
$ 48,000.00
$ 8,000.00
$ 1,320.00
$ 5,100.00
$ 60,000.00
$ 35,000.00
$ 20,000.00
$ 27,420.00

Year 2
Factors
$ 123,600.00
3%
$ 111,240.00
3%
$ 49,440.00
3%
$ 8,240.00
3%
$ 1,360.00
3%
$ 5,253.00
$ 70,000.00 Given
$ 35,000.00
6
$ 20,000.00
15
$ 18,420.00
6%

Total Fixed Costs

$ 432,840.00

$ 442,553.00

increase for year 2
increase for year 2
increase for year 2
increase for year 2
increase for year 2

year useful life
year amortization period
interest rate

OTC Sales
Dispensary Fee Revenue
Prescription Revenue
Total Revenue

Base Number
$ 1,200,000.00
$20,970.00
$ 1,000,000.00

Inflows:
OTC Collections
Dispensary Fee Collections
Prescription Collections
Equity Infusion
Bank Loan
Total Inflows
Outflows:
Variable Costs
OTC COGS
Prescription COGS
Royalty
Cash Short
Credit Card fees
Fixed Costs
Rent
Utilities
Insurance
Marketing
Phone and Internet
Legal
Owners Salary
Interest
Dividends
Tax
Investments:
Franchise Fee
Inventory
Cash Float
Fixed Asset Investment
Loan Repayment
Total Outflows
Net Cash Flow

30.00%
37.00%
1.50%
0.50%
2.30%

$ 120,000.00
$ 108,000.00
$ 48,000.00
$
8,000.00
$
1,320.00
$
5,100.00
$ 60,000.00
$ 27,420.00

$
$
$
$

September
30,000.00
1,748.00
83,333.00
115,081.00

$
$
$
$

2013
October
30,000.00
1,748.00
83,333.00
115,081.00

$
$
$
$
$
$

30,000.00
1,748.00
120,000.00
457,000.00
608,748.00

$
$
$
$
$
$

30,000.00
1,748.00
83,333.00
115,081.00

$
$
$
$
$

504,291.00
621,959.00
25,215.00
8,404.00
38,662.00

$
$
$
$
$

504,291.00
621,959.00
25,215.00
8,404.00
38,662.00

$
$
$
$
$
$
$
$

10,000.00
54,000.00
48,000.00
110.00
5,000.00
2,285.00

$
$
$
$
$
$
$
$

10,000.00
667.00
110.00
5,100.00
5,000.00
2,285.00

$
$
$
$

300,000.00
60,000.00
7,000.00
210,000.00

$ 1,894,926.00 $
$ (1,286,178.00) $

1,221,693.00
(1,106,612.00)

Beginning Cash Balance
Ending Cash Balance

$
7,000.00 $
$ (1,279,178.00) $

(1,279,178.00)
(2,385,790.00)

2013
$
$
$
$

November
30,000.00
1,748.00
83,333.00
115,081.00

$
$
$
$

December
30,000.0...


Anonymous
I was struggling with this subject, and this helped me a ton!

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Related Tags