Instructions:

This project is to be completed in EXCEL and will be done in teams $($of $2$ or $3$ people$).$ The project is to be submitted in Canvas using the correct submittal link. Make sure all team members$$ names are listed on each schedule.

Using the following information you are to prepare a comprehensive budget for Trail Tracker, Inc. The Company assembles a GPS System that is attached to trail signs in outdoor areas such as state and federal parks so that people can use them in case of an emergency to identify their location.

Arrangements have been made for the component parts $($bundled in packets, two packets per unit$)$ to be produced in small Idaho towns before being delivered to the company in Boise. The company completes the final assembly, tests the product, and then sells it globally.

The company has been in business for five years and has finally established a strong market for its product. Using past information it is preparing its budget for the first quarter of $2024$ in orde to project expected income, cash flows, and its balance sheet. Further, the company believes it can open up some new markets and will be approaching the bank for an increase in its operating line of credit.

From the information provided prepare the following monthly schedules$/$statements for the first quarter of $2024$ in Excel. The Excel workbook will include the following tabs$,$ all cells except for initial data entry will contain a formula or reference the appropriate cell:

$1)$ Preliminary Budgets will include the following schedules by month and quarter totals.

a$.$ Production Budget

b$.$ Raw Materials Budget for each raw material

c$.$ Raw Materials Purchases budget

d$.$ Cost of Goods Manufactured Schedule

$2)$ Pro$-$forma Cash Forecast with a column for each month

$3)$ Pro$-$forma Income Statement with a column for each month.

$4)$ Pro Forma Balance Sheet with a column for the last day of each month.

$5)$ Amortization Schedule for Capital Lease

$6)$ Depreciation Schedule

Make sure to include the appropriate headers on the Income Statement, Cash Forecast, and Balance Sheet as you will be providing these to the bank officer who is evaluating your operating line increase.

Data, Company Projections and Requirements

a$.$ Projected sales in units are as follows:

Month Sales in units

January $800$

February $900$

March $900$

April $1000$

May $1000$

June $1100$

b$.$ The company had $400$ GPS systems in their Finished Goods warehouse on December $31,2023.$

c$.$ WIP Inventory is $0$ at the end of each month.

d$.$ At the start of each month the management plans to have $45$ days $(1$ and $(1)/(2)$ months$)$ of direct materials on hand. Each packet of direct material costs $$60\mathrm{.}00.$ The company will have $1,400$ units on hand on December $31,2023($all purchased during December$).$

e$.$ The company wants to have at least $40\%$ of next months projected sales in ending finished goods inventory each month.

f$.$ Six hours of direct labor are required to assemble each device. The direct labor cost $($including fringe benefits$)$ is $$30\mathrm{.}00$ per hour.

g$.$ Manufacturing overhead is $35\%$ of direct labor cost.

h$.$ Devices are sold at $100\%$ markup on cost$.$

i$.$ Direct materials purchases are paid for on the $10$th day of the month following month of purchases.

j$.$ Manufacturing overhead is paid $20\%$ in cash with the balance paid in $30$ days.

k$.$ Wages earned by employees during the first half of each month are paid on the $25$th with the remainder paid on the $10$th of the following month. Assume that workforce is stable each month $($hence$,$ wages and salaries are the same every day of the month$).$

l$.$ On February $1,2024,$ the company acquires equipment and is going to finance $100\%$ of it through a capital lease. Life of the equipment is $48$ months with no salvage value. Capital lease payments are $$10,000$ per month including an imputed interest component. Your cost of capital is $8\%.$ Use this rate to calculate the present value of the cash payments and the present value of the lease principal as of February $1,2024.$ The first payment is due on February $1,2024.$

m$.$ The company uses straight$-$line depreciation to expense off the equipment over its life.

n$.$ Selling commissions are $15\%$ of sales price. These are paid on the $15$th day of the month following month of sale.

o$.$ Administrative salaries and fringe benefits are $$70,000$ per month payable on schedule outlined in $($k$)$ above.

p$.$ Rent is $$12,000$ per month payable on the first day of each month.

q$.$ On March $1,2024$ the Company will pay a $$25,000$ insurance premium for the following $6$ months of coverage. The premium amount has not changed from the prior year.

r$.$ Other general and administrative expenses are estimated to be $15\%$ of sales. They are paid in the month after they are incurred.

s$.$ The company has a $$500,000$ line of credit secured by inventory and accounts receivable. Borrowing against this line must be in increments of $$20,000.$ Interest is $12\%$ per annum and is payable on the $1$st day of the month following the borrowing. Assume all borrowing occurs on the $15$th day of the month. Repayments must also occur in $$20,000$ increments on the $15$th day of the month.

t$.$ all sales are on account and are collected $20\%$ in month of sale, $70\%$in next month and all but $2\%$ of the balance in the following month. $2\%$ of sales on account are typically uncollectible.

u$.$ Income tax rate is $21\%.$ Taxes accrue on each month's income and are paid in arrears on January $15,$ Apr $15,$ Jul $15,$ and Oct $15$ for the preceding quarter. Taxes due on January $15$ for the previous quarter are $$20,000.$ Note: any expected losses create tax benefits that can be used in reduce taxes paid in future quarters.

v$.$ Beginning cash balance on Januarty $1,2024$ is projected to be $$185,000.$ The company borrows from the line of credit if the cash balance is forecasted to fall below $$100,000.$

w$.$ Retained earnings is a plug figure on your balance sheet.

Prior year information you need:

a$.$ November sales revenue $=$ $$300,000$

b$.$ December sales revenue $=$ $$420,000$

c$.$ December direct material purchases $=$ $$84,000$

d$.$ December wages $=$ $$105,000$

e$.$ December Admin Salaries $=$ $$68,000$

f$.$ December other administrative expenses $=$ $$60,000$

g$.$ For simplicity: base interest expense on $30$ days per month and $360$ day year.