FOREST COMMUNICATIONS COMPANY You re hired! Kate Forest formed Forest Communications Company (FCC) in 2007 when she obtained an exclusive franchise to nationally distribute a pen-based input device

FOREST COMMUNICATIONS COMPANY

You’re hired!

Kate Forest formed Forest Communications Company (FCC) in 2007 when she obtained an exclusive franchise to nationally distribute a pen-based input device that provides effortless communication with standard personal computers. Recent high sales growth of the base model pen-based input device (PID-B), along with expected sales growth for a new premium model (PID-P), requires adding new management team members. The Company hires you as a financial analyst to assume direct responsibility for financial planning activities. Your first assignment is to prepare a financial plan for the forthcoming year. Since you are anxious to make a favorable impression on Ms. Forest, the President of FCC, you immediately begin to assemble relevant information.

Ms. Forest is acutely aware of other growth-oriented companies within the high technology sector that have burned through cash and gone bankrupt. Having seen potentially viable businesses fail in the past particularly concerns Ms. Forest and, consequently, she wants to ensure that sufficient cash will be available to accommodate FCC’s expected growth. Thus, on your first day, the President meets with you to emphasize why the Company must thoroughly assess the impact of FCC’s planned growth on cash flow. She would like to present the financial plan to FCC’s board of directors and has requested that you construct a financial model that can be used to perform sensitivities and address a range of questions from top management and board members. She also requests that you provide the board with a supplemental cost-volume-profit graphical analysis.

Other Company Information

You recall from past coursework that the starting point for a financial plan is a reliable sales forecast. Thus you consult with Bogey Fields, the sales manager, and an outside market researcher. Bogey has studied sales and economic trends, as well as changes within the highly competitive handheld computing industry to establish the unit sales forecast, which is presented in the spreadsheet template. As shown, total monthly unit sales volume is expected to continue increasing over the quarters, but with sales mix shifting away from the basic model toward a new premium model with enhanced features. In addition, you determine through discussions with the accounts receivable manager that all sales to retailers are on account, with no discount, and payable within 20 days. Thus far, bad debts have been negligible.

Since FCC’s policy is to never stock out of its pen-based input devices (PIDs), and potentially forfeit market share to competitors, the Company maintains buffer inventory stock. Heretofore, FCC has sold only the basic model PID-B, but FCC recently began carrying a premium model PID-P. FCC expects that the current sales mix will shift toward the premium model, along with experiencing ongoing competitive pricing pressure (as reflected in the Assumptions Sheet).

The company’s quarterly operating expenses (organized by cost behavior) are also provided in the Assumptions Sheet of the excel template. All operating expenses are paid during the quarter, in cash, with the exception of depreciation and insurance expenses. New fixed assets, including personal computers and office furniture, will be purchased during the forthcoming year out of cash. The Company, which is privately owned with Ms. Forest as the majority shareholder, has declared dividends, payable in the first month of the following quarter. FCC’s actual balance sheet at December 31 is provided on the Income Statement and Balance Sheet tab.

Although FCC currently has no debt financing on its balance sheet, the Company has recently established a revolving line-of-credit through which it can borrow from The Bank of Titusville. For simplicity, assume that interest expense is recognized during the quarter incurred, while cash payments for interest occur one quarter in arrears. The same assumption should be applied for income tax expense. Required borrowings are made at the beginning of a quarter, and repayments at the end of a quarter in any dollar amount. FCC wishes to use any excess cash to pay off loan principal as rapidly as possible. However, the Company also desires a minimum ending cash balance each quarter as a bank minimum requirement for the LOC and to meet regular operating expenses.

REQUIRED: Part 1: Financial Planning Model (40 Points)

Download the Excel template from the course page – DO NOT MODIFY THE FORMAT OF THE TEMPLATE. Save the file onto a disk– do not attempt to complete the template directly from the course page and do not copy and paste sheets into a new workbook. Project assumptions have already been provided in the template’s assumptions sheet. Be sure to understand Chapter 3 (CVP Models) and Chapter 13 (Planning and Budgeting) in your textbook before proceeding with the project. Regularly save your work as you complete the model. Remember that you must use formulas so that any changes in input data automatically update your model. Otherwise, your model will not work correctly and you will lose points. Part I requires you to complete the sheets of the Excel template, as indicated below.

Complete Sheet 2 (Schedules)

1) Sales and merchandise purchase plans with supporting schedules. (8 points)

a) A sales plan by quarter and in total. Include a schedule of projected cash collections from sales and accounts receivable, by quarter and in total.

b) A purchases plan in units and in dollars. Include a schedule of projected cash payments for purchases, by quarter and in total. Note: The cost of inventory on hand is released to cost of goods sold before costs for the purchase of additional units (i.e., a FIFO cost flow is assumed).

Show all work and related computations to receive credit. No points will be awarded for handwritten or unclear responses that do not answer the questions. Use formulas in all spreadsheet cells and reference given data cells as necessary. POINTS WILL BE DEDUCTED FOR ENTERING HARDCODED VALUES.

ATTACHMENT PREVIEW

Download attachment

Forest Communications Company
Key Assumptions
Unit Volume Sensitivity
———–>
0.0%
UNIT SALES
Actual
Forecast
Month
3rd Quarter
4th Quarter
1st Quarter
2nd Quarter
3rd Quarter
4th Quarter
1st Quarter
TOTAL
16,310
17,790
18,600
21,600
23,400
25,500
27,900
PID-B
16,310
16,011
16,182
17,928
18,252
18,615
19,251
PID-P

1,779
2,418
3,672
5,148
6,885
8,649
TOTAL
16,310
17,790
18,600
21,600
23,400
25,500
27,900
Sales Mix %
Sales Mix Sensitivity
———–>
0.0%
3%
4%
5%
5%
4%
PID-B
100%
90%
87%
83%
78%
73%
69%
PID-P
0%
10%
13%
17%
22%
27%
31%
Unit selling price
Sales Price Sensitivity
———–>
0.0%
-1.00%
-2.00%
-2.00%
-3.00%
-3.00%
PID-B
$215
$215
$213
$209
$204
$198
$192
PID-P
$380
$376
$369
$361
$350
$340
Unit cost
Unit Cost Sensitivity
———–>
$-
PID-B
$160
$150
$150
$150
$150
$150
$150
PID-P
$220
$220
$220
$220
$220
$220
Wtd Average Sales Price
$231.50
$234.09
$235.81
$238.94
$239.38
$238.10
Wtd Average Unit Cost
$157.00
$159.10
$161.90
$165.40
$168.90
$171.70
Gross Profit %
PID-B
30.2%
29.5%
28.1%
26.6%
24.4%
22.0%
PID-P
42.1%
41.5%
40.3%
39.1%
37.2%
35.3%
Wtd Average
32.2%
32.0%
31.3%
30.8%
29.4%
27.9%
Operating expenses
1st Quarter
2nd Quarter
3rd Quarter
4th Quarter
1st Quarter
Sales commissions
(% of sales price)
5.0%
5.0%
5.0%
5.0%
5.0%
Royalties
(% of sales price)
4.0%
4.0%
4.0%
6.0%
6.0%
Shipping & handling
(per unit)
$7.00
$7.00
$8.00
$8.00
$8.00
Wages and salaries
(quarterly)
$388,000
$400,000
$420,000
$425,000
$440,000
Rent
(quarterly)
$42,000
$42,000
$46,000
$46,000
$46,000
Utilities
(quarterly)
$8,700
$9,000
$9,000
$9,200
$9,200
Insurance expired
(quarterly)
$7,500
$7,500
$7,500
$7,500
$7,500
Depreciation
(quarterly)
$38,000
$40,000
$48,000
$52,000
$54,000
Other S&A
(quarterly)
$110,000
$120,000
$120,000
$130,000
$145,000
Advertising Campaign
(quarterly)
$-
$-
$-
$-
$-
Fixed Asset Purchases
(quarterly)
$-
$105,000
$20,000
$40,000
$-
Dividends Declared
(quarterly)
$20,000
$20,000
$20,000
$20,000
$20,000
LOC Interest rate
(annual)
7%
7%
7%
7%
7%
Other Assumptions
SENSITIVITY SUMMARY RESULTS
Effective income tax rate
30%
NOI
=

Ending cash balance per quarter
$40,000
Net
Income
=

Ending inventory as percentage
Line of Credit
=

of next quarter’s sales
60%
Percentage of purchases paid in:
Part 2: (B3a)
Current quarter
50%
Inventory Turnover
=
0
Following quarter
50%
Part 2: (B3b)
Percentage of sales collected in:
Days Sales in acct. rec.
=
ENTER FORMULA
Current quarter
42%
Following quarter
35%
Second following quarter
23%

Unlock Solution Unlocking…

II.A.2C
Page 2
0
0
0

Show entire document

Leave a Comment