MIS 300

DSS Practice

Travel Expense Worksheet for Tax Purposes

Jack Connors, an accountant for a manufacturing company, has been preparing tax returns on weekends for several years to earn extra income. Although Jack uses a commercial tax package to prepare tax returns, he has discovered that it doesn't provide assistance in determining the portion of a business trip that is tax deductible. Jack has asked you to develop a worksheet to determine deductible travel expenses that he can use with a client who travels on business.

The IRS rules state that a taxpayer can deduct travel expenses incurred while pursuing a business purpose. Those travel expenses include transportation, lodging, incidentals, and 50% of meals. Incidentals are items such as local transportation, laundry, and similar small items that are necessary while traveling. Sometimes a trip may involve both business and personal activities. If fewer than 50% of the travel days are devoted to business, none of the transportation expenses are deductible; otherwise the transportation expenses are fully deductible. The costs for lodging, meals, and incidentals are deductible to the extent that they are related specifically to business activities. Only 50% of meals associated with business travel are deductible.

A calculation formula of each item of deductible travel expense is following;

• Transportation = IF Number of business travel days is greater than Number of personal travel days THEN Transportation (total cost), ELSE 0.
• Lodging = Number of business travel days * Lodging (cost per day)
• Meals = Number of business travel days * Meals (cost per day) * 50%
• Incidentals = Number of business travel days * Incidentals (cost per day)
• Total deductible travel expense = Transportation + Lodging + Meals + Incidentals

Data validation rule is following

• The IRS guideline limits the deduction for meals to $75 per day unless the taxpayer has receipts to justify a higher expense. Thus, set up a data validation rule in the input section to comply with this guideline. You should include an input message and determine appropriate text for the message.
• Also, include an error alert message using the Warning style. You determine the text of the error alert message.

Your job is to develop a Travel Expense DSS, which is similar to 401k DSS. Thus, you need to use the range names to build the formulas in the calculation section, create macros to clear the input section and a chart based on worksheet.